RE: Dynamic SQL: where do you draw the line?

From: Jamadagni, Rajendra <Rajendra.Jamadagni_at_espn.com>
Date: Wed, 25 Jun 2003 12:22:47 -0700
Message-ID: <F001.005B99E9.20030625120943@fatcity.com>

Thomas,

I'd prefer your approach than a spaghetti code of if-then-else, lucky you they haven't discovered GOTO yet ... <g> 
Raj
<FONT face="Courier New"

size=2>--------------------------------------------------------------------------------

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 it could
soemthing look like this:

DELETE FROM TABLE
 WHERE ASSET_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 <FONT

  size=2>--------------------------------------------------------------------------------

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); <FONT

  size=2>   strWhere VARCHAR2(100); <FONT 
  size=2>   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; <FONT 
  size=2>END;                                                                      
  
  -------------------------------------------- <FONT

size=2>Jeffery D Thomas DBA Thomson Information Services Thomson, Inc.
 Email: [EMAIL PROTECTED]
 Indy DBA Master Documentation available at: <A target=_blank
 href="http://gkmqp.tce.com/tis_dba">http://gkmqp.tce.com/tis_dba

Please see the official ORACLE-L FAQ: <A target=_blank href="http://www.orafaq.net">http://www.orafaq.net -- Author: Thomas Jeff INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 <A target=_blank href="http://www.fatcity.com">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: <A target=_blank href="http://www.orafaq.net">http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 <A target=_blank href="http://www.fatcity.com">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).

********************************************************************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

Received on Wed Jun 25 2003 - 14:22:47 CDT