I would like to be able to run the following code to select records:
Desired Syntax
select * from table(my_new_package.get_data(123))
I have been successful in creating code that compiles, but I am unable to retrieve the data using the above syntax. Could anyone identify how I could update this to ensure the above query will run properly.
Here is the current code:
Package Spec
create or replace package my_new_package is
-- Types
-------------
type my_data_type is record (
myfield1 VARCHAR2(10)
,myfield2 VARCHAR2(10)
);
type my_data_type_TABLE is table of my_data_type index by binary_integer;
-- Functions
-------------
FUNCTION get_data(i_id_number varchar2) return my_data_type_table;
end my_new_package;
Package Body
create or replace package body my_new_package is
function get_data(i_id_number varchar2) return my_data_type_table IS
current_row my_data_type;
all_rows my_data_type_table;
n integer;
-- Will update the cursor to pull real data
cursor cur is
select '1' myfield1, '10' myfield2 from dual
union all
select '2' myfield1, '20' myfield2 from dual
;
BEGIN
n := 0;
for rec in cur LOOP
n := n+ 1;
current_row := rec;
-- do some things to current row
-- Add modified current row
all_rows(n) := current_row;
END LOOP;
return all_rows;
END;
end my_new_package;
The above code compiles, but I can't select from the data using the desired syntax.
Is there anyway to do this where the custom types are embeded at the package level only and not created at the database level?
This will be for Oracle 12 (and Oracle 19).
Best Answer
You need to define the function as pipelined. Below works in 19c.
Package header:
Package body:
Test: