RE: bind variables & dbms_sql in procedure/packages
- From: Stephen.Lee@xxxxxxxx
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 30 Mar 2004 09:13:27 -0600
---------------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
-----------------------------------------------------------------
Other related posts:
