RE: bind variables & dbms_sql in procedure/packages
- From: "Davey, Alan" <Alan.Davey@xxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 30 Mar 2004 08:28:57 -0700
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@xxxxxxxx [mailto:Stephen.Lee@xxxxxxxx]
Sent: Tuesday, March 30, 2004 10:13 AM
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;
----------------------------------------------------------------
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 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@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:
