| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Dynamic Sql Problem
A copy of this was sent to Param <paramjit.sublok_at_citicorp.com>
(if that email address didn't require changing)
On Thu, 03 Sep 1998 13:35:40 +0530, you wrote:
>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.
>
roles are never enabled during the execution of a procedure.
Try this:
SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"
If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.
You probably have the privelege to do what you are trying to do in the procedure via a role. Grant the privelege directly to the owner of the procedure and it'll work.
grant CREATE SEQUENCE to <owner>
since you owned the sequence -- the drop (there is not "grant drop to <owner>" priv) works but the sebsequent create will fail since you do not have create sequence granted directly
>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
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Sep 03 1998 - 08:53:30 CDT
 |
 |