Dynamic DDL statements in Stored procedure...

From: Amar Kumar Padhi <TS2017_at_emirates.com>
Date: Mon, 18 Sep 2000 13:30:14 +0400
Message-Id: <10623.117230@fatcity.com>

Hi,
can I dynamically create a table, using dbms_sql, from a stored procedure? I am getting the following error. If I run the routine directly at the sqlprompt, it is executed successfully, but fails as a stored procedure.

create or replace procedure am_test is

	Z	integer;
	Y	integer;
        x	number;
        l_ddl   varchar2(100);
begin
	l_ddl := 'create table am_test_table (x number)';
	dbms_output.put_line(l_ddl);
	Z := dbms_sql.open_cursor ;
	dbms_sql.parse(Z, l_ddl, dbms_sql.native);
	Y := dbms_sql.execute(Z);
	dbms_sql.close_cursor(Z);

End;

sql> exec am_test

ERROR at line 1:

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 239
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "RAPID.AM_TEST", line 11
ORA-06512: at line1

Thanks,
Amar
00-971-50-7883254
ts2017_at_emirates.com
amar_padhi_at_hotmail.com Received on Mon Sep 18 2000 - 04:30:14 CDT