OraFAQ Forum: SQL & PL/SQL » DDL using PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » DDL using PL/SQL

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

DDL using PL/SQL [message #39740]

Tue, 06 August 2002 15:30

IndyWest
Messages: 6
Registered: July 2002

Junior Member

Howdy folks,

I have ended up with an Insufficient Privileges problem while using DDL in my PL/SQL routine. The purpose is to drop and create a sequence again. While Oracle goes through the Drop correctly, it fails on the Create with an "ORA-01031: insufficient privileges" option. The current user falls under the dba role. Here's the code:

=====
i:= DBMS_SQL.Open_Cursor;
strSQL:= 'Drop Sequence My_Seq';
DBMS_SQL.Parse(i, strSQL, DBMS_SQL.NATIVE);
j:= DBMS_SQL.Execute(i);
DBMS_SQL.Close_Cursor(i);

i:= DBMS_SQL.Open_Cursor;
strSQL:= 'Create Sequence My_Seq Increment By 1 Start With 1 MinValue 1 MaxValue 99999999 NoCycle NoOrder NoCache';
DBMS_SQL.Parse(i, strSQL, DBMS_SQL.NATIVE);
j:= DBMS_SQL.Execute(i);
DBMS_SQL.Close_Cursor(i);
========

It fails on the second execute statement with the message. I was able to execute both of these successfully outside the program. Any pointers? Or is there a better method?

Thanks a bunch.

Report message to a moderator

Re: DDL using PL/SQL [message #39744 is a reply to message #39740]

Tue, 06 August 2002 21:43

Rajarshi
Messages: 11
Registered: November 2001

Junior Member

Use the package DBMS_DDL instead of DBMS_SQL.... or use "EXECUTE IMMEDIATE" to do the DDL job. DBMS_SQL works file with SELECs and DMLs but notwith DDLs.

Report message to a moderator

Re: DDL using PL/SQL [message #39750 is a reply to message #39740]

Wed, 07 August 2002 04:49

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

you can use dbms_sql or
execute immediate for DDL operations.
execute immediate is more easy.
but the problem you are facing, is with privs.
make sure, u have granted the create sequence priv explicitly to the user executing the above piece of code.
dynamic sql wont see, the privs granted over the roles.
for further enhancement, you may also look into
using

authid current_user

clause.
if this ddl is issued from a trigger, u got to use a stored procedure to do this along with an autonomous transcation.

Report message to a moderator

Re: DDL using PL/SQL [message #39762 is a reply to message #39740]

Wed, 07 August 2002 14:52

IndyWest
Messages: 6
Registered: July 2002

Junior Member

Thanks Mahesh and Rajarshi. Actually I don't use Ora 8i so perhaps Execute Immediate isn't one of the options. DBMS_DDL is one I should try out though.

However, I did find another link in OraFaq which actually suited by needs much much better! I used Alter Sequence with a negative increment and it works as a charm!

Thanks all the same!

Report message to a moderator

Previous Topic:	SELECT DISTINCT command
Next Topic:	week of year calc

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 04:53:28 CDT 2024