RE: bind variables & dbms_sql in procedure/packages

From: <Stephen.Lee_at_DTAG.Com>
Date: Tue, 30 Mar 2004 09:13:27 -0600
Message-ID: <D6339830FC73944E889CC3CEADDB205B079092D3@bu-dtagpo1.tracs.com>

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


-----------------------------------------------------------------

Received on Tue Mar 30 2004 - 09:11:02 CST