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 [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
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 HaldankarSent: 28 October 2003 17:09To: Multiple recipients of list ORACLE-LSubject: 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
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-From: Siddharth Haldankar [mailto:[EMAIL PROTECTED]Sent: 28 October 2003 11:39To: Multiple recipients of list ORACLE-LSubject: 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]
RE: dynamic sql problem
Title: Mensagem to use it inside packages u must have some priviledges given directly to u not to a role. Regards PG -Mensagem original-De: Siddharth Haldankar [mailto:[EMAIL PROTECTED] Enviada: terça-feira, 28 de Outubro de 2003 11:39Para: Multiple recipients of list ORACLE-LAssunto: 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]
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]
Dynamic SQL problem (Oracle 9<->8)
Title: Dynamic SQL problem (Oracle 9<->8)
Hello,
I have software compiled in Oracle 9 environment. And when I try to fetch data using
different versions of Oracle client/server I receive ORA-errors:
1. client 9 --> server 8: "ORA-00932: inconsistent datatypes"
2. client 8 --> server 9: "ORA-01455: converting column overflows integer datatype"
These errors do not occur when connecting client 9 to server 9.
I use the following example code (similar to example in Oracle 9 documentation):
EXEC SQL BEGIN DECLARE SECTION;
int intn_b;
VARCHAR buffer[6];
VARCHAR cursor_def[500];
EXEC SQL END DECLARE SECTION;
strcpy((char*)cursor_def.arr, "SELECT tstintn,tstcode FROM test WHERE tstcode LIKE 'AU%'");
cursor_def.len = strlen((char*)cursor_def.arr);
EXEC SQL PREPARE S FROM :cursor_def;
EXEC SQL DECLARE My_cursor CURSOR FOR S;
EXEC SQL OPEN My_cursor ;
EXEC SQL WHENEVER NOT FOUND DO break;
for (;;)
{
int i=0;
EXEC SQL FETCH My_cursor INTO :intn_b, :buffer;
buffer.arr[buffer.len] = '\0';
printf("%6d %7s\n", intn_b, buffer.arr);
}
EXEC SQL CLOSE My_cursor;
When I declare cursor without using dynamic SQL:
EXEC SQL DECLARE My_cursor CURSOR FOR
SELECT tstintn, tstcode FROM test WHERE tstcode LIKE 'AU%';
everything works fine.
Does anyone know how to solve this problem?
Regards,
Rafal Wojnar
e-mail: [EMAIL PROTECTED]
RE: dynamic sql problem
Hi there, It could just be that you are missing your semicolons ';' off the end of your statements. Cheers, Kev. __ Kevin Thomas Technical Analyst Deregulation Services Calanais Ltd. Tel: 0141 568 2314 Fax: 0141 568 2366 http://www.calanais.com -Original Message- Sent: 06 August 2001 21:57 To: Multiple recipients of list ORACLE-L HI, I am running a stored procdures which contains following dynamic sql ..i am getting error: ORA-00936 missing expression. for the bold statement while rest of statements r properly executing.. what might be the reason.. and temp_ are variables declared.. and name1,name2,name3 are dynamically generated table names.. str := 'alter table'||' '||name2||' '||'disable constraint'||' '||temp1_cons; execute immediate str; str := 'alter table'||' '||name3||' '||'disable constraint'||' '||temp_cons; execute immediate str; str :='update '||' '||name2||' '||' set id_sess=id_sess + '||' '||temp_id_sess_2; execute immediate str; str :='update '||' '||name3||' '||' set id_sess = id_sess +'||' '||temp_id_sess_3; execute immediate str; str :='create table'||' '||enum||' '||'as select * from'||' '||name1||' '||'union select * from'||' '||name2||' '||'union select * from'||' '||name3; execute immediate str; Thanks Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Thomas, Kevin INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
Re: dynamic sql problem
Which is the problem statement? I didn't see the bold. David A. Barbour Oracle DBA, OCP AISD 512-414-1002 Harvinder Singh atech.com> cc: Sent by:Subject: dynamic sql problem [EMAIL PROTECTED] 08/06/2001 03:57 PM Please respond to ORACLE-L HI, I am running a stored procdures which contains following dynamic sql ..i am getting error: ORA-00936 missing expression. for the bold statement while rest of statements r properly executing.. what might be the reason.. and temp_ are variables declared.. and name1,name2,name3 are dynamically generated table names.. str := 'alter table'||' '||name2||' '||'disable constraint'||' '||temp1_cons; execute immediate str; str := 'alter table'||' '||name3||' '||'disable constraint'||' ' ||temp_cons; execute immediate str; str :='update '||' '||name2||' '||' set id_sess=id_sess + '||' '||temp_id_sess_2; execute immediate str; str :='update '||' '||name3||' '||' set id_sess = id_sess +'||' '||temp_id_sess_3; execute immediate str; str :='create table'||' '||enum||' '||'as select * from'||' '||name1||' '||'union select * from'||' '||name2||' '||'union select * from'||' '||name3; execute immediate str; Thanks Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
dynamic sql problem
HI, I am running a stored procdures which contains following dynamic sql ..i am getting error: ORA-00936 missing expression. for the bold statement while rest of statements r properly executing.. what might be the reason.. and temp_ are variables declared.. and name1,name2,name3 are dynamically generated table names.. str := 'alter table'||' '||name2||' '||'disable constraint'||' '||temp1_cons; execute immediate str; str := 'alter table'||' '||name3||' '||'disable constraint'||' '||temp_cons; execute immediate str; str :='update '||' '||name2||' '||' set id_sess=id_sess + '||' '||temp_id_sess_2; execute immediate str; str :='update '||' '||name3||' '||' set id_sess = id_sess +'||' '||temp_id_sess_3; execute immediate str; str :='create table'||' '||enum||' '||'as select * from'||' '||name1||' '||'union select * from'||' '||name2||' '||'union select * from'||' '||name3; execute immediate str; Thanks Harvinder -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services-- (858) 538-5051 FAX: (858) 538-5051 San Diego, California-- Public Internet access / Mailing Lists To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).
