Integrating a table function with select query in oracle

Question

I have created a function in oracle that accepts a parameter and would return set of records (defined a custom data type in oracle that returns a table). The function is working as expected. My question is how do i integrate the same with the existing select query that I have.

Below are the details of the code.

CREATE OR REPLACE TYPE EmpDetails AS OBJECT 
(
EMP_ID NUMBER,
EMP_SUMMARY VARCHAR2(256),
EMP_LOCATION VARCHAR2(20)
);

CREATE OR REPLACE TYPE EmpAttributes IS TABLE OF EmpDetails;

create or replace FUNCTION sf_GetEmpDetails 
(
 p_EmpID IN NUMBER 
) RETURN EmpAttributes
AS 

v_EmpID NUMBER;
v_EmpSummary VARCHAR2(256);
v_EmpLocation VARCHAR2(20);

table_results EmpAttributes := EmpAttributes();

BEGIN

    table_results := EmpAttributes();
    table_results.EXTEND;

    SELECT T1.BUG_ID,T2.SUMMARY,T1.LOCATION_NAME INTO v_EmpID,v_EmpSummary,v_EmpLocation FROM     EMP_TABLE    T1,EMP_ADDITIONAL_INFO T2 WHERE T1.EMP_ID = T2.EMP_ID AND T1.EMP_ID = p_EmpID;

    table_results(table_results.LAST) := EmpDetails(v_EmpID,v_EmpSummary,v_EmpLocation);

    RETURN table_results;

END;

This is working fine when I call this like mentioned below.

select * from table(sf_GetEmpDetails(100));

Now the same has to be integrated with some query that I have. Like the one mentioned below

SELECT T1.EMP_ID, T1.C1, .. .. T2.C1, ... Have to call the function here by passing the emp id selected above in the query

from t1,t2 where t1.id = t2.id and other conditions..

Please advice how to get this working.

Thanks for your help in advance.

I have removed the unnecessary tags

Popeye
– Popeye

2021-02-03 05:39:43 +00:00
Commented Feb 3, 2021 at 5:39 — Popeye
– Popeye, Commented Feb 3, 2021 at 5:39

Jon Heller · Accepted Answer · 2021-02-03 06:01:16Z

Add an inline view and a CROSS JOIN to pass values into the function, get the results, and convert the results from a collection back into a row. For example:

select emp_id, emp_summary, emp_location
from
(
    select sf_getEmpDetails(id) details from
    (
        select 100 id from dual union all
        select 101 id from dual union all
        select 102 id from dual
    )
)
cross join (table(details));

Also, you probably want to add BULK COLLECT to your table function, in order to handle multiple rows:

create or replace FUNCTION sf_GetEmpDetails 
(
 p_EmpID IN NUMBER 
) RETURN EmpAttributes
AS 
  table_results EmpAttributes := EmpAttributes();
BEGIN
  SELECT EmpDetails(T1.BUG_ID,T2.SUMMARY,T1.LOCATION_NAME)
  BULK COLLECT INTO table_results
  FROM EMP_TABLE T1, EMP_ADDITIONAL_INFO T2
  WHERE T1.EMP_ID = T2.EMP_ID
    AND T1.EMP_ID = p_EmpID;

  RETURN table_results;
END;
/

Here's a db<>fiddle for a working example.

Collectives™ on Stack Overflow

Integrating a table function with select query in oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related