RE: bind variables & dbms_sql in procedure/packages

From: George Leonard <leonarge_at_vodacom.co.za>
Date: Wed, 31 Mar 2004 07:05:11 +0200
Message-ID: <99CEA8FB33554B4BAC372CE602F9B7ED0370D996@zamdh02001.vodacom.corp>

Hi there

That is what is being done atm, they are building literal string and executing them, now we have a 8cpu Sun 4800 and it is CPU strain because of all the parsing etc because of this.

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_at_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!

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stephen.Lee_at_DTAG.Com Sent: 30 March 2004 17:13 PM
To: oracle-l_at_freelists.org
Subject: RE: bind variables & dbms_sql in procedure/packages

---------------Original Message---------------
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.

One way to do this (don't know if it is the only way) is to dynamically build a string variable which becomes the text of the command, then execute
it. For example.

mystring := 'some text';
mystring := mystring || ' some more text';
mystring := mystring || ' some text '|| input_variable;

etc.
etc.

open cursor_variable for mystring;

You can do essentially the same thing with a string to be run by dbms_sql or
execute immediate, I think.
A couple of problems with this kind of approach: The resulting string no longer has bind variables but all literal values. It might be possible to
sneak malicious sql into the string.

I'm far from being an expert in PL/SQL, but it seems that by using packages,
you might be able to use overloading or polymorphism.

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

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

Please see the official ORACLE-L FAQ: http://www.orafaq.com

Received on Tue Mar 30 2004 - 23:01:15 CST