Re: bind variables & dbms_sql in procedure/...

IIRC, you can't do that.

Consider the preparatory steps in using DBMS_SQL.

parse a cursor, define and\/or bind the columns and execute.

An unkown number of parameters kind of precludes the use of DBMS_SQL.

Better to use native dynamic SQL in this case IMO.

That's not to say that there isn't a convoluted method that could be used
to accomplish this with DBMS_SQL, I think it *could* be done.

In the end though it would be very complex and offer no benefits over
native dynamic sql, which would also execute faster.

I'm not offering any proof. Let someone else prove this is wrong, which is
much easier than me proving it is correct. :)

HTH

Jared

"George Leonard" <leonarge@vodacom.co.za>
Sent by: oracle-l-bounce@freelists.org

03/30/2004 12:11 AM
Please respond to oracle-l

To: <oracle-l@freelists.org>
cc:
Subject: bind variables & dbms_sql in procedure/packages

Hi all.

Can someone please send me an example of using bind variables in
dbms_sql in a procedure or package for the where clause where the where
clause is not hard coded/pre-defined, meaning the columns to include is
dependable on the variables passed.

I have so far send my developers allot of links from asktom but would
like to give them more information to ensure the implementation is spot
on since it I such an important component of the Oracle
architecture/implementation.

Thanks.

George

________________________________________________

George Leonard
Oracle Database Administrator
Dimension Data (Pty) Ltd
(Reg. No. 1987/006597/07)
Tel: (+27 11) 575 0573
Fax: (+27 11) 576 0573
E-mail:george.leonard@za.didata.com
Web: http://www.didata.co.za

You Have The Obligation to Inform One Honestly of the risk, And As a
Person
You Are Committed to Educate Yourself to the Total Risk In Any Activity!
Once Informed & Totally Aware of the Risk,
Every Fool Has the Right to Kill or Injure Themselves as They See Fit!

"This e-mail is sent on the Terms and Conditions that can be accessed by Clicking on this link http://www.vodacomnet/legal/email.asp "

Re: bind variables & dbms_sql in procedure/packages

Jared Still

2004-03-30