I create a simplified function using SQLDev as example which return table recordset. I created collection of these records where I add one or more records using below code:
Create Or Replace Type t_Type_Emp Is Object(
Id Number,
Name VarChar2(25),
Job VarChar2(25)
);
/
Create Or Replace Type t_Table_Emp As Table Of t_Type_Emp;
/
Create Or Replace Function Return_Emp Return t_Table_Emp
Is
v_Table_Emp t_Table_Emp;
Begin
-- Add 1st records
Select t_Type_Emp(EmpNo, EName, Job)
Bulk Collect Into v_Table_Emp
From Emp
Where DeptNo = 10;
-- ... Adding other necessary records
-- Add n-th record
Select t_Type_Emp(EmpNo, EName, Job)
Bulk Collect Into v_Table_Emp
From Emp
Where EName = 'FORD';
-- Return all added records
Return v_Table_Emp;
End;
/
Select *
From Table(Return_Emp)
/
Regarding to above code I have two questions:
1. How can I return all this records together by modifying above function? For example above code (calling the function by Select * From Table(Return_Emp);) return only resultset of 2nd select Into statement:
But I added before that also 3 others, which was overwriten. Correct result should return:
How can I retain previously added records and return all "merged" records as shown above?
2. I plan to use such function (similar logic) as input query for a interactive report in APEX which can return also for 50+ pages of records. I don't want to create some temporary tables for such a purpose as a report source, but just fetch these records from memory structure like I present above. Is there any better and more efficient (faster) way to achieve the same result? I have to use logic similar to above, because otherwise I cannot get records I need. Filling some temp table is an option but I would have to empty it, before each report call. I think that filling report from collection should be more efficient. What's would be your recommendation in such case?
Thanks,
BB