Home » SQL & PL/SQL » SQL & PL/SQL » Passing list of values to Stored Procedure
Passing list of values to Stored Procedure [message #255044] Mon, 30 July 2007 08:44 Go to next message
spooja
Messages: 25
Registered: May 2007
Junior Member
Can somebody please tell me how to pass comma seperated values through a parameter to a stored procedure? ie., something like if a user wants to check all salary details of some employee numbers then he will pass parameter like (111,112,113) then he has to get corresponding salaries of emp nos 111, 112 and 113, that means inside the script we have to split the param into seperate values but I am not getting any idea on it. So somebody please help me with it if you can provide me an example that would be really great..

[Updated on: Mon, 30 July 2007 08:51] by Moderator

Report message to a moderator

Re: Passing list of values to Stored Procedure [message #255052 is a reply to message #255044] Mon, 30 July 2007 09:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> var mylist varchar2(100)
SQL> exec :mylist := '7369,7788,7900';

PL/SQL procedure successfully completed.

SQL> with list as (
  2    select substr(:mylist,
  3                  instr(','||:mylist||',', ',', 1, rn),
  4                  instr(','||:mylist||',', ',', 1, rn+1)
  5                  - instr(','||:mylist||',', ',', 1, rn) - 1) value
  6    from (select rownum rn from dual 
  7          connect by level 
  8                     <= length(:mylist)-length(replace(:mylist,',',''))+1)
  9    )
 10  select empno, sal
 11  from emp
 12  where empno IN ( select value from list )
 13  order by empno
 14  /
     EMPNO        SAL
---------- ----------
      7369        800
      7788       3000
      7900        950

3 rows selected.

Regards
Michel
Re: Passing list of values to Stored Procedure [message #255058 is a reply to message #255044] Mon, 30 July 2007 09:20 Go to previous messageGo to next message
MarcL
Messages: 455
Registered: November 2006
Location: Connecticut, USA
Senior Member
create or replace function 
parse_string(vi_string varchar2,
                                             vi_parse_size            number)
return parsed_string
PIPELINED
as
--
--
v_string_size         number;
v_buf                 varchar2(4000);
v_i                   number;
--
begin
         if vi_string is not null then
                v_string_size:=length(vi_string);
                v_i:=1;
                while v_i < v_string_size
                loop
                    v_buf:=substr(vi_string,v_i,vi_parse_size);
                    pipe row(v_buf);
                    v_i:=v_i+vi_parse_size;
                end loop;
         end if;
         return;
end;
 

[Updated on: Mon, 30 July 2007 09:20]

Report message to a moderator

Re: Passing list of values to Stored Procedure [message #255105 is a reply to message #255058] Mon, 30 July 2007 11:24 Go to previous message
spooja
Messages: 25
Registered: May 2007
Junior Member
Thanks to both of you, it is working for me...
Previous Topic: connection problem
Next Topic: Writing output from variables in pl/sql to unix shell script
Goto Forum:
  


Current Time: Sat Dec 10 05:06:59 CST 2016

Total time taken to generate the page: 0.09532 seconds