Re: DBMS_SQL insufficient privileges

A copy of this was sent to Marco Minio <m.minio_at_venis.it> (if that email address didn't require changing) On Tue, 26 Oct 1999 09:32:24 +0200, you wrote:

>
>I tried to create a Stored Procedure for DDL using DBMS_SQL.
>Actually I copied it from the Oracle documentation.
>The procedure is:
>

see the paper "Why do I get an ORA-1031 or PL-201 with stored procedures " located at the web site in my signature below.

>CREATE OR REPLACE PROCEDURE exec(STRING IN varchar2)
>AS cursor_name INTEGER;
>ret INTEGER;
>BEGIN
>cursor_name := DBMS_SQL.OPEN_CURSOR;
>
>DBMS_SQL.PARSE(cursor_name, string, DBMS_SQL.V7);
>ret := DBMS_SQL.EXECUTE(cursor_name);
>DBMS_SQL.CLOSE_CURSOR(cursor_name);
>END;
>
>I created it in the schema T00
>Now connecting with user T00 the following PL-SQL code:
>
>begin
>exec ('create table a (b varchar2(10))');
>end;
>
>returns the following error:
>
>ORA-01031: insufficient privileges
>ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
>ORA-06512: at "SYS.DBMS_SQL", line 32
>ORA-06512: at "T00.EXEC", line 8
>ORA-06512: at line 2
>
>But the repeating the code of the SP in a PL-SQL block all
>Works well:
>
>declare
>cursor_name INTEGER;
>ret INTEGER;
>BEGIN
>cursor_name := DBMS_SQL.OPEN_CURSOR;
>
>DBMS_SQL.PARSE(cursor_name, 'create table a (b varchar2(10))',
>DBMS_SQL.V7);
>ret := DBMS_SQL.EXECUTE(cursor_name);
>DBMS_SQL.CLOSE_CURSOR(cursor_name);
>END;
>
>Since I know that though a SP you can’t use privileges granted via roles
>I suppose
>I need some privileges in SYS schema.
>
>Thanks in advance for your help.
>
>MM.
>

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Oct 26 1999 - 08:16:08 CDT