Skip to Main Content

Problems with DDL Trigger After Table Rename

721427Jul 18 2011 — edited Jul 19 2011
Hey guys,

Oracle 10g, Windows Server 2008.

I have a DDL trigger to create a new public synonym in the schema after a table has been renamed.
create or replace
trigger update_access_after_rename
after rename on schema
  l_str1 varchar2(255);
  l_job1 number;
  sql_text ora_name_list_t;   
  v_stmt VARCHAR2(2000);   
  v_stmt2 VARCHAR2(2000);   
  n := ora_sql_txt(sql_text);   
  FOR i IN 1..n LOOP    
    v_stmt := v_stmt || sql_text(i);   
  v_stmt := regexp_replace( v_stmt, 
  'rename[[:space:]]+.*[[:space:]]+to[[:space:]]+([a-z0-9_]+)', '\1', 1, 1, 'i' );
  if((ora_dict_obj_type = 'TABLE' or ora_dict_obj_type = 'VIEW') and 
      ora_dict_obj_owner = 'PSHRADMN')
    l_str1 := 'begin execute immediate ''set role all'';' || 
              'execute immediate ''create or replace public synonym ' ||
              v_stmt || ' for pshradmn.' || v_stmt ||
              '''; end;';
    dbms_job.submit(l_job1, l_str1);
  end if;
I'm using a regexp to capture the renamed table ( Seems like it should work but when I try to do a rename, I get the following error:
rename test1 to test2;

Error report:
SQL Error: ORA-00604: error occurred at recursive SQL level 1
ORA-06550: line 1, column 150:
PLS-00103: Encountered the symbol "create or replace public synonym test2" when expecting one of the following:

   ( - + case mod new not null <an identifier>
   <a double-quoted delimited-identifier> <a bind variable> avg
   count current exists max min prior sql stddev sum variance
   execute forall merge time timestamp interval date
   <a string literal with character set specification>
   <a number> <a single-quoted SQL string> pipe
   <an alternatively-quoted string literal with character se
ORA-06512: at "SYS.DBMS_JOB", line 82
ORA-06512: at "SYS.DBMS_JOB", line 139
ORA-06512: at line 27
00604. 00000 -  "error occurred at recursive SQL level %s"
*Cause:    An error occurred while processing a recursive SQL statement
           (a statement applying to internal dictionary tables).
*Action:   If the situation described in the next error on the stack
           can be corrected, do so; otherwise contact Oracle Support.
The regexp in the trigger works as it shows the rename table name in the error message. I can hard-code the value of the table after rename, and the trigger works fine. I feel like I'm missing something very simple with the variable definition in the trigger (v_stmt), but I can't put my finger on it.

Any thoughts?
Post Details
Added on Jul 18 2011