Re: Create sequence via DBMS SQL fails

In article <Sh6S5.41$p86.1890_at_news.get2net.dk>,   "Jan Lund" <JANL_at_Lundbeck.Com> wrote:
> Dear all.
>
> I have a problem using DBMS_SQL. I try to Drop a sequence, which
 works fine,
> but when i try to re-create it, it fails with an "Insufficent
 Privleges"
> error.
>
> What am i doing wrong ?
>
> Function1 works :
> CID:=DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(CID,'DROP SEQUENCE PHARM_DB.TESTSEQ',
 DBMS_SQL.NATIVE);
> RET:=DBMS_SQL.EXECUTE(CID);
>
> Function2 fails with an "Insufficent Privleges" error :
>
> CID:=DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE(CID, 'CREATE SEQUENCE PHARM_DB.TESTSEQ
> START WITH 0
> INCREMENT BY 1
> NOMAXVALUE
> MINVALUE 0
> NOCYCLE
> NOCACHE
> ORDER',DBMS_SQL.NATIVE);
> RET:=DBMS_SQL.EXECUTE(CID);
>
> Rgds
> Jan
>
>

This is a FAQ. When you get the 'insufficient privileges' message, you have to be aware roles are ignored during compilation of a stored procedure (verify this by running the same code using an anonymous block and you will see it will work).
You either need to grant create sequence or create any sequence privilege to the owner of the sp *directly*, or you need to run it (8i and higher) with invokers rights instead of definers rights (which is the default).

Hth,

--
Sybrand Bakker, Oracle DBA

All standard disclaimers apply
------------------------------------------------------------------------


Sent via Deja.com http://www.deja.com/
Before you buy.