Oracle-L: RE: dynamic sql problem

From: Charu Joshi <joshic_at_mahindrabt.com>
Date: Tue, 28 Oct 2003 04:25:04 -0800
Message-ID: <F001.005D4CF7.20031028042504@fatcity.com>

Siddharth,

All roles are disabled in any named PL/SQL block (stored procedure, function, or

trigger) that executes with definer rights.

The SESSION_ROLES view shows all roles that are currently enabled. If a named

PL/SQL block that executes with definer rights queries SESSION_ROLES, the query

does not return any rows.

Named PL/SQL blocks that execute with invoker rights and anonymous PL/SQL

blocks are executed based on privileges granted through enabled roles.

So the problem might be that you have been granted 'CREATE TABLE' through a role and not directly.

Regards,

Charu.
  -----Original Message-----
  From: ml-errors_at_fatcity.com [mailto:ml-errors_at_fatcity.com]On Behalf Of Siddharth Haldankar
  Sent: 28 October 2003 17:09
  To: Multiple recipients of list ORACLE-L   Subject: dynamic sql problem

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

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

Disclaimer

This message (including any attachments) contains confidential information intended for a specific individual and purpose, and is protected by law. If you are not the intended recipient, you should delete this message and are hereby notified that any disclosure, copying, or distribution of this message, or the taking of any action based on it, is strictly prohibited.

Visit us at http://www.mahindrabt.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Charu Joshi
INET: joshic_at_mahindrabt.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). Received on Tue Oct 28 2003 - 06:25:04 CST