Re: Dynamic sql

dev..._at_gmail.com wrote:
> Why in sql server environment I ever heard BAD of dynamic sql and in
> Oracle I'm reading instead that it is cool?
>
> Only because of the bind variables way to secure from sql injection?

I wonder where you read dynamic sql generation is 'cool' in Oracle; it isn't any better for performance in Oracle than it was for SQL Server. Dynamic SQL is still hard parsed every time it is executed, even if the same statement is executed multiple times, and even if it uses some form of bind variable. The general rule is:

If you can do it in a single SQL statement, do it. If you can't, then use PL/SQL.
If you need to generate dynamic statements let SQL do the work for you and create a script.
If, for some reason, you can't create a script (you need this within a PL/SQL block) then use native dynamic SQL.

Notice using dynamic SQL is still the last resort to complete a task, which doesn't equate to dynamic SQL being 'cool' in my book.

Post this link where dynamic sql is considered 'cool' in Oracle. I'd like to see who stated such a fallacy.

David Fitzjarrell Received on Tue Aug 29 2006 - 09:44:49 CDT