Dynamic Sql Problem

From: Param <paramjit.sublok_at_citicorp.com>
Date: Thu, 03 Sep 1998 13:35:40 +0530
Message-ID: <35EE4DD4.AD3BE8F4@citicorp.com>

Hi,

I have to write a small Stored Procedure which will drop and create a sequence.

But I am facing a problem. This same SP works fine if the statement is a 'DROP' but
for a 'CREATE'.

I am getting error 1031 - Insufficient Privileges for a create statement.
I am able to CREATE and DROP everything from the SQL Prompt.

I have also tried executing the very same code as a PL/SQL Block and it works.
Beats me why it does not work from inside a procedure.

Source of the SP is :

---

CREATE OR REPLACE PROCEDURE asi_CreateSeq ( pSeqName IN CHAR ) as

   lCid INTEGER ;
   lSqlStmt CHAR(100) ;

BEGIN    lCid := DBMS_SQL.OPEN_CURSOR ;
   lSqlStmt := 'create SEQUENCE '||pSeqName ;    DBMS_OUTPUT.PUT_LINE( 'STMT : '||lSqlStmt ) ;

   DBMS_SQL.PARSE( lCid, pSeqName, dbms_sql.native ) ;

   DBMS_SQL.CLOSE_CURSOR( lCid ) ;

EXCEPTION
   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE( 'Error. Retcode = '||sqlcode ) ;
      DBMS_SQL.CLOSE_CURSOR( lCid ) ;

END ;
/

---

Please folks, I hope somebody can help me as this is very urgent.

Thanks in advance,
Param Received on Thu Sep 03 1998 - 03:05:40 CDT