Use dynamic sql
PROCEDURE upd_details(p_list varchar2) IS
sql_stmt varchar2(2000);
BEGIN
sql_stmt := 'update cm_details
set wd_status = ''NEW''
where wd_order not in (' || p_list || ')';
execute immediate sql_stmt;
END;
HTH
"GC" <glchy_at_email.com> wrote in message
news:be75cede.0109051430.462cb525_at_posting.google.com...
> Hi,
>
> im passing a variable (in PERL) containing a list of values to a
> PL/SQL stored procedure (SP). The SP is supposed to get that variable
> and 'decompose' it into a list of numeric values.
>
> PERL
> ====
> $list = "1,2,3";
>
>
> PL/SQL
> ======
> PROCEDURE upd_details(p_list varchar2) IS
> BEGIN
>
> update cm_details
> set wd_status = 'NEW'
> where wd_order not in (to_number(p_list));
>
> -- wd_order is NUM, p_list is supposed to be all the numbers except
> 1,2,3
>
> END upd_details;
>
> I have no problem calling & executing the SP from PERL. The problem is
> that the SP cannot convert "1,2,3" into numeric 1,2,3.
>
> Can anyone suggest how to make a SP convert a string variable into a
> list of numeric values?
>
> Thanks,
>
> GC
>
Received on Wed Sep 05 2001 - 19:04:05 CDT
