Re: Permissions problem when attempting to execute a procedure (Solved!)

Bingo! After adding the permission directly to the user, the procedure executed without any problems.

One thing that I have noticed is that DDL statements executed via DBMS_SQL seem to be implicitly committed, which means I apparently can't use this approach to apply a series of schema changes inside a transaction from a PL/SQL script. Is there a better way?

Thanks for the informative reply,

--
Randy Baker (remove Z from address in email replies)

Andrew Barnett wrote in message
<01bd5392$58ee8840$8c0564a1_at_azmelw1358.mel.az.bp.com>...


>your user has the privilege to create triggers through the resource role.

>privileges granted through roles don't work in stored procs and views.

>therefore, you need to get a dba to grant "create trigger" directly to your

>user.

>--

>Andrew - Wizzard

>

>barnetaj_at_bp.com

>

>Randy Baker <rsbakerZ_at_msn.com> wrote in article

><6es6gq$39g_at_ecuador.earthlink.net>...

>> I've been writing scripts to distribute to our clients to make any schema

>> changes required between versions of our applications.

>>

>> One of the most frequent things I have to do is create a simple trigger

>> which inserts a sequence value as the primary key of a table.

>>

>> Rather than include the trigger text in each script which has to create a

>> trigger, I created a procedure to do the job for me. The procedure

 generates


>> the code for the trigger and passes it to DBMS_SQL.Parse() to perform the

>> update. However, if I try to

>> execute the procedure, I get an error about insufficient priviledges:

>>

>> ORA-01031: insufficient privileges

>> ORA-06512: at "SYS.DBMS_SYS_SQL", line 491

>> ORA-06512: at "SYS.DBMS_SQL", line 32

>> ORA-06512: at "CRIBSAMPLE.WXDB_CREATESEQUENCETRIGGER", line 30

>> ORA-06512: at line 2

>>

>> In this case, I am logged in as CRIBSAMPLE, and CRIBSAMPLE owns its

 default


>> table space.

>>

>> Note that if I execute the trigger creation text generated by the

 procedure


>> directly from the worksheet, the trigger is created without any problems.

>> The procedure was also created properly via the same  worksheet while

 logged


>> in as the same user.

>>

>> I'm somewhat baffled. Summarizing...

>>

>> 1. I can create a trigger

>> 2. I can create a procedure which generate the same text as used to

 create


>> the trigger in step (1).

>> 3. I *can't* call the procedure I created.

>>

>> --

>> Randy Baker (remove Z from address in email replies)

>>

>>

>>