Re: dynamic DML statements in stored procedures

On Thu, 02 Jul 1998 14:51:10 +0200, Cristian Veronesi <c.veronesi_at_crpa.it> wrote:

>Greetings.
>I'm using Oracle Server 7.2.2.4 and I wish to create a stored procedure
>that runs a dynamic DML statement, as shown in the "Oracle 7 Server
>Application Developer's Guide".
>
>This is the content of my PL/SQL script (file ptest.sql)
>
>create or replace procedure ptest
>as
> TblStatement varchar2(32000);
> c integer ;
>begin
> TblStatement := 'CREATE TABLE TTEST (CTEST VARCHAR2(30))' ;
> c := dbms_sql.open_cursor;
> dbms_sql.parse(c, TblStatement, dbms_sql.v7) ;
> dbms_sql.close_cursor(c);
>end ;
>/
>
>and this what it happens :
>
>SQL> start ptest
>
>Procedure created.
>
>SQL> execute ptest
>begin ptest; end;
>
>*
>ERROR at line 1:
>ORA-01031: insufficient privileges
>ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
>ORA-06512: at "SYS.DBMS_SQL", line 25
>ORA-06512: at "INFO2000.PTEST", line 8
>ORA-06512: at line 1
>
>Notice that if I modify the PL/SQL script this way:
>
>-- create or replace procedure ptest
>-- as
>declare
> TblStatement varchar2(32000);
> c integer ;
>begin
> TblStatement := 'CREATE TABLE TTEST (CTEST VARCHAR2(30))' ;
> c := dbms_sql.open_cursor;
> dbms_sql.parse(c, TblStatement, dbms_sql.v7) ;
> dbms_sql.close_cursor(c);
>end ;
>/
>
>and I run it as an anoymous PL/SQL block, it works !
>
>SQL> start ptest
>
>PL/SQL procedure successfully completed.
>
>SQL> desc ttest
> Name Null? Type
> ------------------------------- -------- ----
> CTEST VARCHAR2(30)
>
>Any suggestions?

I assume the database user has the role resource. This allows them to create database objects. During the execution of PL/SQL roles are not enabled. So the database user needs to have the privledge CREATE TABLE grant directly to them.

SQL> grant create table to <database user>

If you ever want to test and see if something that the user can do in SQL*PLUS is becuase of a role or a privledge granted directly to them, try...

SQL>set role none;

This will disable all roles for that user. Then try running your command from SQL*PLUS. If it works, then it will work in PL/SQL.

>
>Best regards,
>Cristian Veronesi
>
>--------------------------------------------------------------------------------------------------------------
>
>Cristian Veronesi - computer programmer
>CRPA spa - Centro Ricerche Produzioni Animali / Research Centre for
>Animal Production
>Reggio Emilia, Italy
>e-mail: c.veronesi_at_crpa.it
>www: http://www.crpa.it/
>
Received on Thu Jul 02 1998 - 09:11:54 CDT