Home » SQL & PL/SQL » SQL & PL/SQL » pass multiple values as single input parameter into pipelined function (Oracle 11g)
icon5.gif  pass multiple values as single input parameter into pipelined function [message #573186] Sun, 23 December 2012 14:03 Go to next message
ecivgamer
Messages: 105
Registered: May 2011
Senior Member
Hi all,

My need is to pass multiple values as single input parameter into pipelined function.

For example - "2" and "3" are values of input parameter "t":

with data as (
select 1 as t from dual union all
select 2 as t from dual union all
select 3 as t from dual union all
select 4 as t from dual union all
select 5 as t from dual 
)
select * from data where t in (2,3)


Is it possible at all?
Re: pass multiple values as single input parameter into pipelined function [message #573187 is a reply to message #573186] Sun, 23 December 2012 14:16 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
Hello,


It depends on what you want to do and how you implement your function.
For example you can put the set of the specific values stored in a nested table or varray and passed them as a parameter to your function.

You can read about collection types Here

Something like this:

CREATE TYPE numbers_nestab_ty AS TABLE OF NUMBER(5);
/

CREATE OR REPLACE FUNCTION myfunction(param_values IN numbers_nestab_ty)
RETURN ... PIPELINED AS ...
    l_indx PLS_INTEGER := 0;
BEGIN
    l_indx := param_values.FIRST;
    WHILE l_indx IS NOT NULL
    LOOP
        ...
        l_indx := param_values.NEXT(l_indx);
    END LOOP;
    ...
    RETURN ...
END myfunction;
/



Regards,
Dariyoosh

[Updated on: Sun, 23 December 2012 14:26]

Report message to a moderator

Re: pass multiple values as single input parameter into pipelined function [message #573199 is a reply to message #573186] Mon, 24 December 2012 02:18 Go to previous message
Michel Cadot
Messages: 58612
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If the list value is passed as a string, you can put n your pipelined function something like:
SQL> var mylist varchar2(100)
SQL> exec :mylist := '5,11,13,22,23,31,44,45'
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 id, valeur
 11  from t
 12  where id IN ( select value from list )
 13  order by id
 14  /
        ID USERNAME
---------- ------------------------------
         5 SYSTEM
        11 OUTLN
        22 MICHEL
        23 OPS$MCADOT101205
        31 SCOTT

5 rows selected.

Regards
Michel
Previous Topic: Oracle database to Mysql Conversion
Next Topic: Inner Join Record Count
Goto Forum:
  


Current Time: Tue Jul 29 20:03:04 CDT 2014

Total time taken to generate the page: 0.09270 seconds