| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: Need help passing parameter into Stored Proc
In article <8jb12d$7vh$1_at_nnrp2.deja.com>,
vladn2001_at_my-deja.com wrote:
> Hi everybody,
>
> I have a stored procedure that needs to update
> multiple products in one call based on item_sku
> that I am passing as a parameter.
>
> When item sku are hardcoded it executes fine:
>
> UPDATE PRODUCTS SET DESCR = '123TEST' WHERE
> ITEM_SKU IN ('371948,371872')
>
> However, when I am passing those items skus as a
> variable this statement will not execute at all:
>
> vITEM_SKU IN VARCHAR2(255);
>
> --Hardcoded for testing
> vITEM_SKU := '371948,371872';
>
> UPDATE PRODUCTS SET DESCR = '123TEST' WHERE
> ITEM_SKU IN (vITEM_SKU);
>
> I assume that Oracle handles this variable as one
> string, therefore treating it as one item_sku.
> Is my assumption correct ? If it is, what can be
> done to work around this issue.
>
> I would appreciate any help or advice.
>
> Thanks, Davie (eclipse98_at_hotmail.com)
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
sigh... never a version number to be found...
You want to do this one of the following ways -- do NOT use dynamic sql and concatenate in the values as constants, that will only trash your shared pool. The following are "bind variable" friendly.
In Oracle7, this is rather difficult. You cannot bind in a SINGLE value and expect it to be treated as MANY values (think about how hard it would be to find something in an IN LIST that had a "comma" in it for example).
In Oracle8.0 and up, this is easy with object types and nested tables. Just to see what that will look like, I'll show that - then I'll show the v7 implementation.
ops$tkyte_at_8i> create or replace type myTableType
as table of number;
2 /
Type created.
ops$tkyte_at_8i> create or replace function in_list
( p_string in varchar2 ) return myTableType
2 as
3 l_data myTableType := myTableType();
4 l_string long default p_string || ',';
5 l_n number;
9 exit when l_string is null; 10 l_data.extend; 11 l_n := instr( l_string, ',' ); 12 l_data( l_data.count ) := substr( l_string, 1, l_n-1 ); 13 l_string := substr( l_string, l_n+1 );14 end loop;
Function created.
So, in Oracle8 and up we can "select * from PLSQL_FUNCTION" when the function returns a SQL Table type as follows:
ops$tkyte_at_8i>
ops$tkyte_at_8i> select *
2 from THE ( select cast( in_list('1,2,3,5,12')
as mytableType ) from dual ) a
3 /
COLUMN_VALUE
1
2
3
5
12
Which means we can do that in a subquery:
ops$tkyte_at_8i> select *
2 from all_users
3 where user_id in
4 ( select * from THE (
select cast( in_list('1,2,3,5,12')
as mytableType ) from dual ) a )
5 /
USERNAME USER_ID CREATED ------------------------------ ---------- --------- SYSTEM 5 20-APR-99
In v7, we can do something very similar (works in 8.0 and up as well) with plsql tables. Its a little more work but not too much. It would look like this:
ops$tkyte_at_8i> create or replace package my_pkg 2 as
3 function in_list( p_string in varchar2,
p_idx in number ) return varchar2;
4 pragma restrict_references( in_list,
wnds, rnds, wnps, rnps );
5
6 function get_list_count(p_string in varchar2)
return number;
7 pragma restrict_references( get_list_count,
wnds, rnds, wnps, rnps );
8
9 pragma restrict_references( my_pkg,
wnds, rnds, wnps, rnps );
10 end;
Package created.
ops$tkyte_at_8i>
ops$tkyte_at_8i> create or replace package body my_pkg
2 as
3 function in_list( p_string in varchar2,
p_idx in number ) return varchar2
4 is
5 l_start number;
6 l_stop number;
l_stop-l_start ) ) );
17 end;
return number
20 is
21 l_cnt number default 0;
22 begin
23 for i in 1 .. 1000 loop 24 exit when nvl( instr( p_string, ',', 1, i ), 0 ) = 0; 25 l_cnt := i+1; 26 end loop; 27 28 return l_cnt;
Package body created.
So, now I can code a query like:
ops$tkyte_at_8i> variable bindVar varchar2(255) ops$tkyte_at_8i> ops$tkyte_at_8i> exec :bindVar := '1, 3, 44, 1000'
PL/SQL procedure successfully completed.
ops$tkyte_at_8i>
ops$tkyte_at_8i> select my_pkg.in_list( :bindVar, rownum )
2 from all_objects
3 where rownum <=
( select my_pkg.get_list_count( :bindVar ) from dual ) 4 /
MY_PKG.IN_LIST(:BINDVAR,ROWNUM)
All we need for that to work is a table with MORE rows then we have IN LIST items -- all_objects is generally a good candidate. It works by using rownum as an "index" into the plsql table.
So, now we can simply:
ops$tkyte_at_8i> select *
2 from all_users
3 where user_id in (select my_pkg.in_list(:bindVar,rownum)
4 from all_objects
5 where rownum <=
( select my_pkg.get_list_count( :bindVar ) from dual )
6 )
7 /
USERNAME USER_ID CREATED ------------------------------ ---------- --------- WEB$AZEILMAN 1000 24-JUN-99
-- Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries Howtos and such: http://osi.oracle.com/~tkyte/index.html Oracle Magazine: http://www.oracle.com/oramag Opinions are mine and do not necessarily reflect those of Oracle Corp Sent via Deja.com http://www.deja.com/ Before you buy.Received on Tue Jun 27 2000 - 00:00:00 CDT
 |
 |