dynamic DML statements in stored procedures

From: Cristian Veronesi <c.veronesi_at_crpa.it>
Date: Thu, 02 Jul 1998 14:51:10 +0200
Message-ID: <359B823E.8AECD8E0@crpa.it>

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?

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 - 07:51:10 CDT