CODEWITHSUNDEEP
oracle-databaseplsqlplsqldeveloper
Gudala Sandeep
Gudala Sandeep

Reputation: 19

How to pass multiple values for a parameter in a PIPELINED table function in oracle

I have created a function in Oracle with 2 parameters. So when I run the query I want to pass multiple values to each parameter. I tried using below changes in the query:

coul_1 in ('||par1||') and colu_2 in ('||par2||')

But it is not fetching the data.

How to fetch the data when I give multiple values to different declared parameters.Eg:

select * from table(fun_name('val1','val2'))

val1 will have a1,a2,a3
val2 will have b1,b2,b3

Here is the function code:

CREATE OR REPLACE FUNCTION JOBRUN_STATUS_MONITOR_F(
    own_name IN VARCHAR2,
    status   IN VARCHAR2)
  RETURN JOBRUN_STATUS_EDW_1 PIPELINED
IS
  L_TAB JOBRUN_STATUS_MONITOR_EDW_1;
  JR_STATUS NUMBER (38);
  CURSOR jobrun_1_cr (OW_N VARCHAR2, STS VARCHAR2)
  IS
    SELECT *
    FROM JOBRUN A,
      JOBMST B,
      owner C
    WHERE A.JOBMST_ID   = B.JOBMST_ID
    AND C.OWNER_NAME    = OW_N
    AND A.JOBRUN_STATUS = STS ;
 BEGIN V_OWN_NAME := own_name;
  V_STATUS             := status;
  IF jobrun_1_cr%ISOPEN THEN
    CLOSE jobrun_1_cr;
  END IF;
  OPEN jobrun_1_cr (own_name, JR_STATUS);
  CLOSE jobrun_1_cr;
END JOBRUN_STATUS_MONITOR_F; 
/

Upvotes: 0

Views: 2664

Answers (2)

William Robertson
William Robertson

Reputation: 16001

It sounds like you want to call the function three times, passing (a1, b1), (a2, b2) and (a3, b3). One way would be to generate an inline view containing the values you want to pass, and query it including a call to your function.

Demo pipelined function:

create or replace function demo_pipefunc
    ( p_own_name in varchar2
    , p_status   in varchar2 )
    return sys.dbms_debug_vc2coll
    pipelined
as
    l_result long;
begin
    for i in 1..3 loop
        l_result := p_own_name ||';'|| p_status ||';'|| i;
        pipe row (l_result);
    end loop;

    return;
end demo_pipefunc;

Demo call:

with params (own_name, status) as
    ( select 'a1', 'b1' from dual union all
      select 'a2', 'b2' from dual union all
      select 'a3', 'b3' from dual
    )
select t.*
from   params
       cross join table(demo_pipefunc(own_name, status)) t

Output:

COLUMN_VALUE
a1;b1;1
a1;b1;2
a1;b1;3
a2;b2;1
a2;b2;2
a2;b2;3
a3;b3;1
a3;b3;2
a3;b3;3

Upvotes: 1

Cyrille MODIANO
Cyrille MODIANO

Reputation: 2376

try this:

CREATE OR REPLACE FUNCTION JOBRUN_STATUS_MONITOR_F (own_name IN VARCHAR2, status IN VARCHAR2) RETURN JOBRUN_STATUS_EDW_1 PIPELINED
IS L_TAB JOBRUN_STATUS_MONITOR_EDW_1;
JR_STATUS NUMBER (38); 
CURSOR jobrun_1_cr (OW_N VARCHAR2, STS VARCHAR2) 
IS SELECT * 
    FROM JOBRUN A, JOBMST B, owner C 
    WHERE A.JOBMST_ID = B.JOBMST_ID 
    AND C.OWNER_NAME = OW_N 
    AND A.JOBRUN_STATUS = STS;

BEGIN 
    V_OWN_NAME := REPLACE(own_name,'"',''''); 
    V_STATUS := REPLACE(status,'"','''');
    IF jobrun_1_cr%ISOPEN THEN 
        CLOSE jobrun_1_cr; 
    END IF; 

    OPEN jobrun_1_cr (own_name, JR_STATUS);
    CLOSE jobrun_1_cr; 

END JOBRUN_STATUS_MONITOR_F;

then call the procedure like this:

select * from table(fun_name('"a1","b1","c1"','"a1","b1","c1"'));

Your procedure may have other issues with the use of undeclared variables.

Upvotes: 0

Related Questions