| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: dynamic sql problem
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
shaldank_at_cisco.com
s.haldankar_at_zensar.com
-----Original Message-----
Charu Joshi
Sent: Tuesday, October 28, 2003 5:55 PM
To: Multiple recipients of list ORACLE-L
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-----
Siddharth Haldankar
Sent: 28 October 2003 17:09
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
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: Siddharth Haldankar INET: shaldank_at_cisco.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-LReceived on Wed Oct 29 2003 - 08:04:26 CST
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |