Re: Passing multi values into an in clause via a parameter in a store procedure
Date: Tue, 27 Jan 2004 19:11:41 -0500
Message-ID: <CP6dnc16fanGY4vdRVn-tw_at_comcast.com>
"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:...
|
| "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message news:...
| |
| | "Berend" <Berend.Brinkhuis_at_evatone.com> wrote in message
| | news:bdd9ac20.0401271301.22cdb65e_at_posting.google.com...
| | | I am trying to pass multi values into a where clause with an in clause
| | | in a store procedure to use in a Crystal report. This can change
| | | depending on the user. Maybe there is another way to pass multi
| | | values.
| | |
| | |
| | | CREATE OR REPLACE PROCEDURE eva_sp_wrk014_spec_test (
| | | p_eva_product_header_ids IN VARCHAR2,
| | | cur_spec_cd IN OUT
| | | sysadm.eva_pkg_wrk014_spec_test.ref_spec_spec_cd
| | | )
| | | AS
| | | BEGIN
| | | OPEN cur_spec_cd
| | | FOR
| | | SELECT *
| | | FROM sysadm.eva_product_header eph
| | | WHERE eph.eva_product_header_id in (p_eva_product_header_ids);
| | | END eva_sp_wrk014_spec_test;
| |
| | the IN clause requires separate values (i.e. a separate bind variable
for
| | each value), and you've got all your values stuffed into one variable,
the
| | equivalent of
| |
| | WHERE eph.eva_product_header_id in ( '00,01,23,43,09,33' )
| |
| | which attempts to find the value '00,01,23,43,09,33' not the one of the
| | individual values
| |
| | you'll need to rewrite your proc to use dynamic sql, or pull a trick
like
| |
| | WHERE instr( p_eva_product_header_ids, cDelimiter ||
| | eph.eva_product_header_id || cDelimiter) > 0
| |
| | (the p_eva_product_header_ids parameter would need to have delimiters
| | between each value, and at the first and last position) -- not great for
| | performance if it's the only criteria
| |
| | other options: write the list of IDs to a temp table (perhaps within the
| | proc itself) and use a subquery or join to the temp table
| |
| | i think there's also a way these days to write a proc that returns a
rowset
| | that can be used as a SQL table -- that might be another way to
transform
| | the common separated list of ids into something useful in a non-dynamic
SQL
| | statement
| |
| | or -- write a bunch of explicit ORs that can handle up to the max number
or
| | IDs you think you'd be searching for, then parse out the IDs into local
| | variables
| |
| | -- mcs
| |
| |
|
| here's a reference to the using the TABLE and CAST operators/keywords to
| feed a the contents of a multi-valued variable to a SQL statement (thanks
to
| another post by dan morgan) http://www.psoug.org/reference/conditions.html
|
| (forget about the temp table and explicit ORs suggestions)
|
| -- mcs
|
|
and here's an example that includes parsing the comma-separated input parameter into the PL/SQL table:
create or replace type vc4000tbl is table of varchar2(4000); /
create or replace procedure list_emps ( ip_depts in varchar2 ) is
- declare and initialize PL/SQL table based on pre-created type
tbl_deptno vc4000tbl := vc4000tbl();
- variables needed for parsing offset number default 1; dlen number default length(ip_depts); slen number; c_sep constant varchar2(1) := ',';
begin
- load deptno table from comma separated list
- this could be wrapped in a procedure for generalization and reuse
while offset < dlen
loop
slen := instr(ip_depts,c_sep,offset);
if slen = 0
then
slen := dlen+1;
end if;
tbl_deptno.extend;
tbl_deptno(tbl_deptno.count) := substr(ip_depts,offset,slen-offset);
offset := slen+1;
end loop;
- use the deptno table in subquery with TABLE and CAST syntax
- (for illustration -- this would likely be used with a ref cursor)
for r1 in (
select empno, ename, deptno
from emp
where deptno in (
select column_value
from table(cast(tbl_deptno as vc4000tbl))
)
order by deptno, empno
)
loop
dbms_output.put_line(
r1.deptno
|| ': '
|| r1.empno
|| ' -- '
|| r1.ename);
end loop;
end list_emps;
- mcs