Need help with creating table !!!!

From: NetScans <NetScans_at_ix.netcom.com>
Date: Thu, 27 May 1999 00:12:51 -0500
Message-ID: <374CD453.A44EBE21@ix.netcom.com>

I hope someone can help me.

I can create a table as follows:

Oracle8 Enterprise Edition Release 8.0.5.0.0 - Production With the Partitioning and Objects options PL/SQL Release 8.0.5.0.0 - Production

SQLWKS> CREATE TABLE UUT6469.TEST01 (DATE_TIME DATE NULL, PROCESS VARCHAR2(100) NULL, ELAPSED DATE NULL) PCTFREE 1 PCTUSED 99 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 16K NEXT 16K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 1) TABLESPACE FIN; Statement processed.

I can create a table like this as well:

SQLWKS> Declare
     2>
     3>  cur_id         INTEGER;
     4>  rows_processed NUMBER;
     5>  sql_text       VARCHAR2(2000);
     6>
     7> begin
     8>
     9>         sql_text := 'CREATE TABLE UUT6469.TEST01 (DATE_TIME DATE

NULL, PROCESS VARCHAR2(100) NULL, ELAPSED DATE NULL) PCTFREE 1 PCTUSED 99 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 16K NEXT 16K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 1) TABLESPACE FIN';

    10>         cur_id := DBMS_SQL.OPEN_CURSOR;
    11>         DBMS_SQL.PARSE(cur_id, sql_text, DBMS_SQL.NATIVE);
    12>         rows_processed := DBMS_SQL.EXECUTE(cur_id);
    13>         DBMS_SQL.CLOSE_CURSOR(cur_id);
    14>
    15> End;
    16>

Statement processed.

But when I place the same test in a procedure I get an error?

UUT6469.TEST_PROCEDURE:

cur_id INTEGER;
rows_processed NUMBER;
sql_text VARCHAR2(2000);

begin

sql_text := 'CREATE TABLE UUT6469.TEST01 (DATE_TIME DATE NULL, PROCESS VARCHAR2(100) NULL, ELAPSED DATE NULL) PCTFREE 1 PCTUSED 99 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 16K NEXT 16K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 1) TABLESPACE FIN';

        cur_id := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(cur_id, sql_text, DBMS_SQL.NATIVE);
        rows_processed := DBMS_SQL.EXECUTE(cur_id);
        DBMS_SQL.CLOSE_CURSOR(cur_id);

End;

SQLWKS> execute test_procedure;

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 491
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "UUT6469.TEST_PROCEDURE", line 11
ORA-06512: at line 2

I have DBA access and can not figure out why I get this error.

Thanks to all who read. Received on Thu May 27 1999 - 00:12:51 CDT