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
after rename on schema
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 ||
I'm using a regexp to capture the renamed table (http://stackoverflow.com/questions/1809787/oracle-how-do-i-determine-the-new-name-of-an-object-in-an-after-alter-trigger). Seems like it should work but when I try to do a rename, I get the following error:
rename test1 to test2;
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.