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
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]
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
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: where do you draw the line?
Thanks Arup. and thanks to all who replied to this thread. And if anyone is interested, I found a two-part article on SQL Injection and Oracle by Pete Finnigan at: http://www.securityfocus.com/infocus/1644 http://www.securityfocus.com/infocus/1646 -Original Message- Sent: Wednesday, June 25, 2003 5:45 PM To: Multiple recipients of list ORACLE-L Thomas In addition to the responses posted by others, regarding the thrashing of shared pool with zillions of once-used code, there is another serious security hole - SQL Injection. You mentioned this is ging to be your eCommerce application database - and will probably attract the hackers like bees to honey. In your example, if the parameter p_site_guide is passed a value X' or 'X'='X Note there is no single quote before or after the value. The dynamic sql will, in addition to all the other parameters, accept this as a valid where clause and rewrite the query as DELETE FROM some_table WHERE ASSET_GUID IN ( '...') AND OBJECT_ID IN (...) AND SITE_GUID = 'X' OR 'X' = 'X'; what do you think the result is going to be? Your developers are not going to stop at delete only - they will explore this to SELECTs as well. The seriousness of the problem grows - the injectied code could have X' UNION ALL SELECT CUST_NAME, CREDIT_CARD_NUM, EXP_DATE... Now in addition to the good old data, the hacker also got some more (and unauthorized) valuable knowledge. Try to show them the examples and prove how dangerous the practice of dynamic sql is. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 11:04 AM I've been fighting an ongoing war with our ecommerce developers, who are inordinately fond of writing dynamic SQL code that neglects to incorporate bind variables. Researching AskTom I've found and utilized different techniques to force bind variables into these dynamic SQL queries, including the use of application contexts, object types, etc. However, I'm wondering if I'm making things worse, essentially providing them with band-aids, when I should be forcing them to change the way they code. Consider the sample code below (which is a relatively simple example), which is a generic DELETE statement generator. In this situation, the programmers claim the following code is good programming practice, promotes ease of maintenance, less buggy, and promotes code reusability (their definition of reusability is a bit different from mine). I disagreed with them -- not only is this code not reusable at all, with the parsing overhead consequences, it's also harder to debug and tune for performance, due to all the permutations that needs to be tested. My take was that they be far better off writing a simple static DELETE statement for each table. Their rejoinder -- it's not worth writing lots of redundant code at the expense of 'minimal' gains in performance.Now, this code *could* be rewritten to use the SYS_CONTEXT function on the p_object_id and p_site_guid to force a bind variable on those two conditions, but the IN condition with respect to the p_asset_guid would be more problematic. However, I don't feel we should have to be resorting to such measures to get this code to using bind variables. So, I'm at the point of denying such code to be migrated to production. I recognize that there are situations where there is a legitimate need for dynamic SQL, but the SQL has to be written w/o catenating literal SQL -- and if it can't -- they need to go back to square one. Opinions? I'm curious -- do you have policies/standards with respect to dynamic SQL? CREATE OR REPLACE PROCEDURE test ( p_Asset_GuidIN VARCHAR2, p_Object_Id IN VARCHAR2, p_Object_Definition IN VARCHAR2, p_Site_Guid IN VARCHAR2, p_resultIN OUT VARCHAR2 ) AS strTableName VARCHAR2(100); strWhere VARCHAR2(100); strQuery LONG; BEGIN IF p_Object_Definition = 'PRODUCT' THEN strTableName := ' TNE.GPD_PRODUCT_ASSET '; strWhere := ' MODEL_NO '; ELSIF p_Object_Definition = 'CARACTERISTIC' OR p_Object_Definition = 'CHARACTERISTIC' THEN strTableName := ' TNE.GPD_CARACTERISTIC_ASSET '; strWhere := ' CARACTERISTIC_ID '; ELSIF p_Object_Definition = 'CATEGORY' THEN strTableName := ' TNE.GPD_CATEGORY_ASSET '; strWhere := ' CATEGORY_GUID '; ELSIF p_Object_Definition = 'VALUE' THEN strTableName := ' TNE.GPD_VALUE_ASSET '; strWhere := ' VALUE_ID '; ELSIF p_Object_Definition = 'PRODUCT_NODE' THEN strTableName := ' TNE.GPD_PRODUCT_NODE_ASSET '; strWhere := ' PRODUCT_NODE_GUID '; ELSIF p_Object_Definition = 'CARAC_GROUP' THEN strTableName := ' TNE.GPD_CARAC_GROUP_ASSET '; strWhere := ' CARAC_GROUP_GUID '; END IF; strQuery := ' DELETE FROM '
RE: Dynamic SQL: where do you draw the line?
Thomas, First question, how did you manage to convince your WEB lackeys to use stored procedures in the first place? I've been fighting that battle for 4 years now with our CIM group they refuse to budge. Actually I should say, one of their folks refuses to budge. Anyway, to your question. 4 years ago our CIM folks were into 100% dynamic SQL in their C language based transaction servers. I had literally millions of distinct statements running around in the shared pool that had one and only one execution, period. The transition point came when they started getting Oracle errors concerning shared pool allocation, wonder why. Anyway I gave them one of two options. 1) change your code to used passed parameters, 2) we shutdown the database server for 2 hours to add memory adjust the shared pool. Now this is suppose to be a 24x7 manufacturing line they were seen as the villains in this case by the mfg folks so they were not very happy with option 2. Consequently they went back to the offending transaction server re-wrote it to use passed parameters. Then shut it down restarted it. Total down time 5 minutes, which was actually not true as the mfg cells just queued transactions in their mailboxes. Problem gone with that serv! er, so they started re-writing all of them as needed. Sometimes you just have to push the pain back on the developer in a way in which they just can't avoid it. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -Original Message- Sent: Wednesday, June 25, 2003 11:05 AM To: Multiple recipients of list ORACLE-L I've been fighting an ongoing war with our ecommerce developers, who are inordinately fond of writing dynamic SQL code that neglects to incorporate bind variables. Researching AskTom I've found and utilized different techniques to force bind variables into these dynamic SQL queries, including the use of application contexts, object types, etc. However, I'm wondering if I'm making things worse, essentially providing them with band-aids, when I should be forcing them to change the way they code. Consider the sample code below (which is a relatively simple example), which is a generic DELETE statement generator. In this situation, the programmers claim the following code is good programming practice, promotes ease of maintenance, less buggy, and promotes code reusability (their definition of reusability is a bit different from mine). I disagreed with them -- not only is this code not reusable at all, with the parsing overhead consequences, it's also harder to debug and tune for performance, due to all the permutations that needs to be tested. My take was that they be far better off writing a simple static DELETE statement for each table. Their rejoinder -- it's not worth writing lots of redundant code at the expense of 'minimal' gains in performance.Now, this code *could* be rewritten to use the SYS_CONTEXT function on the p_object_id and p_site_guid to force a bind variable on those two conditions, but the IN condition with respect to the p_asset_guid would be more problematic. However, I don't feel we should have to be resorting to such measures to get this code to using bind variables. So, I'm at the point of denying such code to be migrated to production. I recognize that there are situations where there is a legitimate need for dynamic SQL, but the SQL has to be written w/o catenating literal SQL -- and if it can't -- they need to go back to square one. Opinions? I'm curious -- do you have policies/standards with respect to dynamic SQL? CREATE OR REPLACE PROCEDURE test ( p_Asset_GuidIN VARCHAR2, p_Object_Id IN VARCHAR2, p_Object_Definition IN VARCHAR2, p_Site_Guid IN VARCHAR2, p_resultIN OUT VARCHAR2 ) AS strTableName VARCHAR2(100); strWhere VARCHAR2(100); strQuery LONG; BEGIN IF p_Object_Definition = 'PRODUCT' THEN strTableName := ' TNE.GPD_PRODUCT_ASSET '; strWhere := ' MODEL_NO '; ELSIF p_Object_Definition = 'CARACTERISTIC' OR p_Object_Definition = 'CHARACTERISTIC' THEN strTableName := ' TNE.GPD_CARACTERISTIC_ASSET '; strWhere := ' CARACTERISTIC_ID '; ELSIF p_Object_Definition = 'CATEGORY' THEN strTableName := ' TNE.GPD_CATEGORY_ASSET '; strWhere := ' CATEGORY_GUID '; ELSIF p_Object_Definition = 'VALUE' THEN strTableName := ' TNE.GPD_VALUE_ASSET '; strWhere := ' VALUE_ID '; ELSIF p_Object_Definition = 'PRODUCT_NODE' THEN strTableName := ' TNE.GPD_PRODUCT_NODE_ASSET '; strWhere := ' PRODUCT_NODE_GUID '; ELSIF p_Object_Definition = 'CARAC_GROUP' THEN strTableName := ' TNE.GPD_CARAC_GROUP_ASSET '; strWhere := ' CARAC_GROUP_GUID '; END IF; strQuery := ' DELETE FROM ' || strTableName || ' WHERE ASSET_GUID IN ( ''' ||
RE: Dynamic SQL: where do you draw the line?
Thomas 1. Strongly advocate bind variables as a policy. 2. When you are stuck with existing code the problem is that there isn't much ROI in going back, modifying code, retesting, etc. Then go for the code that gets executed the most. For example, a batch report that steps through a table one record at a time (a bad idea anyway) can literally choke an Oracle database. Oracle spends so much time taking each new statement, checking whether it has seen that value before, removing the oldest SQL from the buffer, storing the new SQL statement, etc. that processing can literally grind to a halt. On the plus side you end up with a good story you can tell developers when they think you are just crying wolf. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 10:05 AM To: Multiple recipients of list ORACLE-L I've been fighting an ongoing war with our ecommerce developers, who are inordinately fond of writing dynamic SQL code that neglects to incorporate bind variables. Researching AskTom I've found and utilized different techniques to force bind variables into these dynamic SQL queries, including the use of application contexts, object types, etc. However, I'm wondering if I'm making things worse, essentially providing them with band-aids, when I should be forcing them to change the way they code. Consider the sample code below (which is a relatively simple example), which is a generic DELETE statement generator. In this situation, the programmers claim the following code is good programming practice, promotes ease of maintenance, less buggy, and promotes code reusability (their definition of reusability is a bit different from mine). I disagreed with them -- not only is this code not reusable at all, with the parsing overhead consequences, it's also harder to debug and tune for performance, due to all the permutations that needs to be tested. My take was that they be far better off writing a simple static DELETE statement for each table. Their rejoinder -- it's not worth writing lots of redundant code at the expense of 'minimal' gains in performance.Now, this code *could* be rewritten to use the SYS_CONTEXT function on the p_object_id and p_site_guid to force a bind variable on those two conditions, but the IN condition with respect to the p_asset_guid would be more problematic. However, I don't feel we should have to be resorting to such measures to get this code to using bind variables. So, I'm at the point of denying such code to be migrated to production. I recognize that there are situations where there is a legitimate need for dynamic SQL, but the SQL has to be written w/o catenating literal SQL -- and if it can't -- they need to go back to square one. Opinions? I'm curious -- do you have policies/standards with respect to dynamic SQL? CREATE OR REPLACE PROCEDURE test ( p_Asset_GuidIN VARCHAR2, p_Object_Id IN VARCHAR2, p_Object_Definition IN VARCHAR2, p_Site_Guid IN VARCHAR2, p_resultIN OUT VARCHAR2 ) AS strTableName VARCHAR2(100); strWhere VARCHAR2(100); strQuery LONG; BEGIN IF p_Object_Definition = 'PRODUCT' THEN strTableName := ' TNE.GPD_PRODUCT_ASSET '; strWhere := ' MODEL_NO '; ELSIF p_Object_Definition = 'CARACTERISTIC' OR p_Object_Definition = 'CHARACTERISTIC' THEN strTableName := ' TNE.GPD_CARACTERISTIC_ASSET '; strWhere := ' CARACTERISTIC_ID '; ELSIF p_Object_Definition = 'CATEGORY' THEN strTableName := ' TNE.GPD_CATEGORY_ASSET '; strWhere := ' CATEGORY_GUID '; ELSIF p_Object_Definition = 'VALUE' THEN strTableName := ' TNE.GPD_VALUE_ASSET '; strWhere := ' VALUE_ID '; ELSIF p_Object_Definition = 'PRODUCT_NODE' THEN strTableName := ' TNE.GPD_PRODUCT_NODE_ASSET '; strWhere := ' PRODUCT_NODE_GUID '; ELSIF p_Object_Definition = 'CARAC_GROUP' THEN strTableName := ' TNE.GPD_CARAC_GROUP_ASSET '; strWhere := ' CARAC_GROUP_GUID '; END IF; strQuery := ' DELETE FROM ' || strTableName || ' WHERE ASSET_GUID IN ( ''' || REPLACE(p_Asset_Guid,' ', ''',''') || ''' ) AND '; strQuery := strQuery || strWhere || ' = ''' || p_Object_Id || ; strQuery := strQuery || ' AND SITE_GUID = ''' || p_Site_Guid || ; execute immediate strQuery; p_result := '1'; RETURN; END; Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Jeff INET: [EMAIL PROTECTED] Fat City Network
RE: Dynamic SQL: where do you draw the line?
Title: RE: Dynamic SQL: where do you draw the line? Thomas, IMHO You probably picked up a BAD example to make your point. Yes I agree with you, but here the table is unknown, which would make this a Dynamic_sql of type 4 or 3?). in this case you are constructing whole statement dynamically. Also it seems user developers are sending a string of numbers separated by space, which is replaced by semicolon to make a dynamic IN List. This might be the weekenss, Send them a URL to Asktom site and show them how to convert inlists into a table for max flexibility in the code. You could, OTOH run some quick tests on this code and another where you have one delete statement per table and use 10046 event to show them what impact it has on the database. I hate long if-then-else structures too. ps: if you want to break this code, send in p_site_guid value as '1,2' and watch the fun. In case of dynamic sql like this (and especially if it is coming from web), have your developers learn about sql-injecting techniques ... you _must_ validate input against a KNOWN set of values before using them. You _must_ also reject all unknowns with appropriate feedback. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Wednesday, June 25, 2003 10:05 AM To: Multiple recipients of list ORACLE-L I've been fighting an ongoing war with our ecommerce developers, who are inordinately fond of writing dynamic SQL code that neglects to incorporate bind variables. Researching AskTom I've found and utilized different techniques to force bind variables into these dynamic SQL queries, including the use of application contexts, object types, etc. However, I'm wondering if I'm making things worse, essentially providing them with band-aids, when I should be forcing them to change the way they code. Consider the sample code below (which is a relatively simple example), which is a generic DELETE statement generator. In this situation, the programmers claim the following code is good programming practice, promotes ease of maintenance, less buggy, and promotes code reusability (their definition of reusability is a bit different from mine). I disagreed with them -- not only is this code not reusable at all, with the parsing overhead consequences, it's also harder to debug and tune for performance, due to all the permutations that needs to be tested. My take was that they be far better off writing a simple static DELETE statement for each table. Their rejoinder -- it's not worth writing lots of redundant code at the expense of 'minimal' gains in performance. Now, this code *could* be rewritten to use the SYS_CONTEXT function on the p_object_id and p_site_guid to force a bind variable on those two conditions, but the IN condition with respect to the p_asset_guid would be more problematic. However, I don't feel we should have to be resorting to such measures to get this code to using bind variables. So, I'm at the point of denying such code to be migrated to production. I recognize that there are situations where there is a legitimate need for dynamic SQL, but the SQL has to be written w/o catenating literal SQL -- and if it can't -- they need to go back to square one. Opinions? I'm curious -- do you have policies/standards with respect to dynamic SQL? CREATE OR REPLACE PROCEDURE test ( p_Asset_Guid IN VARCHAR2, p_Object_Id IN VARCHAR2, p_Object_Definition IN VARCHAR2, p_Site_Guid IN VARCHAR2, p_result IN OUT VARCHAR2 ) AS strTableName VARCHAR2(100); strWhere VARCHAR2(100); strQuery LONG; BEGIN IF p_Object_Definition = 'PRODUCT' THEN strTableName := ' TNE.GPD_PRODUCT_ASSET '; strWhere := ' MODEL_NO '; ELSIF p_Object_Definition = 'CARACTERISTIC' OR p_Object_Definition = 'CHARACTERISTIC' THEN strTableName := ' TNE.GPD_CARACTERISTIC_ASSET '; strWhere := ' CARACTERISTIC_ID '; ELSIF p_Object_Definition = 'CATEGORY' THEN strTableName := ' TNE.GPD_CATEGORY_ASSET '; strWhere := ' CATEGORY_GUID '; ELSIF p_Object_Definition = 'VALUE' THEN strTableName := ' TNE.GPD_VALUE_ASSET '; strWhere := ' VALUE_ID '; ELSIF p_Object_Definition = 'PRODUCT_NODE' THEN strTableName := ' TNE.GPD_PRODUCT_NODE_ASSET '; strWhere := ' PRODUCT_NODE_GUID '; ELSIF p_Object_Definition = 'CARAC_GROUP' THEN strTableName := ' TNE.GPD_CARAC_GROUP_ASSET '; strWhere := ' CARAC_GROUP_GUID '; END IF; strQuery := ' DELETE FROM ' || strTableName || ' WHERE ASSET_GUID IN ( ''' || REPLACE(p_Asset_Guid,' ', ''',''') || ''' ) AND '; strQuery := strQuery || strWhere || ' = ''' || p_Object_Id || ; strQuery := strQuery || ' AND SITE_GUID = ''' || p_Site_Guid || ; execute immediate strQuery; p_result := '1'; RETURN; END
RE: Dynamic SQL: where do you draw the line?
I'm not sure why it's that much harder to use the USING clause of execute immediate and use bind variables. Seems much easier to read, first of all, and the impact it might have on the library cache would be most positive. Sure, we are talking perhaps the difference between .02 and 1 seconds, but that can add up over multipule executions. See if you can determine the number of executions for this code, and the average parse time. Then rewrite it to use bind variables and run some tests to get a good average parse time for that bit of code, multipuly by executions and you have quantified the overall performance impact on that bit of code. Of course, that dosen't begin to quantify the impact of reducing the hash chaining that is probably happening in your shared pool that would occur as a result of using bind variables. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/25/2003 10:04 AM I've been fighting an ongoing war with our ecommerce developers, who are inordinately fond of writing dynamic SQL code that neglects to incorporate bind variables. Researching AskTom I've found and utilized different techniques to force bind variables into these dynamic SQL queries, including the use of application contexts, object types, etc. However, I'm wondering if I'm making things worse, essentially providing them with band-aids, when I should be forcing them to change the way they code. Consider the sample code below (which is a relatively simple example), which is a generic DELETE statement generator. In this situation, the programmers claim the following code is good programming practice, promotes ease of maintenance, less buggy, and promotes code reusability (their definition of reusability is a bit different from mine). I disagreed with them -- not only is this code not reusable at all, with the parsing overhead consequences, it's also harder to debug and tune for performance, due to all the permutations that needs to be tested. My take was that they be far better off writing a simple static DELETE statement for each table. Their rejoinder -- it's not worth writing lots of redundant code at the expense of 'minimal' gains in performance.Now, this code *could* be rewritten to use the SYS_CONTEXT function on the p_object_id and p_site_guid to force a bind variable on those two conditions, but the IN condition with respect to the p_asset_guid would be more problematic. However, I don't feel we should have to be resorting to such measures to get this code to using bind variables. So, I'm at the point of denying such code to be migrated to production. I recognize that there are situations where there is a legitimate need for dynamic SQL, but the SQL has to be written w/o catenating literal SQL -- and if it can't -- they need to go back to square one. Opinions? I'm curious -- do you have policies/standards with respect to dynamic SQL? CREATE OR REPLACE PROCEDURE test ( p_Asset_GuidIN VARCHAR2, p_Object_Id IN VARCHAR2, p_Object_Definition IN VARCHAR2, p_Site_Guid IN VARCHAR2, p_resultIN OUT VARCHAR2 ) AS strTableName VARCHAR2(100); strWhere VARCHAR2(100); strQuery LONG; BEGIN IF p_Object_Definition = 'PRODUCT' THEN strTableName := ' TNE.GPD_PRODUCT_ASSET '; strWhere := ' MODEL_NO '; ELSIF p_Object_Definition = 'CARACTERISTIC' OR p_Object_Definition = 'CHARACTERISTIC' THEN strTableName := ' TNE.GPD_CARACTERISTIC_ASSET '; strWhere := ' CARACTERISTIC_ID '; ELSIF p_Object_Definition = 'CATEGORY' THEN strTableName := ' TNE.GPD_CATEGORY_ASSET '; strWhere := ' CATEGORY_GUID '; ELSIF p_Object_Definition = 'VALUE' THEN strTableName := ' TNE.GPD_VALUE_ASSET '; strWhere := ' VALUE_ID '; ELSIF p_Object_Definition = 'PRODUCT_NODE' THEN strTableName := ' TNE.GPD_PRODUCT_NODE_ASSET '; strWhere := ' PRODUCT_NODE_GUID '; ELSIF p_Object_Definition = 'CARAC_GROUP' THEN strTableName := ' TNE.GPD_CARAC_GROUP_ASSET '; strWhere := ' CARAC_GROUP_GUID '; END IF; strQuery := ' DELETE FROM ' || strTableName || ' WHERE ASSET_GUID IN ( ''' || REPLACE(p_Asset_Guid,' ', ''',''') || ''' ) AND '; strQuery := strQuery || strWhere || ' = ''' || p_Object_Id || ; strQuery := strQuery || ' AND SITE_GUID = ''' || p_Site_Guid || ; execute immediate strQuery; p_result := '1'; RETURN; END; Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Jeff INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com
Re: Dynamic SQL: where do you draw the line?
Jeff, The first thing to do is teach them to spell 'CHARACTERISTIC'. Secondly, their definition of 'reusable' and yours mean entirely different things. Their idea of reusability is code that can be reused elsewhere, though based on the example, I would say they're not quite there yet. ;) Your understanding of 'reusable' is database centric, which they have not considered, and apparently don't care about. Thirdly, why can't they use bind variables in their procedure? They do work with execute immediate ... using ... Bindvars won't work for the table_names, but it will certainly work for the the values in the WHERE clause. Jared Thomas Jeff [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 06/25/2003 08:04 AM Please respond to ORACLE-L To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Dynamic SQL: where do you draw the line? I've been fighting an ongoing war with our ecommerce developers, who are inordinately fond of writing dynamic SQL code that neglects to incorporate bind variables. Researching AskTom I've found and utilized different techniques to force bind variables into these dynamic SQL queries, including the use of application contexts, object types, etc. However, I'm wondering if I'm making things worse, essentially providing them with band-aids, when I should be forcing them to change the way they code. Consider the sample code below (which is a relatively simple example), which is a generic DELETE statement generator. In this situation, the programmers claim the following code is good programming practice, promotes ease of maintenance, less buggy, and promotes code reusability (their definition of reusability is a bit different from mine). I disagreed with them -- not only is this code not reusable at all, with the parsing overhead consequences, it's also harder to debug and tune for performance, due to all the permutations that needs to be tested. My take was that they be far better off writing a simple static DELETE statement for each table. Their rejoinder -- it's not worth writing lots of redundant code at the expense of 'minimal' gains in performance.Now, this code *could* be rewritten to use the SYS_CONTEXT function on the p_object_id and p_site_guid to force a bind variable on those two conditions, but the IN condition with respect to the p_asset_guid would be more problematic. However, I don't feel we should have to be resorting to such measures to get this code to using bind variables. So, I'm at the point of denying such code to be migrated to production. I recognize that there are situations where there is a legitimate need for dynamic SQL, but the SQL has to be written w/o catenating literal SQL -- and if it can't -- they need to go back to square one. Opinions? I'm curious -- do you have policies/standards with respect to dynamic SQL? CREATE OR REPLACE PROCEDURE test ( p_Asset_GuidIN VARCHAR2, p_Object_Id IN VARCHAR2, p_Object_Definition IN VARCHAR2, p_Site_Guid IN VARCHAR2, p_resultIN OUT VARCHAR2 ) AS strTableName VARCHAR2(100); strWhere VARCHAR2(100); strQuery LONG; BEGIN IF p_Object_Definition = 'PRODUCT' THEN strTableName := ' TNE.GPD_PRODUCT_ASSET '; strWhere := ' MODEL_NO '; ELSIF p_Object_Definition = 'CARACTERISTIC' OR p_Object_Definition = 'CHARACTERISTIC' THEN strTableName := ' TNE.GPD_CARACTERISTIC_ASSET '; strWhere := ' CARACTERISTIC_ID '; ELSIF p_Object_Definition = 'CATEGORY' THEN strTableName := ' TNE.GPD_CATEGORY_ASSET '; strWhere := ' CATEGORY_GUID '; ELSIF p_Object_Definition = 'VALUE' THEN strTableName := ' TNE.GPD_VALUE_ASSET '; strWhere := ' VALUE_ID '; ELSIF p_Object_Definition = 'PRODUCT_NODE' THEN strTableName := ' TNE.GPD_PRODUCT_NODE_ASSET '; strWhere := ' PRODUCT_NODE_GUID '; ELSIF p_Object_Definition = 'CARAC_GROUP' THEN strTableName := ' TNE.GPD_CARAC_GROUP_ASSET '; strWhere := ' CARAC_GROUP_GUID '; END IF; strQuery := ' DELETE FROM ' || strTableName || ' WHERE ASSET_GUID IN ( ''' || REPLACE(p_Asset_Guid,' ', ''',''') || ''' ) AND '; strQuery := strQuery || strWhere || ' = ''' || p_Object_Id || ; strQuery := strQuery || ' AND SITE_GUID = ''' || p_Site_Guid || ; execute immediate strQuery; p_result := '1'; RETURN; END; Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL PROTECTED] Indy DBA Master Documentation available at: http://gkmqp.tce.com/tis_dba -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Jeff INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California
RE: Dynamic SQL: where do you draw the line?
USING clause could be used for column values in dynamic sql, but for table names and column names you'd still have to concatenate strings. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Freeman Robert - IL Sent: Wednesday, June 25, 2003 12:40 PM To: Multiple recipients of list ORACLE-L I'm not sure why it's that much harder to use the USING clause of execute immediate and use bind variables. Seems much easier to read, first of all, and the impact it might have on the library cache would be most positive. Sure, we are talking perhaps the difference between .02 and 1 seconds, but that can add up over multipule executions. See if you can determine the number of executions for this code, and the average parse time. Then rewrite it to use bind variables and run some tests to get a good average parse time for that bit of code, multipuly by executions and you have quantified the overall performance impact on that bit of code. Of course, that dosen't begin to quantify the impact of reducing the hash chaining that is probably happening in your shared pool that would occur as a result of using bind variables. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/25/2003 10:04 AM I've been fighting an ongoing war with our ecommerce developers, who are inordinately fond of writing dynamic SQL code that neglects to incorporate bind variables. Researching AskTom I've found and utilized different techniques to force bind variables into these dynamic SQL queries, including the use of application contexts, object types, etc. However, I'm wondering if I'm making things worse, essentially providing them with band-aids, when I should be forcing them to change the way they code. Consider the sample code below (which is a relatively simple example), which is a generic DELETE statement generator. In this situation, the programmers claim the following code is good programming practice, promotes ease of maintenance, less buggy, and promotes code reusability (their definition of reusability is a bit different from mine). I disagreed with them -- not only is this code not reusable at all, with the parsing overhead consequences, it's also harder to debug and tune for performance, due to all the permutations that needs to be tested. My take was that they be far better off writing a simple static DELETE statement for each table. Their rejoinder -- it's not worth writing lots of redundant code at the expense of 'minimal' gains in performance.Now, this code *could* be rewritten to use the SYS_CONTEXT function on the p_object_id and p_site_guid to force a bind variable on those two conditions, but the IN condition with respect to the p_asset_guid would be more problematic. However, I don't feel we should have to be resorting to such measures to get this code to using bind variables. So, I'm at the point of denying such code to be migrated to production. I recognize that there are situations where there is a legitimate need for dynamic SQL, but the SQL has to be written w/o catenating literal SQL -- and if it can't -- they need to go back to square one. Opinions? I'm curious -- do you have policies/standards with respect to dynamic SQL? CREATE OR REPLACE PROCEDURE test ( p_Asset_GuidIN VARCHAR2, p_Object_Id IN VARCHAR2, p_Object_Definition IN VARCHAR2, p_Site_Guid IN VARCHAR2, p_resultIN OUT VARCHAR2 ) AS strTableName VARCHAR2(100); strWhere VARCHAR2(100); strQuery LONG; BEGIN IF p_Object_Definition = 'PRODUCT' THEN strTableName := ' TNE.GPD_PRODUCT_ASSET '; strWhere := ' MODEL_NO '; ELSIF p_Object_Definition = 'CARACTERISTIC' OR p_Object_Definition = 'CHARACTERISTIC' THEN strTableName := ' TNE.GPD_CARACTERISTIC_ASSET '; strWhere := ' CARACTERISTIC_ID '; ELSIF p_Object_Definition = 'CATEGORY' THEN strTableName := ' TNE.GPD_CATEGORY_ASSET '; strWhere := ' CATEGORY_GUID '; ELSIF p_Object_Definition = 'VALUE' THEN strTableName := ' TNE.GPD_VALUE_ASSET '; strWhere := ' VALUE_ID '; ELSIF p_Object_Definition = 'PRODUCT_NODE' THEN strTableName := ' TNE.GPD_PRODUCT_NODE_ASSET '; strWhere := ' PRODUCT_NODE_GUID '; ELSIF p_Object_Definition = 'CARAC_GROUP' THEN strTableName := ' TNE.GPD_CARAC_GROUP_ASSET '; strWhere := ' CARAC_GROUP_GUID '; END IF; strQuery := ' DELETE FROM ' || strTableName || ' WHERE ASSET_GUID IN ( ''' || REPLACE(p_Asset_Guid,' ', ''',''') || ''' ) AND '; strQuery := strQuery || strWhere || ' = ''' || p_Object_Id || ; strQuery := strQuery || ' AND SITE_GUID = ''' || p_Site_Guid || ; execute immediate strQuery; p_result := '1'; RETURN; END; Jeffery D Thomas DBA Thomson Information Services Thomson, Inc. Email: [EMAIL
RE: Dynamic SQL: where do you draw the line?
Title: RE: Dynamic SQL: where do you draw the line? Raj, I'm aware of the table technique for converting inlists and employed that a few weeks ago in a different stored procedure.I was thinking if we could make the DELETE static, then itcould soemthing look like this: DELETE FROMTABLE WHEREASSET_GUID IN(SELECT *FROM THE (SELECT CAST( str2tab(p_str) as STRTABTYPE ) FROM DUAL)) AND SITE_GUIDE = p_site_guid AND OBJECT_GUID = p_object_guid; And I would have them code 7 small procedures each with the similar DELETE syntax. This way, with static SQL, you eliminate both the hard and soft parsing.With EXEC IMMEDIATE, you will always at least get the soft parsing. That's my understanding. -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Wednesday, June 25, 2003 12:35 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Dynamic SQL: where do you draw the line? Thomas, IMHO You probably picked up a BAD example to make your point. Yes I agree with you, but here the table is unknown, which would make this a Dynamic_sql of type 4 or 3?). in this case you are constructing whole statement dynamically. Also it seems user developers are sending a string of numbers separated by space, which is replaced by semicolon to make a dynamic IN List. This might be the weekenss, Send them a URL to Asktom site and show them how to convert inlists into a table for max flexibility in the code. You could, OTOH run some quick tests on this code and another where you have one delete statement per table and use 10046 event to show them what impact it has on the database. I hate long if-then-else structures too. ps: if you want to break this code, send in p_site_guid value as '1,2' and watch the fun. In case of dynamic sql like this (and especially if it is coming from web), have your developers learn about sql-injecting techniques ... you _must_ validate input against a KNOWN set of values before using them. You _must_ also reject all unknowns with appropriate feedback. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Wednesday, June 25, 2003 10:05 AM To: Multiple recipients of list ORACLE-L I've been fighting an ongoing war with our ecommerce developers, who are inordinately fond of writing dynamic SQL code that neglects to incorporate bind variables. Researching AskTom I've found and utilized different techniques to force bind variables into these dynamic SQL queries, including the use of application contexts, object types, etc. However, I'm wondering if I'm making things worse, essentially providing them with band-aids, when I should be forcing them to change the way they code. Consider the sample code below (which is a relatively simple example), which is a generic DELETE statement generator. In this situation, the programmers claim the following code is good programming practice, promotes ease of maintenance, less buggy, and promotes code reusability (their definition of reusability is a bit different from mine). I disagreed with them -- not only is this code not reusable at all, with the parsing overhead consequences, it's also harder to debug and tune for performance, due to all the permutations that needs to be tested. My take was that they be far better off writing a simple static DELETE statement for each table. Their rejoinder -- it's not worth writing lots of redundant code at the expense of 'minimal' gains in performance. Now, this code *could* be rewritten to use the SYS_CONTEXT function on the p_object_id and p_site_guid to force a bind variable on those two conditions, but the IN condition with respect to the p_asset_guid would be more problematic. However, I don't feel we should have to be resorting to such measures to get this code to using bind variables. So, I'm at the point of denying such code to be migrated to production. I recognize that there are situations where there is a legitimate need for dynamic SQL, but the SQL has to be written w/o catenating literal SQL -- and if it can't -- they need to go back to square one. Opinions? I'm curious -- do you have policies/standards with respect to dynamic SQL? CREATE OR REPLACE PROCEDURE test ( p_Asset_Guid IN VARCHAR2, p_Object_Id IN VARCHAR2, p_Object_Definition IN VARCHAR2, p_Site_Guid IN VARCHAR2, p_result IN OUT VARCHAR2 ) AS strTableName VARCHAR2(100); strWhere VARCHAR2(100); strQuery LONG; BEGIN IF p_Object_Definition = 'PRODUCT' THEN strTableName := ' TNE.GPD_PRODUCT_ASSET '; strWhere := ' MODEL_NO '; ELSIF p_Object_Definition = 'CARACTERISTIC' OR p_Object_Definition = 'CHARACTERISTIC' THEN strTableName := ' TNE.GPD_CARACTERISTIC_ASSET '; strWhere := ' CARACTERISTIC_ID '; ELSIF
RE: Dynamic SQL: where do you draw the line?
I was merely pointing to limitations of USING clause. My message had nothing to do with building reusable code. Though, I'd love if dynamic sql would allow USING clause to be used for tables/columns names. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 1:44 PM To: 'Igor Neyman '; 'Multiple recipients of list ORACLE-L ' Really... wow, and how do you propose to build reusable code given those conditions? RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/25/2003 2:19 PM USING clause could be used for column values in dynamic sql, but for table names and column names you'd still have to concatenate strings. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Freeman Robert - IL Sent: Wednesday, June 25, 2003 12:40 PM To: Multiple recipients of list ORACLE-L I'm not sure why it's that much harder to use the USING clause of execute immediate and use bind variables. Seems much easier to read, first of all, and the impact it might have on the library cache would be most positive. Sure, we are talking perhaps the difference between .02 and 1 seconds, but that can add up over multipule executions. See if you can determine the number of executions for this code, and the average parse time. Then rewrite it to use bind variables and run some tests to get a good average parse time for that bit of code, multipuly by executions and you have quantified the overall performance impact on that bit of code. Of course, that dosen't begin to quantify the impact of reducing the hash chaining that is probably happening in your shared pool that would occur as a result of using bind variables. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/25/2003 10:04 AM I've been fighting an ongoing war with our ecommerce developers, who are inordinately fond of writing dynamic SQL code that neglects to incorporate bind variables. Researching AskTom I've found and utilized different techniques to force bind variables into these dynamic SQL queries, including the use of application contexts, object types, etc. However, I'm wondering if I'm making things worse, essentially providing them with band-aids, when I should be forcing them to change the way they code. Consider the sample code below (which is a relatively simple example), which is a generic DELETE statement generator. In this situation, the programmers claim the following code is good programming practice, promotes ease of maintenance, less buggy, and promotes code reusability (their definition of reusability is a bit different from mine). I disagreed with them -- not only is this code not reusable at all, with the parsing overhead consequences, it's also harder to debug and tune for performance, due to all the permutations that needs to be tested. My take was that they be far better off writing a simple static DELETE statement for each table. Their rejoinder -- it's not worth writing lots of redundant code at the expense of 'minimal' gains in performance.Now, this code *could* be rewritten to use the SYS_CONTEXT function on the p_object_id and p_site_guid to force a bind variable on those two conditions, but the IN condition with respect to the p_asset_guid would be more problematic. However, I don't feel we should have to be resorting to such measures to get this code to using bind variables. So, I'm at the point of denying such code to be migrated to production. I recognize that there are situations where there is a legitimate need for dynamic SQL, but the SQL has to be written w/o catenating literal SQL -- and if it can't -- they need to go back to square one. Opinions? I'm curious -- do you have policies/standards with respect to dynamic SQL? CREATE OR REPLACE PROCEDURE test ( p_Asset_GuidIN VARCHAR2, p_Object_Id IN VARCHAR2, p_Object_Definition IN VARCHAR2, p_Site_Guid IN VARCHAR2, p_resultIN OUT VARCHAR2 ) AS strTableName VARCHAR2(100); strWhere VARCHAR2(100); strQuery LONG; BEGIN IF p_Object_Definition = 'PRODUCT' THEN strTableName := ' TNE.GPD_PRODUCT_ASSET '; strWhere := ' MODEL_NO '; ELSIF p_Object_Definition = 'CARACTERISTIC' OR p_Object_Definition = 'CHARACTERISTIC' THEN strTableName := ' TNE.GPD_CARACTERISTIC_ASSET '; strWhere := ' CARACTERISTIC_ID '; ELSIF p_Object_Definition = 'CATEGORY' THEN strTableName := ' TNE.GPD_CATEGORY_ASSET '; strWhere := ' CATEGORY_GUID '; ELSIF p_Object_Definition = 'VALUE' THEN strTableName := ' TNE.GPD_VALUE_ASSET '; strWhere := ' VALUE_ID '; ELSIF p_Object_Definition = 'PRODUCT_NODE' THEN strTableName := ' TNE.GPD_PRODUCT_NODE_ASSET '; strWhere := ' PRODUCT_NODE_GUID '; ELSIF p_Object_Definition = 'CARAC_GROUP' THEN strTableName := ' TNE.GPD_CARAC_GROUP_ASSET ';
RE: Dynamic SQL: where do you draw the line?
Really... wow, and how do you propose to build reusable code given those conditions? RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/25/2003 2:19 PM USING clause could be used for column values in dynamic sql, but for table names and column names you'd still have to concatenate strings. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Freeman Robert - IL Sent: Wednesday, June 25, 2003 12:40 PM To: Multiple recipients of list ORACLE-L I'm not sure why it's that much harder to use the USING clause of execute immediate and use bind variables. Seems much easier to read, first of all, and the impact it might have on the library cache would be most positive. Sure, we are talking perhaps the difference between .02 and 1 seconds, but that can add up over multipule executions. See if you can determine the number of executions for this code, and the average parse time. Then rewrite it to use bind variables and run some tests to get a good average parse time for that bit of code, multipuly by executions and you have quantified the overall performance impact on that bit of code. Of course, that dosen't begin to quantify the impact of reducing the hash chaining that is probably happening in your shared pool that would occur as a result of using bind variables. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/25/2003 10:04 AM I've been fighting an ongoing war with our ecommerce developers, who are inordinately fond of writing dynamic SQL code that neglects to incorporate bind variables. Researching AskTom I've found and utilized different techniques to force bind variables into these dynamic SQL queries, including the use of application contexts, object types, etc. However, I'm wondering if I'm making things worse, essentially providing them with band-aids, when I should be forcing them to change the way they code. Consider the sample code below (which is a relatively simple example), which is a generic DELETE statement generator. In this situation, the programmers claim the following code is good programming practice, promotes ease of maintenance, less buggy, and promotes code reusability (their definition of reusability is a bit different from mine). I disagreed with them -- not only is this code not reusable at all, with the parsing overhead consequences, it's also harder to debug and tune for performance, due to all the permutations that needs to be tested. My take was that they be far better off writing a simple static DELETE statement for each table. Their rejoinder -- it's not worth writing lots of redundant code at the expense of 'minimal' gains in performance.Now, this code *could* be rewritten to use the SYS_CONTEXT function on the p_object_id and p_site_guid to force a bind variable on those two conditions, but the IN condition with respect to the p_asset_guid would be more problematic. However, I don't feel we should have to be resorting to such measures to get this code to using bind variables. So, I'm at the point of denying such code to be migrated to production. I recognize that there are situations where there is a legitimate need for dynamic SQL, but the SQL has to be written w/o catenating literal SQL -- and if it can't -- they need to go back to square one. Opinions? I'm curious -- do you have policies/standards with respect to dynamic SQL? CREATE OR REPLACE PROCEDURE test ( p_Asset_GuidIN VARCHAR2, p_Object_Id IN VARCHAR2, p_Object_Definition IN VARCHAR2, p_Site_Guid IN VARCHAR2, p_resultIN OUT VARCHAR2 ) AS strTableName VARCHAR2(100); strWhere VARCHAR2(100); strQuery LONG; BEGIN IF p_Object_Definition = 'PRODUCT' THEN strTableName := ' TNE.GPD_PRODUCT_ASSET '; strWhere := ' MODEL_NO '; ELSIF p_Object_Definition = 'CARACTERISTIC' OR p_Object_Definition = 'CHARACTERISTIC' THEN strTableName := ' TNE.GPD_CARACTERISTIC_ASSET '; strWhere := ' CARACTERISTIC_ID '; ELSIF p_Object_Definition = 'CATEGORY' THEN strTableName := ' TNE.GPD_CATEGORY_ASSET '; strWhere := ' CATEGORY_GUID '; ELSIF p_Object_Definition = 'VALUE' THEN strTableName := ' TNE.GPD_VALUE_ASSET '; strWhere := ' VALUE_ID '; ELSIF p_Object_Definition = 'PRODUCT_NODE' THEN strTableName := ' TNE.GPD_PRODUCT_NODE_ASSET '; strWhere := ' PRODUCT_NODE_GUID '; ELSIF p_Object_Definition = 'CARAC_GROUP' THEN strTableName := ' TNE.GPD_CARAC_GROUP_ASSET '; strWhere := ' CARAC_GROUP_GUID '; END IF; strQuery := ' DELETE FROM ' || strTableName || ' WHERE ASSET_GUID IN ( ''' || REPLACE(p_Asset_Guid,' ', ''',''') || ''' ) AND '; strQuery := strQuery || strWhere || ' = ''' || p_Object_Id || ; strQuery := strQuery || ' AND SITE_GUID = ''' || p_Site_Guid || ; execute
RE: Dynamic SQL: where do you draw the line?
Title: RE: Dynamic SQL: where do you draw the line? Thomas, I'd prefer your approach than a spaghetti code of if-then-else, lucky you they haven't discovered GOTO yet ... g Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message-From: Thomas Jeff [mailto:[EMAIL PROTECTED]Sent: Wednesday, June 25, 2003 3:25 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Dynamic SQL: where do you draw the line? Raj, I'm aware of the table technique for converting inlists and employed that a few weeks ago in a different stored procedure.I was thinking if we could make the DELETE static, then itcould soemthing look like this: DELETE FROMTABLE WHEREASSET_GUID IN(SELECT *FROM THE (SELECT CAST( str2tab(p_str) as STRTABTYPE ) FROM DUAL)) AND SITE_GUIDE = p_site_guid AND OBJECT_GUID = p_object_guid; And I would have them code 7 small procedures each with the similar DELETE syntax. This way, with static SQL, you eliminate both the hard and soft parsing.With EXEC IMMEDIATE, you will always at least get the soft parsing. That's my understanding. -Original Message-From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]Sent: Wednesday, June 25, 2003 12:35 PMTo: Multiple recipients of list ORACLE-LSubject: RE: Dynamic SQL: where do you draw the line? Thomas, IMHO You probably picked up a BAD example to make your point. Yes I agree with you, but here the table is unknown, which would make this a Dynamic_sql of type 4 or 3?). in this case you are constructing whole statement dynamically. Also it seems user developers are sending a string of numbers separated by space, which is replaced by semicolon to make a dynamic IN List. This might be the weekenss, Send them a URL to Asktom site and show them how to convert inlists into a table for max flexibility in the code. You could, OTOH run some quick tests on this code and another where you have one delete statement per table and use 10046 event to show them what impact it has on the database. I hate long if-then-else structures too. ps: if you want to break this code, send in p_site_guid value as '1,2' and watch the fun. In case of dynamic sql like this (and especially if it is coming from web), have your developers learn about sql-injecting techniques ... you _must_ validate input against a KNOWN set of values before using them. You _must_ also reject all unknowns with appropriate feedback. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Wednesday, June 25, 2003 10:05 AM To: Multiple recipients of list ORACLE-L I've been fighting an ongoing war with our ecommerce developers, who are inordinately fond of writing dynamic SQL code that neglects to incorporate bind variables. Researching AskTom I've found and utilized different techniques to force bind variables into these dynamic SQL queries, including the use of application contexts, object types, etc. However, I'm wondering if I'm making things worse, essentially providing them with band-aids, when I should be forcing them to change the way they code. Consider the sample code below (which is a relatively simple example), which is a generic DELETE statement generator. In this situation, the programmers claim the following code is good programming practice, promotes ease of maintenance, less buggy, and promotes code reusability (their definition of reusability is a bit different from mine). I disagreed with them -- not only is this code not reusable at all, with the parsing overhead consequences, it's also harder to debug and tune for performance, due to all the permutations that needs to be tested. My take was that they be far better off writing a simple static DELETE statement for each table. Their rejoinder -- it's not worth writing lots of redundant code at the expense of 'minimal' gains in performance. Now, this code *could* be rewritten to use the SYS_CONTEXT function on the p_object_id and p_site_guid to force a bind variable on those two conditions, but the IN condition with respect to the p_asset_guid would be more problematic. However, I don't feel we should have to be resorting to such measures to get this code to using bind variables. So, I'm at the point of denying such code to be migrated to production. I recognize that there are situations where there is a legitimate need for dynamic SQL
RE: Dynamic SQL: where do you draw the line?
Understand. Would be cool, wouldn't it! RF -Original Message- To: Freeman Robert - IL; 'Multiple recipients of list ORACLE-L ' Sent: 6/25/2003 1:53 PM I was merely pointing to limitations of USING clause. My message had nothing to do with building reusable code. Though, I'd love if dynamic sql would allow USING clause to be used for tables/columns names. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Sent: Wednesday, June 25, 2003 1:44 PM To: 'Igor Neyman '; 'Multiple recipients of list ORACLE-L ' Really... wow, and how do you propose to build reusable code given those conditions? RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/25/2003 2:19 PM USING clause could be used for column values in dynamic sql, but for table names and column names you'd still have to concatenate strings. Igor Neyman, OCP DBA [EMAIL PROTECTED] -Original Message- Freeman Robert - IL Sent: Wednesday, June 25, 2003 12:40 PM To: Multiple recipients of list ORACLE-L I'm not sure why it's that much harder to use the USING clause of execute immediate and use bind variables. Seems much easier to read, first of all, and the impact it might have on the library cache would be most positive. Sure, we are talking perhaps the difference between .02 and 1 seconds, but that can add up over multipule executions. See if you can determine the number of executions for this code, and the average parse time. Then rewrite it to use bind variables and run some tests to get a good average parse time for that bit of code, multipuly by executions and you have quantified the overall performance impact on that bit of code. Of course, that dosen't begin to quantify the impact of reducing the hash chaining that is probably happening in your shared pool that would occur as a result of using bind variables. RF -Original Message- To: Multiple recipients of list ORACLE-L Sent: 6/25/2003 10:04 AM I've been fighting an ongoing war with our ecommerce developers, who are inordinately fond of writing dynamic SQL code that neglects to incorporate bind variables. Researching AskTom I've found and utilized different techniques to force bind variables into these dynamic SQL queries, including the use of application contexts, object types, etc. However, I'm wondering if I'm making things worse, essentially providing them with band-aids, when I should be forcing them to change the way they code. Consider the sample code below (which is a relatively simple example), which is a generic DELETE statement generator. In this situation, the programmers claim the following code is good programming practice, promotes ease of maintenance, less buggy, and promotes code reusability (their definition of reusability is a bit different from mine). I disagreed with them -- not only is this code not reusable at all, with the parsing overhead consequences, it's also harder to debug and tune for performance, due to all the permutations that needs to be tested. My take was that they be far better off writing a simple static DELETE statement for each table. Their rejoinder -- it's not worth writing lots of redundant code at the expense of 'minimal' gains in performance.Now, this code *could* be rewritten to use the SYS_CONTEXT function on the p_object_id and p_site_guid to force a bind variable on those two conditions, but the IN condition with respect to the p_asset_guid would be more problematic. However, I don't feel we should have to be resorting to such measures to get this code to using bind variables. So, I'm at the point of denying such code to be migrated to production. I recognize that there are situations where there is a legitimate need for dynamic SQL, but the SQL has to be written w/o catenating literal SQL -- and if it can't -- they need to go back to square one. Opinions? I'm curious -- do you have policies/standards with respect to dynamic SQL? CREATE OR REPLACE PROCEDURE test ( p_Asset_GuidIN VARCHAR2, p_Object_Id IN VARCHAR2, p_Object_Definition IN VARCHAR2, p_Site_Guid IN VARCHAR2, p_resultIN OUT VARCHAR2 ) AS strTableName VARCHAR2(100); strWhere VARCHAR2(100); strQuery LONG; BEGIN IF p_Object_Definition = 'PRODUCT' THEN strTableName := ' TNE.GPD_PRODUCT_ASSET '; strWhere := ' MODEL_NO '; ELSIF p_Object_Definition = 'CARACTERISTIC' OR p_Object_Definition = 'CHARACTERISTIC' THEN strTableName := ' TNE.GPD_CARACTERISTIC_ASSET '; strWhere := ' CARACTERISTIC_ID '; ELSIF p_Object_Definition = 'CATEGORY' THEN strTableName := ' TNE.GPD_CATEGORY_ASSET '; strWhere := ' CATEGORY_GUID '; ELSIF p_Object_Definition = 'VALUE' THEN strTableName := ' TNE.GPD_VALUE_ASSET '; strWhere := ' VALUE_ID '; ELSIF p_Object_Definition = 'PRODUCT_NODE' THEN strTableName := '
Re: Dynamic SQL: where do you draw the line?
Thomas In addition to the responses posted by others, regarding the thrashing of shared pool with zillions of once-used code, there is another serious security hole - SQL Injection. You mentioned this is ging to be your eCommerce application database - and will probably attract the hackers like bees to honey. In your example, if the parameter p_site_guide is passed a value X' or 'X'='X Note there is no single quote before or after the value. The dynamic sql will, in addition to all the other parameters, accept this as a valid where clause and rewrite the query as DELETE FROM some_table WHERE ASSET_GUID IN ( '...') AND OBJECT_ID IN (...) AND SITE_GUID = 'X' OR 'X' = 'X'; what do you think the result is going to be? Your developers are not going to stop at delete only - they will explore this to SELECTs as well. The seriousness of the problem grows - the injectied code could have X' UNION ALL SELECT CUST_NAME, CREDIT_CARD_NUM, EXP_DATE... Now in addition to the good old data, the hacker also got some more (and unauthorized) valuable knowledge. Try to show them the examples and prove how dangerous the practice of dynamic sql is. HTH. Arup Nanda - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, June 25, 2003 11:04 AM I've been fighting an ongoing war with our ecommerce developers, who are inordinately fond of writing dynamic SQL code that neglects to incorporate bind variables. Researching AskTom I've found and utilized different techniques to force bind variables into these dynamic SQL queries, including the use of application contexts, object types, etc. However, I'm wondering if I'm making things worse, essentially providing them with band-aids, when I should be forcing them to change the way they code. Consider the sample code below (which is a relatively simple example), which is a generic DELETE statement generator. In this situation, the programmers claim the following code is good programming practice, promotes ease of maintenance, less buggy, and promotes code reusability (their definition of reusability is a bit different from mine). I disagreed with them -- not only is this code not reusable at all, with the parsing overhead consequences, it's also harder to debug and tune for performance, due to all the permutations that needs to be tested. My take was that they be far better off writing a simple static DELETE statement for each table. Their rejoinder -- it's not worth writing lots of redundant code at the expense of 'minimal' gains in performance.Now, this code *could* be rewritten to use the SYS_CONTEXT function on the p_object_id and p_site_guid to force a bind variable on those two conditions, but the IN condition with respect to the p_asset_guid would be more problematic. However, I don't feel we should have to be resorting to such measures to get this code to using bind variables. So, I'm at the point of denying such code to be migrated to production. I recognize that there are situations where there is a legitimate need for dynamic SQL, but the SQL has to be written w/o catenating literal SQL -- and if it can't -- they need to go back to square one. Opinions? I'm curious -- do you have policies/standards with respect to dynamic SQL? CREATE OR REPLACE PROCEDURE test ( p_Asset_GuidIN VARCHAR2, p_Object_Id IN VARCHAR2, p_Object_Definition IN VARCHAR2, p_Site_Guid IN VARCHAR2, p_resultIN OUT VARCHAR2 ) AS strTableName VARCHAR2(100); strWhere VARCHAR2(100); strQuery LONG; BEGIN IF p_Object_Definition = 'PRODUCT' THEN strTableName := ' TNE.GPD_PRODUCT_ASSET '; strWhere := ' MODEL_NO '; ELSIF p_Object_Definition = 'CARACTERISTIC' OR p_Object_Definition = 'CHARACTERISTIC' THEN strTableName := ' TNE.GPD_CARACTERISTIC_ASSET '; strWhere := ' CARACTERISTIC_ID '; ELSIF p_Object_Definition = 'CATEGORY' THEN strTableName := ' TNE.GPD_CATEGORY_ASSET '; strWhere := ' CATEGORY_GUID '; ELSIF p_Object_Definition = 'VALUE' THEN strTableName := ' TNE.GPD_VALUE_ASSET '; strWhere := ' VALUE_ID '; ELSIF p_Object_Definition = 'PRODUCT_NODE' THEN strTableName := ' TNE.GPD_PRODUCT_NODE_ASSET '; strWhere := ' PRODUCT_NODE_GUID '; ELSIF p_Object_Definition = 'CARAC_GROUP' THEN strTableName := ' TNE.GPD_CARAC_GROUP_ASSET '; strWhere := ' CARAC_GROUP_GUID '; END IF; strQuery := ' DELETE FROM ' || strTableName || ' WHERE ASSET_GUID IN ( ''' || REPLACE(p_Asset_Guid,' ', ''',''') || ''' ) AND '; strQuery := strQuery || strWhere || ' = ''' || p_Object_Id || ; strQuery := strQuery || ' AND SITE_GUID = ''' || p_Site_Guid || ; execute immediate strQuery; p_result := '1'; RETURN; END;
RE: dynamic SQL - preformance?
RE: Dynamic SQL
JP, No, but you can make multiple 4000 char strings and concat it all together: sel1 varchar2(4000); sel2 varchar2(4000); where_clause varchar2(4000); order_by_clause varchar2(4000); begin open my_cursor for sel1 || sel2 || where_clause || order_by_clause; I do it all the time! Hope this helps. Tom Mercadante Oracle Certified Professional -Original Message- Sent: Tuesday, March 26, 2002 1:44 PM To: Multiple recipients of list ORACLE-L Hello I'm using variable sqlstr VARCHAR2(4000) in procedure to create SELECT string. But the length of my SELECT * FROM is bigger than 4000 chars. Is there a way how to call OPEN my_cursor FOR sqlstr; for sqlstr longer than 4000 signs? JP -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner 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: Mercadante, Thomas F 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
Hmm this is a classic RTFM, but the answer is here ... if you are using dbms_sql then use procedure dbms_sql.parse(c in integer, statement in varchar2s, lb in integer, ub in integer, lfflg in boolean, language_flag in integer); syntax. This requires you to declare a local variable of type dbms_sql.varchar2s. This type is essentially an index by table of varchar2(2000), so you are then pretty much limited by your creativity for the length of the sql statement. More information is available in $ORACLE_HOME/rdbms/admin/dbmssql.sql (it is a very good reading). If you are using execute immediate thant I don't see what you'd have a problem with 4000 characters. You mention 4000, so I assume you are on 8i or 9i, then since Oracle 7x, the varchar2 variable is 32K-1 i.e. 32767 in pl/sql, way more than 4000 characters. In either case, you win. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
Re: Dynamic SQL
Depends how you are getting that 4,000 characters to the procedure, of course, but a pl/sql varchar2() can in principle be 32,000 bytes. And if that isn't enough, you may have to fall back to dbms_sql which exposes a packaged type which is an array of varchar2(255) so that you can build, pass, and execute the array. (I think there's a sample of this on my web site). Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 26 March 2002 18:58 |Hello |I'm using variable sqlstr VARCHAR2(4000) in procedure to create SELECT string. |But the length of my SELECT * FROM is bigger than 4000 chars. | |Is there a way how to call OPEN my_cursor FOR sqlstr; |for sqlstr longer than 4000 signs? | |JP -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis 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
Yes, I know it's 32767. My code: PROCEDURE ... sqlstr VARCHAR2(4000); sqlstr := my_pack.GETSQL( sql_id ... ); -- function OPEN my_cursor FOR sqlstr; ... END; If the length of sqlstr is 4000, I get an exception and the error is: sqlstr _IS_NULL_ Why? I'm using 8.1.7.0.3 on Linux. JP On Tue 26. March 2002 20:24, you wrote: Depends how you are getting that 4,000 characters to the procedure, of course, but a pl/sql varchar2() can in principle be 32,000 bytes. And if that isn't enough, you may have to fall back to dbms_sql which exposes a packaged type which is an array of varchar2(255) so that you can build, pass, and execute the array. (I think there's a sample of this on my web site). Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 26 March 2002 18:58 |Hello |I'm using variable sqlstr VARCHAR2(4000) in procedure to create SELECT string. |But the length of my SELECT * FROM is bigger than 4000 chars. | |Is there a way how to call OPEN my_cursor FOR sqlstr; |for sqlstr longer than 4000 signs? | |JP -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jan Pruner 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
Looking at Raj's post I seem to have made at least two errors in my original reply - the size of the varchar2s in the dbms_sql array and the question of whether a varchar2 can be 32K, 32,000 bytes, or 4,000 bytes in pl/sql. Perhaps there are a couple of version-dependent details that need to be checked. Having said that, when you say the error is | I get an exception and the error is: sqlstr _IS_NULL_ what exactly is reporting the error in that format; it doesn't appear to be a normal exception message, and isn't one of the standard exceptions. I have tried to reproduce your problem, but only have 8.1.7.3. If the sql_str variable is declared large enough to hold the incoming string it works as expected, if the variable is larger than the declared length of the variable the error is the usual PL/SQL 6502 numeric or value error. Jonathan Lewis http://www.jlcomp.demon.co.uk Next Seminar - UK, April 3rd - 5th http://www.jlcomp.demon.co.uk/seminar.html Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Author of: Practical Oracle 8i: Building Efficient Databases -Original Message- To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Date: 26 March 2002 20:30 |Yes, I know it's 32767. |My code: |PROCEDURE ... |sqlstr VARCHAR2(4000); | |sqlstr := my_pack.GETSQL( sql_id ... ); -- function | |OPEN my_cursor FOR sqlstr; |... |END; | |If the length of sqlstr is 4000, | I get an exception and the error is: sqlstr _IS_NULL_ |Why? |I'm using 8.1.7.0.3 on Linux. | |JP | -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jonathan Lewis 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
Sorry, fat fingers varchar2s is a table of varchar2(256) Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art! *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2
RE: Dynamic SQL
Title: Dynamic SQL Laura, Here's one. In this case, if the in variable 'p_actualenddate' is passed into the procedure, and additional 'and' clause is added to the where clause. Any questions, give me a shout. PROCEDURE ListScheduleforDelete ( p_employid IN VARCHAR2, p_statusIN WTW_EMPL_WEEKENDING_SCHEDULE.SCHED_STATUS_CODE%TYPE, p_actualenddate IN WTW_EMPLOYMENT.EMPLOYMENT_START_DATE%TYPE, p_refCursor IN OUT empRS ) IS sel_string VARCHAR2(2000) := ' SELECT WTW_EMPL_WEEKENDING_SCHEDULE.EMPLOYMENT_ID, ' || ' WTW_EMPL_WEEKENDING_SCHEDULE.EMPLOYMENT_WEEKEND_DATE, ' || ' WTW_EMPL_WEEKENDING_SCHEDULE.TOTAL_ACTUAL_HRS_NBR,' || ' WTW_EMPL_SCHED_STATUS_CODE.SCHED_LONG_TXT,' || ' WTW_EMPL_WEEKENDING_SCHEDULE.TOTAL_SCHED_HRS_NBR ' || ' FROM WTW_EMPL_WEEKENDING_SCHEDULE, WTW_EMPL_SCHED_STATUS_CODE ' || ' WHERE WTW_EMPL_WEEKENDING_SCHEDULE.EMPLOYMENT_ID = :p_employid AND ' || ' WTW_EMPL_WEEKENDING_SCHEDULE.SCHED_STATUS_CODE = :p_status AND ' || ' WTW_EMPL_SCHED_STATUS_CODE.SCHED_STATUS_CODE(+) = WTW_EMPL_WEEKENDING_SCHEDULE.SCHED_STATUS_CODE '; where_string VARCHAR2(400); where_str VARCHAR2(7) := ' AND '; ActualDatePlusSeven DATE; BEGIN IF p_actualenddate IS NOT NULL THEN ActualDatePlusSeven := p_actualenddate + 7; where_string := where_str || ' WTW_EMPL_WEEKENDING_SCHEDULE.EMPLOYMENT_WEEKEND_DATE = ' || || ActualDatePlusSeven || || ' '; END IF; OPEN p_refCursor FOR sel_string || where_string || ' ORDER BY 2 DESC' USING p_employid, p_status; END ListScheduleforDelete; -- %%% Tom Mercadante Oracle Certified Professional -Original Message-From: Burton, Laura L. [mailto:[EMAIL PROTECTED]]Sent: Tuesday, February 19, 2002 4:57 PMTo: Multiple recipients of list ORACLE-LSubject: Dynamic SQL We have Oracle version 8.0.5 and need to use dynamic sql. Through research I know that there is a dbms_sql package that is suppose to support this, but we cannot find an example of what we are needing to do. We have been told that we can do it easily in '8i' but we are not able to upgrade yet. We are trying to populate a reference cursor via a procedure with a select statement. Has anyone done this and if so can you furnish an example? I may need to tell more about what we are doing and if so please tell me. Thank you, Laura
RE: Dynamic SQL
Title: Dynamic SQL Laura, Here is an example: create or replace package pkg_drill_thru as type CompanyPhone is ref cursor; Procedure get_mobile_numbers( activity_dt date, company_cd varchar2, channel_cd varchar2, subscriber_cd varchar2, initial_add number, volun_disc number, involun_disc number, volun_recon number, involun_recon number, no_install number, company_phone in out CompanyPhone); end;/ create or replace package body pkg_drill_thru as Procedure get_mobile_numbers( activity_dt date, company_cd varchar2, channel_cd varchar2, subscriber_cd varchar2, initial_add number, volun_disc number, involun_disc number, volun_recon number, involun_recon number, no_install number, company_phone in out CompanyPhone) as sql_str varchar2(2000); begin sql_str := 'select nvl(company_desc, a.company_code) company, a.channel_code, mobile_number from dly_transaction_detail a, company_dimension b where activity_date = :1 and subscriber_code = upper(:2) and a.company_code in (select company_code from company_dimension start with parent_company_code = :3 connect by parent_company_code = prior company_code union select :4 from dual) and a.channel_code in (select channel_code from channel_dimension start with parent_channel_code = :5 connect by parent_channel_code = prior channel_code union select :6 from dual) and a.company_code = b.company_code'; if initial_add = 1 then sql_str := sql_str || ' and initial_add = 1'; elsif volun_disc = 1 then sql_str := sql_str || ' and volun_disc = 1'; elsif involun_disc = 1 then sql_str := sql_str || ' and involun_disc = 1'; elsif volun_recon = 1 then sql_str := sql_str || ' and volun_recon = 1'; elsif involun_recon = 1 then sql_str := sql_str || ' and involun_recon = 1'; elsif no_install = 1 then sql_str := sql_str || ' and no_install = 1'; end if; open company_phone for sql_str using activity_dt, subscriber_cd, company_cd, company_cd, channel_cd, channel_cd; end; end; Prakash -Original Message-From: Burton, Laura L. [mailto:[EMAIL PROTECTED]]Sent: Tuesday, February 19, 2002 4:57 PMTo: Multiple recipients of list ORACLE-LSubject: Dynamic SQL We have Oracle version 8.0.5 and need to use dynamic sql. Through research I know that there is a dbms_sql package that is suppose to support this, but we cannot find an example of what we are needing to do. We have been told that we can do it easily in '8i' but we are not able to upgrade yet. We are trying to populate a reference cursor via a procedure with a select statement. Has anyone done this and if so can you furnish an example? I may need to tell more about what we are doing and if so please tell me. Thank you, Laura
Re: Dynamic SQL
Burton, Laura L. wrote: We have Oracle version 8.0.5 and need to use dynamic sql. Through research I know that there is a dbms_sql package that is suppose to support this, but we cannot find an example of what we are needing to do. We have been told that we can do it easily in '8i' but we are not able to upgrade yet. We are trying to populate a reference cursor via a procedure with a select statement. Has anyone done this and if so can you furnish an example? I may need to tell more about what we are doing and if so please tell me. Thank you, Laura Laura, Read $ORACLE_HOME/rdbms/admin/dbmssql.sql, it contains examples. -- Regards, Stephane Faroult Oriole Ltd -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroult 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
Hi, What exactly would you like to know about dynamic sql? Kev. hit any user to continue __ Kevin Thomas Technical Analyst Deregulation Services Calanais Ltd. (2nd Floor East - Weirs Building) Tel: 0141 568 2377 Fax: 0141 568 2366 http://www.calanais.com -Original Message- Sent: 03 October 2001 09:30 To: Multiple recipients of list ORACLE-L Hi Lisits I would appreciate any feed back on this topic. dbms_sql.open_cursor dbms_sql.parse dbms_sql.define_column dbms_sql.execute_and_fetch dbms_sql.column_value dbms_sql.close_cursor Regrds NItheesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pullikol Kumar 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
I like all of them and since 8i, dislike them Seriously what kind of feedback you want? Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! *1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *1
RE: Dynamic Sql
Hi Rajendra Jamadagni, Can U explain about dbms_sql.open_cursor dbms_sql.parse dbms_sql.define_column dbms_sql.execute_and_fetch dbms_sql.column_value dbms_sql.close_cursor I tried in Oracle PLSQL Documents, but couldn't trace about these. Hope U can help me. Regards Nitheesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pullikol Kumar 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
Try looking in the Application Developers Guide. For 7.3 it was Chapter 10, though this may have changed. At 05:55 AM 10/3/01 -0800, you wrote: Hi Rajendra Jamadagni, Can U explain about dbms_sql.open_cursor dbms_sql.parse dbms_sql.define_column dbms_sql.execute_and_fetch dbms_sql.column_value dbms_sql.close_cursor I tried in Oracle PLSQL Documents, but couldn't trace about these. Hope U can help me. Regards Nitheesh -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Pullikol Kumar 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: Regina Harter 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
Kumar, so far in my experience the best explanation of these built-ins can be found in $ORACLE_HOME/rdbms/admin/dbmssql.sql I learned from those first and then touched the Application Developers Guide. Good luck Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! *1 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *1
RE: DYNAMIC SQL - Please mod request to trap error when insert fa
Al,
did you issue a set serveroutput on before you tried executing the
procedure to see if your proc worked ok?
your procedure looks ok, the only other thing I would check is to run the
select statement outside of the proc to be sure that records are selected
ok.
one other thing - depending on what version of Oracle you are running, you
could try and run the new version of dynamic sql.
you could change your statement to:
BEGIN
execute immediate 'INSERT INTO scan_contract ' ||
'SELECT CONTRACT_BEGIN_DATE, ' ||
'NSN, CONTRACT, CONTRACT_END_DATE, ' ||
'FUTURE_EFF_DATE, FUTURE_SELL_PRICE, ' ||
'SELL_PRICE, UPDATE_DATE, DODAAC, ' ||
p_ffs ||
' FROM ' || p_table ;
g_rows_inserted := sql%rowcount;
dbms_output.put_line ('ROWS INSERTED: ' || g_rows_inserted);
EXCEPTION
WHEN OTHERS THEN
RAISE;
END PopScanContract;
hope this helps
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Sunday, September 09, 2001 2:20 PM
To: Multiple recipients of list ORACLE-L
fails
I CREATED PROCEDURE:
CREATE OR REPLACE PROCEDURE PopScanContract (
p_table IN VARCHAR2,
p_ffs IN VARCHAR2) IS
g_statement_txt VARCHAR2(500);
g_cursor_id_num PLS_INTEGER;
g_rows_inserted PLS_INTEGER := 0;
BEGIN
g_cursor_id_num := DBMS_SQL.OPEN_CURSOR;
g_statement_txt := 'INSERT INTO scan_contract ' ||
'SELECT CONTRACT_BEGIN_DATE, ' ||
'NSN, CONTRACT, CONTRACT_END_DATE, ' ||
'FUTURE_EFF_DATE, FUTURE_SELL_PRICE, ' ||
'SELL_PRICE, UPDATE_DATE, DODAAC, ' ||
p_ffs ||
' FROM ' || p_table ;
DBMS_SQL.PARSE(g_cursor_id_num,
g_statement_txt,
DBMS_SQL.NATIVE);
g_rows_inserted := DBMS_SQL.EXECUTE(g_cursor_id_num);
dbms_output.put_line ('ROWS INSERTED: ' || g_rows_inserted);
DBMS_SQL.CLOSE_CURSOR(g_cursor_id_num);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(g_cursor_id_num) THEN
DBMS_SQL.CLOSE_CURSOR(g_cursor_id_num);
END IF;
RAISE;
END PopScanContract;
I EXECUTE AS:
exec PopScanContract('sm_contract_rge', 'RGE')
I GET:
PL/SQL procedure successfully completed.
YET:
The table 'scan_contract' still contains the same number of rows AFTER
the procedureexecutes As BEFORE the procedure executed.
ANY HELP WILL BE GREATLY APPRECIATED !!
TIA
Al Rusnak
804-734-8453
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rusnak, George A.
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: Mercadante, Thomas F
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 - Please mod request to trap error when insert fa
George ... After insert you need a commit somewhere ... preferable after the dbms_sql.execute only then number of rows actually written to the database will increase. Raj __ Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com Any opinion expressed here is personal and doesn't reflect that of ESPN Inc. QOTD: Any clod can have facts, but having an opinion is an art ! *2 This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. *2 -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jamadagni, Rajendra 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
Try commiting (COMMIT;) before your exception clause.
- Original Message -
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Sent: Sunday, September 09, 2001 1:10 PM
I CREATED PROCEDURE:
CREATE OR REPLACE PROCEDURE PopScanContract (
p_table IN VARCHAR2,
p_ffs IN VARCHAR2) IS
g_statement_txt VARCHAR2(500);
g_cursor_id_num PLS_INTEGER;
g_rows_inserted PLS_INTEGER := 0;
BEGIN
g_cursor_id_num := DBMS_SQL.OPEN_CURSOR;
g_statement_txt := 'INSERT INTO scan_contract ' ||
'SELECT CONTRACT_BEGIN_DATE, ' ||
'NSN, CONTRACT, CONTRACT_END_DATE, ' ||
'FUTURE_EFF_DATE, FUTURE_SELL_PRICE, ' ||
'SELL_PRICE, UPDATE_DATE, DODAAC, ' ||
p_ffs ||
' FROM ' || p_table ;
DBMS_SQL.PARSE(g_cursor_id_num,
g_statement_txt,
DBMS_SQL.NATIVE);
g_rows_inserted := DBMS_SQL.EXECUTE(g_cursor_id_num);
dbms_output.put_line ('ROWS INSERTED: ' || g_rows_inserted);
DBMS_SQL.CLOSE_CURSOR(g_cursor_id_num);
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(g_cursor_id_num) THEN
DBMS_SQL.CLOSE_CURSOR(g_cursor_id_num);
END IF;
RAISE;
END PopScanContract;
I EXECUTE AS:
exec PopScanContract('sm_contract_rge', 'RGE')
I GET:
PL/SQL procedure successfully completed.
YET:
The table 'scan_contract' still contains the same number of rows
AFTER
the procedureexecutes As BEFORE the procedure executed.
ANY HELP WILL BE GREATLY APPRECIATED !!
TIA
Al Rusnak
804-734-8453
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rusnak, George A.
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).
_
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Scott Shafer
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
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 Harvinder.Singh@metr To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] 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).
HELP! Re: Dynamic SQL- Issue a connect stmt.
Could anybody think of any other way to open a session from within a
procedure, by accepting userid and password from the user.
Sarah, thanks very much trying.
Radhika.
From: Sarah Satterthwaite [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Re: Dynamic SQL- Issue a connect stmt.
Date: Tue, 06 Feb 2001 15:44:43 -0800
I have tried this and could not get it to work. I think connect is not a
SQL statement, but a SQLPlus statement, and the dynamic_sql package only
works on SQL, not SQLPlus.
Sarah Satterthwaite
Cambridge, MA
Manasa Rao wrote:
All,
I am trying to open another session using input parameters passed to the
procedure. Can I open a session in a procedure and if so how do I stay
in
the opened session?
Is it possible at all?
I am placing the code that I used.
Appreciate any ideas or suggestions.
Radhika.
create or replace procedure open_session(userid_in in varchar2,
password_in in varchar2)
is
connect_string varchar2(14);
sql_stmt varchar2(2000);
curr_user varchar2(20);
cursor_handle integer;
feedback_v integer;
begin
connect_string := 'xxx_database'; --database connect string.
/* create a cursor to user fo the dynamic sql */
cursor_handle := DBMS_SQL.OPEN_CURSOR;
/*construct the sql statement and parse it. */
sql_stmt := 'connect
'||userid_in||'/'||password_in||'@'||connect_string||';';
dbms_output.put_line('Constructed SqlStmt is: '||sql_stmt);
/* parse the sql statment */
DBMS_SQL.PARSE
(cursor_handle, sql_stmt, DBMS_SQL.NATIVE);
/* execute the sql statement */
feedback_v := DBMS_SQL.EXECUTE(cursor_handle);
/* check to see which user is connected */
select user
into curr_user
from dual;
dbms_output.put_line(curr_user);
dbms_output.put_line('Feedback'||feedback_v);
/* close the cursor */
DBMS_SQL.CLOSE_CURSOR(cursor_handle);
EXCEPTION
WHEN OTHERS
THEN
/* close cursor on failure */
DBMS_SQL.CLOSE_CURSOR(cursor_handle);
end validate_user;
/
_
Get your FREE download of MSN Explorer at http://explorer.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Manasa Rao
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: Sarah Satterthwaite
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).
_
Get your FREE download of MSN Explorer at http://explorer.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Manasa Rao
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- Issue a connect stmt.
I have tried this and could not get it to work. I think connect is not a SQL
statement, but a SQLPlus statement, and the dynamic_sql package only works on SQL, not
SQLPlus.
Sarah Satterthwaite
Cambridge, MA
Manasa Rao wrote:
All,
I am trying to open another session using input parameters passed to the
procedure. Can I open a session in a procedure and if so how do I stay in
the opened session?
Is it possible at all?
I am placing the code that I used.
Appreciate any ideas or suggestions.
Radhika.
create or replace procedure open_session(userid_in in varchar2,
password_in in varchar2)
is
connect_string varchar2(14);
sql_stmt varchar2(2000);
curr_user varchar2(20);
cursor_handle integer;
feedback_v integer;
begin
connect_string := 'xxx_database'; --database connect string.
/* create a cursor to user fo the dynamic sql */
cursor_handle := DBMS_SQL.OPEN_CURSOR;
/*construct the sql statement and parse it. */
sql_stmt := 'connect
'||userid_in||'/'||password_in||'@'||connect_string||';';
dbms_output.put_line('Constructed SqlStmt is: '||sql_stmt);
/* parse the sql statment */
DBMS_SQL.PARSE
(cursor_handle, sql_stmt, DBMS_SQL.NATIVE);
/* execute the sql statement */
feedback_v := DBMS_SQL.EXECUTE(cursor_handle);
/* check to see which user is connected */
select user
into curr_user
from dual;
dbms_output.put_line(curr_user);
dbms_output.put_line('Feedback'||feedback_v);
/* close the cursor */
DBMS_SQL.CLOSE_CURSOR(cursor_handle);
EXCEPTION
WHEN OTHERS
THEN
/* close cursor on failure */
DBMS_SQL.CLOSE_CURSOR(cursor_handle);
end validate_user;
/
_
Get your FREE download of MSN Explorer at http://explorer.msn.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Manasa Rao
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: Sarah Satterthwaite
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).
