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_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: DENNIS WILLIAMS
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).
