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 -- //www.freelists.org/webpage/oracle-l -- Michael Fontana Sr. Technical Consultant Enkitec M: 214.912.3709 enkitec oracle_certified_partner -- //www.freelists.org/webpage/oracle-l