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

Thank you for your reply. I did
replace the procedure with
anonymous block. It works fine
now.

Ario Mihardja.

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:uq16q8iaaoro7f_at_corp.supernews.com...
>
> "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
>
>
>
Received on Mon Oct 07 2002 - 12:27:25 CDT