Re: Using EXCUTE IMMEDIATE in PL/SQL

In article <8ijs1g$d32$1_at_perki.connect.com.au>,   "Tom Zamani" <tomz_at_redflex.com.au> wrote:
> Do you have grant to create table?
> Tom
> Alexandros Kotsiras, NYC <alexandros_k_at_prodigy.net> wrote in message
> news:394D6F73.C4005C54_at_prodigy.net...
> > CREATE OR REPLACE PROCEDURE TEST_PROC AS
> > begin
> > execute immediate 'create table TEST_1 as select * from TEST ' ;
> > end;
> >
> > I receive :
> > ORA-01031 Insufficient privileges
> >
> > Why that ? ?
> > I do not have problems with procedures that do not have "execute
> > immediate" inside.
> > I am the owner of the procedure and the TEST table.
> > If i execute the create table statement directly from SQL*Plus it
 works
> > fine ....
> >
> > The description of the error message in the documentation doesn't
 really
> > help.
> >
>
>

I have tried this and the only user account that can create this procedure and allow it to execute without failure is SYS. Apparently there are inherent permissions to the sysdba user that are missing and not otherwise available or grantable to any other user account, DBA or not. If you absolutely must have 'execute immediate' in your procedures you will likely need to create them with the SYS account (connect internal in Server Manager) then create public synonyms for them and grant execute to public on them.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.