title: oracle 10g ddl auditing: ora-00604 error occurred at recursive sql on dbms_metadata
body: <p>I want to audit all new tables created in my database by the application team.
  The sql_text column of dba_audit_trail is empty, so I created a procedure and trigger.</p>
  
  <pre><code>CREATE TABLE bhattd.ddl_logger (
  obj_operation varchar2(25),
  obj_owner     varchar2(25),
  obj_name      varchar2(25),
  obj_type      varchar2(25),
  obj_text      clob,
  obj_when      timestamp);
  
  CREATE OR REPLACE PROCEDURE get_ddl (o_name varchar2, o_owner varchar2, l_sysevent varchar2) authid current_user IS
  count_tab number;
  BEGIN
  SELECT COUNT(*) INTO count_tab FROM sys.dba_tables WHERE table_name = o_name and owner = o_owner;
  IF count_tab = 1 THEN
  INSERT INTO bhattd.ddl_logger VALUES (l_sysevent, o_owner, o_name, 'TABLE', sys.dbms_metadata.get_ddl('TABLE',o_name,o_owner), systimestamp);
  END IF;
  END;
  </code></pre>
  
  <p>1st question: If I create a table in my own schema (bhattd) then the procedure works without "authid current_user".  If I execute</p>
  
  <pre><code>get_ddl('S_UCM_PRIVACY','SIEBEL','CREATE');
  </code></pre>
  
  <p>then it fails unless I include "authid current_user" in the procedure definition, even though I have the DBA role.  Why is this happening?</p>
  
  <p>Next, I created the trigger:</p>
  
  <pre><code>CREATE OR REPLACE TRIGGER ddl_trigger1 AFTER CREATE ON DATABASE
  DECLARE
  l_sysevent varchar2(25);
  BEGIN
  SELECT ora_sysevent INTO l_sysevent FROM dual;
  IF ( l_sysevent = 'CREATE' )
  THEN
  BEGIN
  bhattd.get_ddl(ora_dict_obj_name,ora_dict_obj_owner,l_sysevent);
  END;
  END IF;
  END;
  </code></pre>
  
  <p>Now, if I try to create a table under schema "SCOTT", I get this error:</p>
  
  <pre><code>ORA-00604: error occurred at recursive SQL level 1
  ORA-31603: object "T1" of type TABLE not found in schema "SCOTT"
  ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
  ORA-06512: at "SYS.DBMS_METADATA", line 2806
  ORA-06512: at "SYS.DBMS_METADATA", line 4333
  ORA-06512: at line 1
  ORA-06512: at "BHATTD.GET_DDL", line 8
  ORA-06512: at line 8
  </code></pre>
  
  <p>2nd question: I think it should have worked without "authid current_user" based on definer's rights.  Then after adding "authid current_user", I think this should work based on invoker's rights (I have the DBA role, which includes execute on dbms_metadata, plus I own the table, procedure, and trigger).  Why is this happening?</p>
  
  <p>Thank you in advance for your response.</p>