Trigger to Create Public Synonyms

We have an application which creates dynamic temporary tables, which require public synonymns generated to enable users to access these tables.

The solution which came to mind was to create a simple DDL trigger. However, once I'd finished writing the trigger, I discovered the following limitation:

 
ORA-30511 invalid DDL operation in system triggers. 
Cause 
An attempt was made to perform an invalid DDL operation in a system trigger. Most DDL operations currently are not supported in system triggers. The only currently supported DDL operations are table operations and ALTER/COMPILE operations. 

I tried a few different options such as playing around with my favourite pragma autonomous_transaction method, but couldn't get the trigger to execute the "create public synonym" command.

Therefore, I decided to create a stored procedure which would accept the parameters (objowner and objname) and wrote the trigger to execute a background job which would run the procedure in a minute - nice and easy solution.

The Stored Procedure

CREATE OR REPLACE PROCEDURE SYN_PROC (objname in varchar2, objowner in varchar2) IS

BEGIN

 execute immediate 'create public synonym  ' || objname || '  for ' || objowner || '.' || objname;

END;
/

The Trigger

CREATE OR REPLACE TRIGGER SYN_TRIG AFTER create ON SCHEMA

declare

jobnum   number;

BEGIN 

IF ORA_DICT_OBJ_OWNER='&SCHEMA_OWNER' AND ORA_DICT_OBJ_NAME LIKE '%&TABLE_NAME%' THEN

   SYS.DBMS_JOB.SUBMIT (jobnum, 'ORACLE_DBA.SYN_PROC(''' || ORA_DICT_OBJ_NAME || ''',''' || ORA_DICT_OBJ_OWNER || ''');' , sysdate+(1/24/60));

END IF;

END;
/