Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Creating a table with the DBMS_SQL package
To create a table out of an PL/SQL procedure, I found an example in
the Oracle docu, using the DBMS_SQL package:
CREATE OR REPLACE Procedure EXECUTESQL (SQLStatement IN VARCHAR2) IS
cursor_name INTEGER;
ret INTEGER;
BEGIN
cursor_name := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor_name, SQLStatement, DBMS_SQL.native);
ret := DBMS_SQL.Execute(cursor_name);
DBMS_SQL.CLOSE_CURSOR(cursor_name);
END; -- Procedure EXECUTESQL
With this procedure, I can execute SQL statements, e.g.:
exec EXECUTESQL('drop table test');
statement processed.
But if I try to create a table using this procedure, I get an error:
exec EXECUTESQL('create table test(name number)');
ORA-01031: insufficient privileges ORA-06512: at "SYS.DBMS_SYS_SQL", line 824 ORA-06512: at "SYS.DBMS_SQL", line 32 ORA-06512: at "EXECUTESQL", line 6 ORA-06512: at line 2
But if I execute a "create table" statement without my procedure, it works - so I think it cannot be a problem with insufficient create-table-privilege.
Can anyone help?
Thank you in advance,
Ralf
Received on Tue Sep 18 2001 - 03:44:08 CDT