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_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; -------------------------------------------- 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 -- Mailing list and web hosting services --------------------------------------------------------------------- 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.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [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.net -- Author: Igor Neyman INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [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.net -- Author: Freeman Robert - IL INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [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).