CODEWITHSUNDEEP
oracle-database
Anish Gopinath
Anish Gopinath

Reputation: 182

How to pass the value of the select statement where clause as a input to a function in oracle DB

First of all, thank you, stacker, for helping me a lot for most of the questions which I posted over here! I Need a help again.Kindly let me know in case of any further clarification needed from my end. Thanks in advance. The code goes,

created a pipelined query based on performance factor.

create package pkg_emp
as
  type emprec is record
  ( id int
  , name varchar2(10)
  );

  type tb_emp IS   TABLE OF emprec;    
  function f_emp(id number) return tb_emp PIPELINED;
end pkg_emp;
/

Package created.

 create package body pkg_emp
 as
   function f_emp(p_id) return tb_emp PIPELINED
   is
     out_rec emprec;
   begin
    for i in (select id,name from emp_records where id =p_id) loop
        out_rec.id := i.id;
        out_rec.name := i.name;
        pipe_row(out_rec);
    end loop; 
    return;
   end f_emp;
 end mypkg;
 /

now, when i select the query using the table function as below,

 select * from table(pkg_emp.f_emp(100));

the record gets displayed perfectly

 id         name
 -----      ------
 100        marc
 100        paul
 100        sam
 100        stacker

Now, the real problem is how to pass the value dynamically using the where clause.

some thing like this,

 select * from table(pkg_emp.f_emp(%id)) where id=30;

Note: I have mentioned the % just for understanding only .Like to represent % as to substitute for the value of 30 as input to a function.

Upvotes: 1

Views: 453

Answers (1)

krokodilko
krokodilko

Reputation: 36087

Try a subquery factoring (WITH) clause:

WITH myId AS (
  SELECT 30 As id FROM dual
)
select * from table(pkg_emp.f_emp(myId.id)) where id=myId.id;

A subquery and a cross apply can be used too (on Oracle 12c):

select x.* 
from (
    SELECT 30 As id FROM dual
) MyId
CROSS APPLY( 
   table(pkg_emp.f_emp(myId.id)) where id=myId.id
) x

Upvotes: 1

Related Questions