Debug DDL trigger

Links

Frequent Oracle Errors

•	TNS:could not resolve the connect identifier specified
•	Backtrace message unwound by exceptions
•	invalid identifier
•	PL/SQL compilation error
•	internal error
•	missing expression
•	table or view does not exist
•	end-of-file on communication channel
•	TNS:listener unknown in connect descriptor
•	insufficient privileges
•	PL/SQL: numeric or value error string
•	TNS:protocol adapter error
•	ORACLE not available
•	target host or object does not exist
•	invalid number
•	unable to allocate string bytes of shared memory
•	resource busy and acquire with NOWAIT specified
•	error occurred at recursive SQL level string
•	ORACLE initialization or shutdown in progress
•	archiver error. Connect internal only, until freed
•	snapshot too old
•	unable to extend temp segment by string in tablespace
•	Credential retrieval failed
•	missing or invalid option
•	invalid username/password; logon denied
•	unable to create INITIAL extent for segment
•	out of process memory when trying to allocate string bytes
•	shared memory realm does not exist
•	cannot insert NULL
•	TNS:unable to connect to destination
•	remote database not found'>ora-02019
•	exception encountered: core dump
•	inconsistent datatypes
•	no data found
•	TNS:operation timed out
•	PL/SQL: could not find program
•	existing state of packages has been discarded
•	maximum number of processes exceeded
•	error signaled in parallel query server
•	ORACLE instance terminated. Disconnection forced
•	TNS:packet writer failure
•	see ORA-12699
•	missing right parenthesis
•	name is already used by an existing object
•	cannot identify/lock data file
•	invalid file operation
•	quoted string not properly terminated

Debug DDL trigger

Debug DDL trigger

2005-08-22 - By david wendelken

Reply: 1 2 3 4 5 6 7 8 9 10 >>

I'm guessing ora_dict_obj_type is a column in dherri.schema?

If so, you are using row-level data in a statement-level trigger.
You need to add the magic words that turn the trigger into a row level
trigger.
It's "for each row" or words to that effect. You'll find the exact syntax
in the manual.

Here's the trigger's text:

CREATE OR REPLACE TRIGGER sys.cp_tab_before_drop_btg

BEFORE drop ON dherri.SCHEMA

WHEN ( ora_dict_obj_type = 'TABLE'

AND ( ora_dict_obj_name LIKE 'SCORE\_ACCT\_TB\_%' ESCAPE '\'

)

)

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:o =
"urn:schemas-microsoft-com:office:office" xmlns:w =
"urn:schemas-microsoft-com:office:word" xmlns:x =
"urn:schemas-microsoft-com:office:excel"><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<TITLE>Message</TITLE>

<META content="MSHTML 6.00.2800.1515" name=GENERATOR>
<STYLE>@(protected) Section1 {size: 8.5in 11.0in; margin: 1.0in 1.25in 1.0in 1.25in; }
P.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoNormal {
FONT-SIZE: 12pt; MARGIN: 0in 0in 0pt; FONT-FAMILY: "Times New Roman"
}
P.MsoBodyText {
FONT-SIZE: 12pt; MARGIN: 0in 0in 6pt; FONT-FAMILY: "Times New Roman"
}
LI.MsoBodyText {
FONT-SIZE: 12pt; MARGIN: 0in 0in 6pt; FONT-FAMILY: "Times New Roman"
}
DIV.MsoBodyText {
FONT-SIZE: 12pt; MARGIN: 0in 0in 6pt; FONT-FAMILY: "Times New Roman"
}
A:link {
COLOR: blue; TEXT-DECORATION: underline
}
SPAN.MsoHyperlink {
COLOR: blue; TEXT-DECORATION: underline
}
A:visited {
COLOR: #606420; TEXT-DECORATION: underline
}
SPAN.MsoHyperlinkFollowed {
COLOR: #606420; TEXT-DECORATION: underline
}
P.StyleBodyTextItalic {
FONT-SIZE: 10pt; MARGIN: 0in 0in 6pt 0.25in; FONT-STYLE: italic; FONT-FAMILY:
"Times New Roman"
}
LI.StyleBodyTextItalic {
FONT-SIZE: 10pt; MARGIN: 0in 0in 6pt 0.25in; FONT-STYLE: italic; FONT-FAMILY:
"Times New Roman"
}
DIV.StyleBodyTextItalic {
FONT-SIZE: 10pt; MARGIN: 0in 0in 6pt 0.25in; FONT-STYLE: italic; FONT-FAMILY:
"Times New Roman"
}
SPAN.EmailStyle19 {
COLOR: windowtext; FONT-FAMILY: Arial; mso-style-type: personal-compose
}
DIV.Section1 {
page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=#606420 link=blue>
<DIV><FONT face=Arial color=#0000ff size=2></FONT> </DIV>
<DIV><SPAN class=748373721-22082005><FONT face=Arial color=#0000ff size=2>I'm
guessing <FONT color=#000000>ora_dict_obj_type is a column in
dherri.schema?</FONT></FONT></SPAN></DIV>
<DIV><SPAN class=748373721-22082005><FONT face=Arial
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=748373721-22082005><FONT face=Arial size=2>If so, you are
using
row-level data in a statement-level trigger.</FONT></SPAN></DIV>
<DIV><SPAN class=748373721-22082005><FONT face=Arial color=#0000ff size=2>You
need to add the magic words that turn the trigger into a row level
trigger.</FONT></SPAN></DIV>
<DIV><SPAN class=748373721-22082005><FONT face=Arial color=#0000ff size=2>It's
"for each row" or words to that effect.  You'll find the exact
syntax in the manual.</FONT></SPAN></DIV>
<DIV><SPAN class=748373721-22082005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=748373721-22082005></SPAN> </DIV>
<DIV><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">Here's the trigger's
text:<o:p></o:p></SPAN></FONT></DIV>
<BLOCKQUOTE dir=ltr
style="PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #0000ff 2px solid;
MARGIN-RIGHT: 0px">
<DIV class=Section1>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial"><o:p> </o:p></SPAN></FONT><
/P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">CREATE OR REPLACE TRIGGER
sys.cp_tab_before_drop_btg<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">   BEFORE drop ON
dherri.SCHEMA<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">     
WHEN (    ora_dict_obj_type =
'TABLE'<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">     
      
AND (   ora_dict_obj_name LIKE 'SCORE\_ACCT\_TB\_%' ESCAPE
'\'<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">     
          
)<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial size=2><SPAN
style="FONT-SIZE: 10pt; FONT-FAMILY: Arial">     
     
)<o:p></o:p></SPAN></FONT></P>
<P class=MsoNormal><FONT face=Arial color=#0000ff
size=2></FONT> </P></DIV></BLOCKQUOTE></BODY></HTML>