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)
>
>
>
Received on Thu Mar 19 1998 - 00:00:00 CST
