1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

pass multiple values as single input parameter into pipelined function

Discussion in 'SQL PL/SQL' started by ecivgamer, Dec 23, 2012.

  1. ecivgamer

    ecivgamer Active Member

    Messages:
    73
    Likes Received:
    0
    Trophy Points:
    130
    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":

    Code (Text):
    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?
     
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    626
    Likes Received:
    129
    Trophy Points:
    810
    Location:
    Russian Federation
    Hi,ecivgamer.
    One method is to use collections

    This example :
    Code (Text):

    declare
    type t_my_array is table of integer;
    v_my_array t_my_array;
    procedure test_array (p_my_array  in t_my_array)
      as
      begin
        for z in 1 .. p_my_array.count
          loop
            dbms_output.put_line('p_my_array['||z||']='||p_my_array(z));
          end loop;
      end;
      begin
          with data as (
              select 1 as t from dual union all
              select 2  from dual union all
              select 3  from dual union all
              select 4  from dual union all
              select 5  from dual
              )
          select t
          bulk collect into v_my_array
          from data where t in (2,3)  ;
          dbms_output.put_line('Create my collection of '||v_my_array.count||' of elements');
          test_array(v_my_array);        
      end;

     
    There are still transmission methods of multiple values as procedure or function parameter.
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    This can be using the sys refcursor also as given below.

    Code (Text):

    CREATE OR REPLACE
    TYPE  AS OBJECT
    (
      EMPNO   NUMBER (4),
      ENAME   VARCHAR2 (10),
      DEPTNO  NUMBER (2),
      SAL   NUMBER (7,2)
    )
    /

    CREATE OR REPLACE TYPE emptab
       AS TABLE OF empobj
    /  
       
       
    CREATE OR REPLACE  FUNCTION getempdtls( c_dept IN SYS_REFCURSOR )
    RETURN emptab PIPELINED IS
        TYPE emp_dept_tab IS TABLE OF NUMBER(3) INDEX BY PLS_INTEGER;
        emp_dept emp_dept_tab;
    BEGIN  
         FETCH c_dept BULK COLLECT INTO emp_dept;
         FOR i IN 1 .. emp_dept.COUNT
         LOOP
            FOR rec IN (select EMPNO, ENAME, DEPTNO, SAL from emp where DEPTNO = emp_dept(i))
            LOOP
                PIPE ROW (empobj(rec.EMPNO, rec.ENAME, rec.DEPTNO, rec.SAL ) );
            END LOOP;
         
         END LOOP;  
    END;
    /  


       select *
       from  TABLE(getempdtls(CURSOR(select 10 from dual)) );


       select *
       from  TABLE(getempdtls(CURSOR(select distinct DEPTNO from emp where DEPTNO in (10,20))) );