RE: dynamic sql problem

[Siddharth Haldankar]

Thanks for all those who answered Using authid current_user in package has solved my problem.   With Warm Regards Siddharth Haldankar Zensar Technologies Ltd. Cisco Systems Inc. (Offshore Development Center) #  : 091 020 4128394 [EMAIL PROTECTED] [EMAIL PROTECTED] -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Charu Joshi Sent: Tuesday, October 28, 2003 5:55 PM To: Multiple recipients of list ORACLE-L Subject: RE: dynamic sql problem   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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]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 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 [EMAIL PROTECTED] [EMAIL PROTECTED]   ********************************************************* 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