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 Herring Dave - dherri

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

Folks,

I'm having a rough time trying to figure out how to debug a DDL trigger.
I'm on Oracle 9.2.0.4, under Tru64 5.1. I've written a trigger whose
purpose is to make a backup copy of a table in one schema into another,
before the table gets dropped. The problem is, as far as I can tell,
the trigger is never fired. I've validated that the trigger exists and
is enabled, but can't get any further. Is there a way to trace how
triggers get chosen to be fired by Oracle?

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
'\'

)

)

DECLARE

PRAGMA AUTONOMOUS_TRANSACTION;

v_datestamp DATE := sysdate;

v_ddl_stmt VARCHAR2(4000);

v_ora_error NUMBER;

v_procedure_name VARCHAR2(30) := 'CP_TAB_BEFORE_DROP_BTG';

v_procedure_owner VARCHAR2(30) := 'SYS';

v_table_count PLS_INTEGER := 1;

BEGIN

INSERT INTO dherri.aud_plsql_error_tb values ('SYS','Test: Before
SELECT',1,sysdate);

COMMIT;

/*

* Validate the table to be dropped does exist.

*/

SELECT COUNT(*) INTO v_table_count

FROM dba_tables

WHERE owner = ora_dict_obj_owner

AND table_name = ora_dict_obj_name;

IF v_table_count > 0 THEN

INSERT INTO dherri.aud_plsql_error_tb values ('SYS','Test: Within
IF-TEST',v_table_count,sysdate);

COMMIT;

v_ddl_stmt := 'CREATE TABLE dherri.'||ora_dict_obj_name||'_bkp
PCTFREE 0 AS SELECT * FROM dherri.'||ora_dict_obj_name||';';

EXECUTE IMMEDIATE v_ddl_stmt;

END IF;

EXCEPTION

WHEN OTHERS THEN

v_ora_error := ABS(SQLCODE);

INSERT INTO dherri.aud_plsql_error_tb VALUES (v_procedure_owner,
v_procedure_name, v_ora_error, sysdate);

END;

/

Dave

-- ---- ---- ---- ---- ---- ---- ----

Dave Herring, DBA

Acxiom Corporation

3333 Finley

Downers Grove, IL 60515

wk: 630.944.4762

<mailto:dherri@(protected) <mailto:dherri@(protected)> >

-- ---- ---- ---- ---- ---- ---- ----

**************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally
privileged.

If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.

If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.

Thank You.
**************************************************************************

<html xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas
-microsoft-com:office:word" xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns="http://www.w3.org/TR/REC-html40">

<head>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 11 (filtered medium)">
<style>

</style>

</head>

<body lang=EN-US link=blue vlink="#606420">

<div class=Section1>

Folks,<o:p></o:p>

<o:p> </o:p>

I'm having a rough time trying to figure out how to debug a
DDL trigger.  I'm on Oracle 9.2.0.4, under Tru64 5.1.  I've written a
trigger
whose purpose is to make a backup copy of a table in one schema into another,
before the table gets dropped.  The problem is, as far as I can tell, the
trigger is never fired.  I've validated that the trigger exists and is
enabled,
but can't get any further.  Is there a way to trace how triggers get
chosen to
be fired by Oracle?<o:p></o:p>

<o:p> </o:p>

Here's the trigger's text:<o:p></o:p>

<o:p> </o:p>

CREATE OR REPLACE TRIGGER sys.cp_tab_before_drop_btg<o:p></o
:p>

   BEFORE drop ON dherri.SCHEMA<o:p></o:p>

      WHEN (    ora
_dict_obj_type = 'TABLE'<o:p></o:p>

          
  AND (   ora_dict_obj_name LIKE
'SCORE\_ACCT\_TB\_%' ESCAPE '\'<o:p></o:p>

          
      )<o:p></o:p>

          
)<o:p></o:p>

DECLARE<o:p></o:p>

   PRAGMA AUTONOMOUS_TRANSACTION;<o:p></o:p>

   v_datestamp      
    DATE := sysdate;<o:p></o:p>

   v_ddl_stmt      
     VARCHAR2(4000);<o:p></o:p>

   v_ora_error      
    NUMBER;<o:p></o:p>

   v_procedure_name    
VARCHAR2(30) :=
'CP_TAB_BEFORE_DROP_BTG';<o:p></o:p>

   v_procedure_owner    VARCHAR2(30
) := 'SYS';<o:p></o:p>

   v_table_count     
   PLS_INTEGER := 1;<o:p></o:p>

<o:p> </o:p>

BEGIN<o:p></o:p>

   INSERT INTO dherri.aud_plsql_error_tb values
('SYS','Test: Before SELECT',1,sysdate);<o:p></o:p>

   COMMIT;<o:p></o:p>

<o:p> </o:p>

/*<o:p></o:p>

 * Validate the table to be dropped does exist.<o:p></o
:p>

 */<o:p></o:p>

   SELECT COUNT(*) INTO v_table_count<o:p></o:p>

     FROM dba_tables<o:p></o:p>

    WHERE owner = ora_dict_obj_owner<o:p></o
:p>

      AND table_name = ora_dict_obj
_name;<o:p></o:p>

<o:p> </o:p>

   IF v_table_count > 0 THEN<o:p></o:p>

      INSERT INTO dherri.aud_plsql
_error_tb values
('SYS','Test: Within IF-TEST',v_table_count,sysdate);<o:p></o:p>

      COMMIT;<o:p></o:p>

<o:p> </o:p>

      v_ddl_stmt := 'CREATE TABLE
dherri.'||ora_dict_obj_name||'_bkp
PCTFREE 0 AS SELECT * FROM dherri.'||ora_dict_obj_name||';';<o:p></o:p>

      EXECUTE IMMEDIATE v_ddl_stmt;
<o:p></o:p>

   END IF;<o:p></o:p>

<o:p> </o:p>

EXCEPTION<o:p></o:p>

   WHEN OTHERS THEN<o:p></o:p>

      v_ora_error := ABS(SQLCODE);
<o:p></o:p>

      INSERT INTO dherri.aud_plsql
_error_tb VALUES
(v_procedure_owner, v_procedure_name, v_ora_error, sysdate);<o:p></o:p>

END;<o:p></o:p>

/<o:p></o:p>

<o:p> </o:p>

Dave<o:p></o:p>

-- ---- ---- ---- ---- ---- ---- ----<o:p></o:p>

Dave Herring, DBA<o:p></o:p>


Acxiom Corporation<o:p></o:p
>

3333 Finley<o:p></o:p>

Downers Grove, IL 60515<o:p>
</o:p>

wk: 630.944.4762<o:p></o:p>

<<a href="mailto:dherri@(protected)">mailto:dherri@(protected)</a>><o:p></o:p>

-- ---- ---- ---- ---- ---- ---- ----<o:p></o:p>

<o:p> </o:p>

</div>

<pre>**************************************************************************
The information contained in this communication is confidential, is
intended only for the use of the recipient named above, and may be legally
privileged.

If the reader of this message is not the intended recipient, you are
hereby notified that any dissemination, distribution or copying of this
communication is strictly prohibited.

If you have received this communication in error, please resend this
communication to the sender and delete the original message or any copy
of it from your computer system.

Thank You.
**************************************************************************
</pre></body>

</html>