My goal is to take parameter and return a record set. I did this through function returning a user defined table type.
I create the record type and the table type of the record type. Use a function to return this table type(the function is in a package).
But my issue is the SQL returns records but the function is not. It seems table variable is not populating. What could be the cause?
1. The steps using function is:
a)
create or replace type EMPL_RECORD as object (
COMPANY VARCHAR2(3),
EMPLID VARCHAR2(11),
LAST_NAME VARCHAR2(30),
FIRST_NAME VARCHAR2(30),
MIDDLE_NAME VARCHAR2(30),
EMPL_STATUS VARCHAR2(1)
);
b) create or replace type EMPL_table as table of EMPL_RECORD;
c)
create or replace PACKAGE My_Package AS
Function EMPL_Rec(End_DT Varchar2) return EMPL_table;
END My_Package;
d)
create or replace PACKAGE BODY My_Package AS
Function EMPL_Rec(End_DT Varchar2)
return EMPL_table IS
v_ret EMPL_table;
BEGIN
select EMPL_RECORD(COMPANY,EMPLID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, EMPL_STATUS)
bulk collect into v_ret
from
(
select COMPANY
, EMPLID
, LAST_NAME
, FIRST_NAME
, MIDDLE_NAME
, EMPL_STATUS
from EMP p
where EFFDT = (select max(a.EFFDT) from EMP a
where a.EMPLID = p.EMPLID
and a.EFFDT <= to_date(End_DT, 'yyyy-mm-dd'))
);
return v_ret;
End EMPL_Rec;
END My_Package;
e) when I run following, No record is returned.
select * from table(My_Package.EMPL_Rec('20190620'))
2. When I ran the following SQL statements, I have records come back:
DEFINE End_DT = '20190620' (CHAR)
select COMPANY
, EMPLID
, LAST_NAME
, FIRST_NAME
, MIDDLE_NAME
, EMPL_STATUS
from EMP p
where EFFDT = (select max(a.EFFDT) from EMP a
where a.EMPLID = p.EMPLID
and a.EFFDT <= to_date(&&End_DT, 'yyyy-mm-dd'))