Links
• Home
• Oracle DBA Forum
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-23       - By david wendelken

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



> ora_dict_obj_type is a system defined event attribute for DDL triggers,
escentially Oracle-defined variables.  

Duh.  :(  Should have read it closer!  Brain on autopilot read "drop" as
"delete".

Haven't worked with this type of trigger.

I would suggest turning the when clause into an if statement in the trigger
body, and, for debugging purposes, add an else branch that does something
you can also trace.
Maybe it doesn't "set" the values until the trigger fires.  If nothing else,
you can use the else branch to find out what values are in those variables!



I need this to be a DDL trigger, not a DML trigger, as I want to capture the
table and its contents before its dropped.  When created, it could be done
via CTAS or just CREATE, followed by various DML statements.  To capture its
final contents I'd be easiest for me to copy it right before it gets
dropped.  I guess I could generate a materialized view on it when its
created, so that all content would be automatically applied to the copy, but
I thought this would be easier.






<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML xmlns="http://www.w3.org/TR/REC-html40" xmlns:v =
"urn:schemas-microsoft-com:vml" 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><!--[if !mso]>
<STYLE>v\:* {
  BEHAVIOR: url(#default#VML)
}
o\:* {
  BEHAVIOR: url(#default#VML)
}
w\:* {
  BEHAVIOR: url(#default#VML)
}
shape {
  BEHAVIOR: url(#default#VML)
}
</STYLE>
<![endif]-->
<STYLE>@(protected) {
  font-family: Tahoma;
}
@(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"
}
P.stylebodytextitalic0 {
  FONT-SIZE: 10pt; MARGIN: 0in 0in 6pt 0.25in; FONT-STYLE: italic; FONT-FAMILY:
"Times New Roman"
}
LI.stylebodytextitalic0 {
  FONT-SIZE: 10pt; MARGIN: 0in 0in 6pt 0.25in; FONT-STYLE: italic; FONT-FAMILY:
"Times New Roman"
}
DIV.stylebodytextitalic0 {
  FONT-SIZE: 10pt; MARGIN: 0in 0in 6pt 0.25in; FONT-STYLE: italic; FONT-FAMILY:
"Times New Roman"
}
SPAN.EmailStyle20 {
  COLOR: windowtext; FONT-FAMILY: Arial; mso-style-type: personal
}
SPAN.EmailStyle21 {
  COLOR: navy; FONT-FAMILY: Arial; mso-style-type: personal-reply
}
DIV.Section1 {
  page: Section1
}
</STYLE>
</HEAD>
<BODY lang=EN-US vLink=#606420 link=blue>
<DIV><FONT face=Arial color=#0000ff size=2></FONT>&nbsp;</DIV><FONT face=Tahoma>
<DIV><BR></FONT><FONT color=navy><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><SPAN
class=950503213-23082005><FONT
color=#0000ff>&gt;&nbsp;</FONT></SPAN>ora_dict_obj_type is a system defined
event attribute for DDL triggers, escentially Oracle-defined variables.<SPAN
class=950503213-23082005>&nbsp;<FONT
color=#0000ff>&nbsp;</FONT></SPAN></SPAN></FONT></DIV>
<DIV><SPAN style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><SPAN
class=950503213-23082005></SPAN><FONT color=#0000ff><SPAN
class=950503213-23082005>&nbsp;</SPAN></FONT></SPAN></DIV>
<DIV><SPAN style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><FONT
color=#0000ff><SPAN class=950503213-23082005>Duh.&nbsp; :(&nbsp;
</SPAN></FONT></SPAN><SPAN
style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><FONT
color=#0000ff><SPAN class=950503213-23082005>Should have read it closer!&nbsp;
Brain on autopilot read "drop" as "delete".</SPAN></FONT></SPAN></DIV>
<DIV><SPAN style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><FONT
color=#0000ff><SPAN class=950503213-23082005></SPAN></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><FONT
color=#0000ff><SPAN class=950503213-23082005>Haven't worked with this type of
trigger.</SPAN></FONT></SPAN></DIV>
<DIV><SPAN style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><FONT
color=#0000ff><SPAN class=950503213-23082005></SPAN></FONT></SPAN>&nbsp;</DIV>
<DIV><SPAN style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><FONT
color=#0000ff><SPAN class=950503213-23082005>I would suggest turning the when
clause into an if statement in the trigger body, and, for debugging purposes,
add an else branch that does something you can also
trace.</SPAN></FONT></SPAN></DIV>
<DIV><SPAN style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><FONT
color=#0000ff><SPAN class=950503213-23082005>Maybe it doesn't "set" the values
until the trigger fires.&nbsp; If nothing else, you can use the else branch to
find out what values are in those variables!</SPAN></FONT></SPAN></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 color=navy size=2><SPAN
 style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p>&nbsp;</o:p><
/SPAN></FONT></P>
 <P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
 style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial">I need this to be a
 DDL trigger, not a DML trigger, as I want to capture the table and its
 contents before its dropped. &nbsp;When created, it could be done via CTAS or
 just CREATE, followed by various DML statements. &nbsp;To capture its final
 contents I'd be easiest for me to copy it right before it gets dropped.
 &nbsp;I guess I could generate a materialized view on it when its created, so
 that all content would be automatically applied to the copy, but I thought
 this would be easier.<I><SPAN
 style="FONT-STYLE: italic"><o:p></o:p></SPAN></I></SPAN></FONT></P>
 <P class=MsoNormal><FONT face=Arial color=navy size=2><SPAN
 style="FONT-SIZE: 10pt; COLOR: navy; FONT-FAMILY: Arial"><o:p>&nbsp;</o:p><
/SPAN></FONT></P>
 <DIV>
 <P class=MsoNormal><FONT face=Arial color=#0000ff
 size=2></FONT>&nbsp;</P></DIV></DIV></BLOCKQUOTE></BODY></HTML>