Cannot create a new table from PL/SQL

Hi,

[Quoted] [Quoted] 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 Mon Nov 18 1996 - 00:00:00 CET