Stephen -- Thanks for the question regarding "DDL within a Trigger", version 10.2.0
Submitted on 4-Mar-2008 9:19 Central time zone
Last updated 4-Mar-2008 14:16
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 we 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://tkyte.blogspot.com/2005/08/something-different-part-i-of-iii.html
http://tkyte.blogspot.com/2005/08/part-ii-seeing-restart.html
http://tkyte.blogspot.com/2005/09/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.
Follow Up...
March 4, 2008 - 1pm Central time zone
Reviewer: Stephen Moon from Nashville, TN
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
Followup March 4, 2008 - 2pm Central time zone:
lose the ""
'p_execute_sql( ''' || g_sql || ''' );'
use two single quotes to get a single sql in a string.
PUBLIC synonym overload?
March 4, 2008 - 2pm Central time zone
Reviewer: Michael O from TX
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.
Followup March 4, 2008 - 2pm Central time zone:
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
March 4, 2008 - 2pm Central time zone
Reviewer: Michel Cadot from France
Can you explain in what your question is related to "DDL within a Trigger"?
Regards
Michel
Followup March 4, 2008 - 2pm Central time zone:
he made a better than valid point - he didn't have a question in as much as some sound advice.
Oh!
March 4, 2008 - 2pm Central time zone
Reviewer: Michel Cadot from France
Ok, I see the point.
Regards
Michel
...to Michael O from TX
March 4, 2008 - 2pm Central time zone
Reviewer: Stephen Moon from Nashville, TN
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
March 5, 2008 - 11am Central time zone
Reviewer: Kevin Walz from South Carolina
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
|