RE: bind variables & dbms_sql in procedure/packages
- From: "George Leonard" <leonarge@xxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 31 Mar 2004 07:05:11 +0200
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@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Stephen.Lee@xxxxxxxx
Sent: 30 March 2004 17:13 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //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 "
Other related posts:
