Re: Creating a table with the DBMS_SQL package

The user you are using does not have enough rights to execute certain things out of dbms_sql

using a 'bigger' user results in this:

SQL> exec EXECUTESQL('create table test(p1 number)') BEGIN EXECUTESQL('create table test(p1 number)'); END;

*
ERROR at line 1:

ORA-01003: no statement parsed
ORA-06512: at "SYS.DBMS_SYS_SQL", line 1092
ORA-06512: at "SYS.DBMS_SQL", line 327
ORA-06512: at "APPS.EXECUTESQL", line 7
ORA-06512: at line 1


SQL> desc test
 Name                            Null?    Type
 ------------------------------- -------- ----
 P1                                       NUMBER

even though there is an error message the statement has been executed

hth

Marc

"Ralf Zwanziger" <goldensurfer_at_gmx.de> wrote in message news:3ba70791.96092353_at_news.fth.sbs.de...
> 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 - 05:00:54 CDT