Re: Need help with creating table !!!!

From: Philippe <parnaud_at_yahoo.com>
Date: Thu, 27 May 1999 09:01:21 +0200
Message-ID: <7iiqkc$29a$1@concorde.ctp.com>

Your privileges are grant through roles and they have to be granted = directly to the procedure owner

NetScans wrote in message <374CD453.A44EBE21_at_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 - 02:01:21 CDT