Re: Need help with creating table !!!!

A copy of this was sent to NetScans <NetScans_at_ix.netcom.com> (if that email address didn't require changing) On Thu, 27 May 1999 00:12:51 -0500, you wrote:

>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.
>------------------------------------------------------------------------------------------------
>

[snip]

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

[snip]
>
>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.

roles are never enabled during the execution of a procedure.

Try this:

SQL> set role none;
SQL> "statement you want to test to see if it'll work in a procedure"

If you can do it in plus with no roles you can do it in a procedure. If you can't, you must have the privelege from a role and hence won't be able to do it in a procedure.

You probably have the privelege to do what you are trying to do in the procedure via a role. Grant the privelege directly to the owner of the procedure and it'll work.

grant create table to <owner>;

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu May 27 1999 - 07:10:47 CDT