Solved

DBMS_SQL package in a trigger

Posted on 2002-05-21
6
704 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
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

Independent Software Vendors: 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!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

710 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