Re: Cannot create a new table from PL/SQL

After parsing, you must use DBMS_SQL.EXECUTE. (to executed the DDL). Hope this helps.
In <56oshv$g0e_at_clam.niwa.cri.nz> d.cook_at_niwa.cri.nz (Dave Cook) writes:

>
>Hi,
>
>As a new comer to PL/SQL programming I have having some problems
>creating a table from PL/SQL. I don't want to use PL/SQL tables, but
>want to create a completely new table which I can then query from
>outside PL/SQL (eg; from ARCINFO or Microsoft Excel).
>
>The PL/SQL code is :
>
>CREATE OR REPLACE PROCEDURE make_table (
> sql_statement IN VARCHAR2)
>IS
> cursor_handle INTEGER;
>BEGIN
> DBMS_OUTPUT.PUT_LINE (sql_statement);
> cursor_handle := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.PARSE (cursor_handle,sql_statement, DBMS_SQL.V7);
> DBMS_SQL.CLOSE_CURSOR (cursor_handle) ;
>END;
>/
>
>This procedure compiles OK.
>
>I then call it from SQLPLUS :
>
>BEGIN
> make_table ('CREATE TABLE ddd (pointno NUMBER(9,0))
> TABLESPACE data1');
>END;
>/
>
>I now get the following error messages ::
>
>BEGIN
>*
>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 "NZOIDATA.MAKE_TABLE", line 8
>ORA-06512: at line 2
>
>The user has full DBA access to a tablespace called data1. I can
>create tables from SQLPLUS.
>
>I have had a quick look in the SYS.DBMS_SYS_SQL and SYS.DBMS_SQL
>functions, but got no information that helped me.
>
>I have a similar piece of PL/SQL that can drop tables from this
>tablespace, so I am positive that I should have enough permissions to
>create/modify/drop tables ....
>
>Any ideas, advice, or examples of PL/SQL code that creates new
>tables??
>
>Thanks,
>
>
>
>
Received on Fri Nov 22 1996 - 00:00:00 CET