Re: Please help: create table with NDS PL/SQL.

"Ario Mihardja" <ario3_at_earthlink.net> wrote in message news:ny%n9.11734$OB5.1220249_at_newsread2.prod.itd.earthlink.net...
> Hi Readers,
>
> Subject: Execute DDL and DML with PL/SQL.
>
> I want to create a table called 'Dummy' within
> a procedure 'Temp'. The procedure got created. However,
> the table didn't get created. I executed the
> procedure 'Temp', but I got 'insufficient privileges'
> error. Anyone can tell me what could be the problem
> here? I am using Oracle 8i. How to do it properly
> so I know the table gets created?
> Thank you in advance for your help.
>
> Have a nice day,
> Ario Mihardja.
>
> ------------------------------------------------------------
> SQL>CREATE OR REPLACE PROCEDURE Temp IS
> 2 BEGIN
> 3 EXECUTE IMMEDIATE 'CREATE TABLE Dummy ( I INTEGER )';
> 4 END Temp;
> 5 /
>
> Procedure created.
>
> SQL> desc Dummy;
> ERROR:
> ORA-24372: invalid object for describe
>
>
> SQL> drop table Dummy;
> drop table Dummy
> *
> ERROR at line 1:
> ORA-00942: table or view does not exist
>
>
> SQL> execute scott.temp;
> BEGIN scott.temp; END;
>
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
> ORA-06512: at "SCOTT.TEMP", line 3
> ORA-06512: at line 1
>
>
> SQL> declare
> 2 begin
> 3 Temp;
> 4 end;
> 5 /
> declare
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
> ORA-06512: at "SCOTT.TEMP", line 3
> ORA-06512: at line 3
>
>
> SQL> execute temp;
> BEGIN temp; END;
>
> *
> ERROR at line 1:
> ORA-01031: insufficient privileges
> ORA-06512: at "SCOTT.TEMP", line 3
> ORA-06512: at line 1
>
>
> SQL> spool off;
> ---------------------------------------------
>
>

You need to have create table privilege. You have this privilege through a role.
Roles are ignored during compilation of stored procedures. Run this code as an anonymous block and you'll see it works in this fashion. In 8i, create the procedure with the 'authid current_user' (recommended) or grant the create table privilege directly to your user (you might end up with numerous privileges granted by SYS, that aren't in an export). BTW: this is a FAQ. Please try to search on the keyword 'insufficient privileges' or ora-1031, before posting.

Regards

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address