Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Stephen.

Asked: March 04, 2008 - 9:19 am UTC

Last updated: September 23, 2013 - 5:35 pm UTC

Version: 10.2.0

Viewed 10K+ times! This question is

You Asked

Everything I have ever read about triggers explicity states that DDL, because of an implicit commit, cannot be used within a trigger. I have, though, seen in blogs and other various places where people have claimed to have gotten it to work through various hacks but I have never been successfull in doing so. I have two questions relating to this.

1) Why doesn't using
pragma autonomous_transaction
work within a procedure called from a trigger?

2) Do you know of workaround to execute DDL in a procedure called from a trigger?

Thanks in advance.

and Tom said...

1) it would, but THANKFULLY you never succeeded. Be very very very thankful.

2) use dbms_job to schedule the create statement AFTER your transaction has committed.




think about the ramifications of doing non-transactional work in a trigger for a moment. What happens when you need to rollback? Well, of course the DDL would not roll back - you are left "half way there".


Whenever you are tempted to do something non-transactional in a trigger - thing 500 times more and decide against it. It can only lead to "really bad things happening".


If you use dbms_job:

declare
   l_job number;
begin
   dbms_job.submit( l_job, 'do_ddl_safely( .... );' );
end;


where do_ddl_safely is a stored procedure you write that "does ddl, catching errors, notifying people, whatever needs be done" correctly. it will happen SHORTLY after you commit.

and, if you rollback - well, the queue into the job queue does too - you are protected.

http://asktom.oracle.com/Misc/something-different-part-i-of-iii.html
http://asktom.oracle.com/Misc/part-ii-seeing-restart.html
http://asktom.oracle.com/Misc/part-iii-why-is-restart-important-to.html

read that too to truly understand why doing none transactional things in triggers is a really horribly bad idea.

Rating

  (7 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Follow Up...

Stephen Moon, March 04, 2008 - 1:41 pm UTC

Below is the code for the procedure and trigger along with the error message. It's probably something simple but I'm just not seeing it. The procedure does works outside the job.

CREATE OR REPLACE PROCEDURE P_Execute_Sql
   (i_sql IN VARCHAR2)
AS
BEGIN
   EXECUTE IMMEDIATE (i_sql);
END;


CREATE OR REPLACE TRIGGER ddl_create
   AFTER CREATE ON SCHEMA
DECLARE
   g_job         BINARY_INTEGER;
   g_sql         VARCHAR2(4000);
   g_object_name user_objects.object_name%TYPE;
   g_object_type user_objects.object_type%TYPE;
BEGIN
   SELECT
      object_name,
      object_type
   INTO
      g_object_name,
      g_object_type
   FROM
      user_objects
   WHERE created = (SELECT MAX(created)
                      FROM user_objects);

   IF g_object_type IN ('TABLE','VIEW','FUNCTION','PACKAGE','PROCEDURE','SEQUENCE') THEN
      g_sql := 'create or replace public synonym ' || g_object_name || ' for ' || USER || '.' || g_object_name;

      DBMS_JOB.SUBMIT (
          job => g_job,
         what => 'p_execute_sql ("' || g_sql || '");');
   END IF;
END;


Error message after creating object:
ORA-06550: line 1, column 108:
PLS-00114: identifier 'create or replace public synon' too long
ORA-06512: at "SYS.DBMS_JOB", line 79
ORA-06512: at "SYS.DBMS_JOB", line 136
ORA-06512: at line 21
Tom Kyte
March 04, 2008 - 2:10 pm UTC

lose the ""

'p_execute_sql( ''' || g_sql || ''' );'

use two single quotes to get a single sql in a string.

PUBLIC synonym overload?

Michael O, March 04, 2008 - 2:07 pm UTC

Please tell me I'm not the only one who sees blanket creation of PUBLIC synonyms as an extremely bad practice. In my world PUBLIC synonyms are sparingly (if ever) used.

I can see why the Oracle product itself uses them, however I won't install third-party stuff that require that they be created.

PUBLIC synonyms are too much of the mindset "one database server : one application" for me.
Tom Kyte
March 04, 2008 - 2:15 pm UTC

actually - now that you mention it...

yes, this would be a pretty bad idea.


In order to solve the "I want to see all objects in schema X without having to to X.object", you should just use

alter session set current_schema=x;

in the application. that solves this nicely. I can just see someone creating "ALL_USERS" as a table in this schema and toasting a database.

ESPECIALLY with the really dangerous create or replace!!!!!!

to Michael O from TX

Michel Cadot, March 04, 2008 - 2:15 pm UTC


Can you explain in what your question is related to "DDL within a Trigger"?

Regards
Michel

Tom Kyte
March 04, 2008 - 2:16 pm UTC

he made a better than valid point - he didn't have a question in as much as some sound advice.

Oh!

Michel Cadot, March 04, 2008 - 2:31 pm UTC

Ok, I see the point.

Regards
Michel

...to Michael O from TX

Stephen Moon, March 04, 2008 - 2:39 pm UTC

Unfortunately DBAs sometimes inherit subpar systems with subpar designs. I agree that public synonyms are a bad way to go but unfortunatley the application I inherited will not work correctly without them. When I first got here the application user was granted DBA and the SYS password was change_on_install! Yet two more reasons why developers should be no where near the administration of a database. Sometimes you have to choose your battles.

Autonomous Transactions

Kevin Walz, March 05, 2008 - 11:51 am UTC

Happened on this just today and I think it falls well in the discussion. This is a pretty thorough review of autonomous transactions - where they are useful - and where they are dangerous. I think the message in it is very similar to what Tom has said many times here and elsewhere

http://www.orafaq.com/node/1915

Grant to Another schema

Matheus, September 10, 2013 - 8:30 pm UTC

and if we create a ddl trigger after create.. in this trigger has a call to a job that do grant to the table created, something like that..

CREATE OR REPLACE TRIGGER USER1.AFTER_CREATE_DDL AFTER CREATE ON DATABASE
DECLARE
L_JOBNO PLS_INTEGER;
BEGIN
  IF (UPPER(ORA_DICT_OBJ_TYPE) IN ('TABLE', 'VIEW', 'FUNCTION', 'PROCEDURE','TYPE')) THEN
    DBMS_JOB.SUBMIT( L_JOBNO, 'BEGIN EXECUTE IMMEDIATE ''GRANT ALL ON '||ORA_DICT_OBJ_NAME||
                              ' TO USER1,USER2''; END;', SYSDATE + INTERVAL '2' SECOND );
  END IF;
END;



With these trigger assumes that we have 2 users in the database, USER1 and USER2, if we, LOGGED AS USER1, write a DDL statament like
   CREATE TABLE USER2.TAB1 (ID INTEGER);
 

the trigger will not work fine because the PLSQL statament (job) will fire with the USER1 and this user does not have permission grant to a table in the USER2 in the PLSQL block yet.

There is something to do in this situation?
Tom Kyte
September 23, 2013 - 5:35 pm UTC

instead of posting code that does not work, cannot work, please post what you are trying to attempt in the form of a spec, like you would write up to give to a developer to implement.

then we can tell you if what you want to do is possible.

that ddl trigger wouldn't work regardless since the DDL that fires it is not 100%, it is not committed - your grant would NOT be able to see it.



further, user2 would already have access to the table, they own it, they need no grant.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library