Solved

DBMS_SQL package in a trigger

Posted on 2002-05-21
6
690 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 200 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

756 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