Re: More Ammo Against Dynamic SQL?

Kellyn,  

Depending upon your position in the organization, and the fact that you are 
fairly new within in,  I believe your presenation may come across as 
"pontificating".  

What I would do, in your shoes, is find the source and basis for the existence 
of the dynamic SQL.  If it's coming from purchased application packages, even 
if everyone
agrees with you, the code cannot easily be changed;  if it's endemic to the 
culture, you're going to have to work from the inside out to change it.  Rather 
than
doing a presentation, this might mean working with the developers and 
attempting to explain how other ways and methods are better; working with the 
groups responsible
for the code will take awhile but might ultimately be more rewarding.  

As you've observed from this thread, you'll get support from many people on 
your position, but if you went to a primarily development site, you'd hear a 
lot of opinions
from the other side.  Unfortunately, right or wrong, there's more of them than 
us...

----- Original Message -----
From: "Kellyn Pedersen" <kjped1313@xxxxxxxxx>
To: "cary millsap" <cary.millsap@xxxxxxxxxxxx>, "michael fontana" 
<michael.fontana@xxxxxxxxxxx>
Cc: mwf@xxxxxxxx, "oracle Freelists" <oracle-l@xxxxxxxxxxxxx>
Sent: Monday, November 23, 2009 8:28:01 PM GMT -06:00 US/Canada Central
Subject: Re: More Ammo Against Dynamic SQL?


Sigh... I'm in complete agreement with all of you and as the "new DBA on the 
block" I get the lovely task of showing why this isn't such a good idea vs. 
"this is the way we've always done it!" :) 

I like the correlation between dynamic sql and jaywalking though. I could make 
some reference in my presentation to "hit and runs" by the DBA's....hmm... 

Thank you, this is the first place I've seen use dynamic SQL other than small 
adhoc script generation for maintenance tasks, etc. and it's making me see red! 



Kellyn Pedersen 
Multi-Platform DBA 
I-Behavior Inc. 
http://www.linkedin.com/in/kellynpedersen 

"Go away before I replace you with a very small and efficient shell script..." 

--- On Mon, 11/23/09, Michael Fontana <michael.fontana@xxxxxxxxxxx> wrote: 



From: Michael Fontana <michael.fontana@xxxxxxxxxxx> 
Subject: Re: More Ammo Against Dynamic SQL? 
To: "cary millsap" <cary.millsap@xxxxxxxxxxxx> 
Cc: mwf@xxxxxxxx, "oracle Freelists" <oracle-l@xxxxxxxxxxxxx> 
Date: Monday, November 23, 2009, 3:53 PM 


The sad thing is, type "generating dynamic sql within application" in a search 
engine and you'll get close to a million hits! 

And the folks showing you how to do it are SO PROUD of their work. Their 
arguments for so doing completely and thoroughly miss the point (something 
about not knowing the data values or table names in advance is usually 
mentioned). 

Type in "how to jaywalk" and you won't get nearly as much information, and yet 
doing so is almost as dangerous.... 




----- Original Message ----- 
From: "Cary Millsap" < cary.millsap@xxxxxxxxxxxx > 
To: "michael fontana" < michael.fontana@xxxxxxxxxxx > 
Cc: mwf@xxxxxxxx , "oracle Freelists" < oracle-l@xxxxxxxxxxxxx > 
Sent: Monday, November 23, 2009 4:43:55 PM GMT -06:00 US/Canada Central 
Subject: Re: More Ammo Against Dynamic SQL? 

Michael, 



I am now thinking that this thread is missing the main point. 
There should be no SQL queries whatsoever in the application. 
It should only call functions, procedures and packages with business rules 
stored in the database. 

I have a development bias, and I agree with your three sentences 100%. 


Cary Millsap 
Method R Corporation 
http://method-r.com 



On Mon, Nov 23, 2009 at 4:00 PM, Michael Fontana < michael.fontana@xxxxxxxxxxx 
> wrote: 


I am now thinking that this thread is missing the main point. 
There should be no SQL queries whatsoever in the application. 
It should only call functions, procedures and packages with business rules 
stored in the database. 

I'll leave on my own now, before those with development biases me out..... 






----- Original Message ----- 
From: "Mark W. Farnham" < mwf@xxxxxxxx > 
To: kjped1313@xxxxxxxxx , "oracle Freelists" < oracle-l@xxxxxxxxxxxxx > 
Sent: Monday, November 23, 2009 3:49:36 PM GMT -06:00 US/Canada Central 
Subject: RE: More Ammo Against Dynamic SQL? 




Toons and Richard gave you some very nice ones. 



To those I would add the ability, in the event that your application includes 
access over a wide area, to encode (and document of course) your packages, 
procedures, and functions with short names so that instead of transmitting many 
Ks of sqlcode, your calls across whatever network you’re across are a few 
characters for the package name, a few characters for the procedure or function 
name, plus the required inbound parameters and any results that are retrived. 
You’d be surprised… 



mwf 






From: oracle-l-bounce@xxxxxxxxxxxxx [mailto: oracle-l-bounce@xxxxxxxxxxxxx ] On 
Behalf Of Kellyn Pedersen 
Sent: Monday, November 23, 2009 3:01 PM 
To: oracle Freelists 
Subject: More Ammo Against Dynamic SQL? 




I am working on a presentation to convince my company against some of the 
dastardly dynamic SQL that we have in our code. We perform everything from 
inserts, updates, deletes, selects and CTAS' all with dynamic SQL and it's 
killing me! 


I would love any new reasons NOT to use it, as I have all the standard reasons 
like, inability to reuse sql in the buffer, parsing issues, bind peeking 
issues, execution plan instability, etc.. 


Thanks for the assist! :) 


Kellyn Pedersen 


Multi-Platform DBA 


I-Behavior Inc. 


http://www.linkedin.com/in/kellynpedersen 





"Go away before I replace you with a very small and efficient shell script..." 

-- 






Michael Fontana 

Sr. Technical Consultant 

Enkitec M: 214.912.3709 

enkitec 

oracle_certified_partner 







-- 
http://www.freelists.org/webpage/oracle-l 

-- 






Michael Fontana 

Sr. Technical Consultant 

Enkitec M: 214.912.3709 

enkitec 

oracle_certified_partner 




-- 
http://www.freelists.org/webpage/oracle-l 

-- 






Michael Fontana 

Sr. Technical Consultant 

Enkitec M: 214.912.3709 

enkitec

oracle_certified_partner




--
http://www.freelists.org/webpage/oracle-l

Other related posts: