Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

DBMS_SQL package in a trigger

Posted on 2002-05-21
6
Medium Priority
?
733 Views
Last Modified: 2008-02-01
Hey gurus...

Is there a workaround for the following: (?)

I have a trigger (on insert) that wants to create a function (CREATE OR REPLACE FUNCTION fn ...)

I can build the function text in a string, then i try to use DBMS_SQL to open a cursor, parse the text, and close the cursor. (This is the same procedure that will work for normal DDL statement processing.)

But I'm getting an Oracle error 4092 - Cannot commit in a trigger.

Of course you can't commit in a trigger, but the DBMS_SQL package is in fact attempting it...  Is there a method of creating functions from a trigger that is different from the method i tried?

any help is appreciated.

randy
0
Comment
Question by:randyd
  • 3
  • 3
6 Comments
 
LVL 4

Expert Comment

by:asimkovsky
ID: 7024541
Yes, you can have your trigger call another procedure that contains the code to create your function. However, the trick is that the other procedure must be declared as AUTONOMOUS.  Anytime you execute a DDL statement, it causes an implicit commit, which is why you are getting this error.  Using an autonomous transaction creates another transaction independent of the first, and you can commit within that second transaction without affecting the state of the first one.


Andrew
0
 
LVL 3

Author Comment

by:randyd
ID: 7024572
hey andrew thanks..  

just before i got this response i entered:
PRAGMA AUTONOMOUS_TRANSACTION
in the top of my procedure and got past the initial COMMIT problem.. now it tells me i have insufficient privileges...

I have granted execute on DBMS_SQL and DBMS_SQL_SYS to the user who created the procedure and the trigger - and it still says insufficient privileges...

I'll be tracking this down - unless omeone else has a pointer...

I'll award points soon.
0
 
LVL 4

Accepted Solution

by:
asimkovsky earned 600 total points
ID: 7024654
Where in the package are you getting the error? Could it be with the CREATE FUNCTION statement? When you are issuing DDL from PL/SQL, the user issuing it must have the DDL privilege granted directly to him, and not through a role.


Andrew
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 3

Author Comment

by:randyd
ID: 7024851
yes i needed to grant create procedure directly to the creator of the trigger/procedure...  now it works...

a struggle :) but it does what i wanted when i started...

thanks andrew for the pointers. enjoy the points.

randy
0
 
LVL 4

Expert Comment

by:asimkovsky
ID: 7025625
Why just a grade of B? Did the answer not suit your needs?

Andrew

0
 
LVL 3

Author Comment

by:randyd
ID: 7025636
hey no offense..

i have reserved A grades on my few posts for the times people gave code examples

thanks again
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

971 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question