RE: bind variables & dbms_sql in procedure/packages

From: Davey, Alan <Alan.Davey_at_encodasystems.com>
Date: Tue, 30 Mar 2004 08:28:57 -0700
Message-ID: <7487761A46884449834CAD636919C07B0120CE05@nycexc02.Denver.cjds.com>

Another way could be something like the following:

NOTE that I have not tested this, but based on other code, I'm reasonably confident that it will do what you requested.

Pass in two associative arrays (or whatever the current version of pl/sql calls them) to your procedure. The first will contain the variable names, the second will contain their values.

In the procedure loop through the values: for i in 1..asc_array_col_name.COUNT
loop

add conditions to control predicate structure here l_sql_text := l_sql_text || ' AND tab_col_x = :'||asc_arrary_col_name(i); end loop;
- now parse the sql statement DBMS_SQL.PARSE(l_myCursor, l_sql_text, dbms_sql.native);

now loop again and bind
for i in 1..asc_array_col_name
loop

DBMS_SQL.BIND_VARIABLE(l_myCursor,asc_array_col_name(i),asc_array_col_val(i) );
end loop;

define columns and execute

HTH,

Alan Davey
Senior Analyst/Project Leader
Oracle 9i OCA; 3/4 OCP
w) 973.267.5990 x458
w) 212.295.3458

-----Original Message-----
From: Stephen.Lee_at_DTAG.Com [mailto:Stephen.Lee_at_DTAG.Com] Sent: Tuesday, March 30, 2004 10:13 AM
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;

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 information in this e-mail is intended solely for the addressee and
may contain information which is confidential or privileged.  Access to this
e-mail by anyone else is unauthorized.  If you are not the intended
recipient, or believe that you have received this communication in error,
please do not print, copy, retransmit, disseminate, or otherwise use the
information. Also, please notify the sender that you have received this
e-mail in error, and delete the copy you received."


----------------------------------------------------------------
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:35:53 CST