RE: dynamic sql problem

Your account probably has the create table privilege granted through the resource role, grant create table to your account and try again, privileges granted through a role are not active when running a procedure.

-----Original Message-----
Sent: 28 October 2003 11:39
To: Multiple recipients of list ORACLE-L

Hi Gurus,  

I have problem running Dynamic SQL through a package, though it runs fine in a unnamed block.  

This is the sample code

DECLARE lv_sql_stmt VARCHAR2(2000);

begin

   lv_sql_stmt := 'create table a_temp (a number)';

   EXECUTE IMMEDIATE lv_sql_stmt;

end;

/  

This runs fine.  

But as soon as I put this inside a package I get an error

PROCEDURE test

is

lv_sql_stmt VARCHAR2(2000);

begin

   lv_sql_stmt := 'create table a_temp (a number)';

   EXECUTE IMMEDIATE lv_sql_stmt;

end;  

ERROR at line 1:

ORA-01031: insufficient privileges

ORA-06512: at "COMMADM.CT_REFRESH_PK", line 415

ORA-06512: at line 1  

This line 415 is the execute immediate line.  

Any clues why this is acting strangely.  

Thanks in advance for your time in answering to my query    

With Warm Regards

Siddharth Haldankar

Zensar Technologies Ltd.

Cisco Systems Inc.

(Offshore Development Center)

# : 091 020 4128394

shaldank_at_cisco.com

s.haldankar_at_zensar.com  

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: McBain, Neil  SITI-ITDIEEE
  INET: Neil.McBain_at_shell.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).