Create a Pipelined table function in oracle. I am tired of it. Remember it and save the trouble.
1. Create a type first:
Create or replace type smpl_metadata AS OBJECT
(
Smpl varchar2 (2000 ),
Lauren varchar2 (2000 ),
Grade varchar2 (2000)
);
2. Create a "Table type" with the new type ":
Create or replace type smpl_metadata_table as table of smpl_metadata
3. Then you can use this "Table type" to create a "table function:
Create or replace function query_smpl_metadata
RETURN smpl_metadata_table PIPELINED
IS
V_smpl_metadata smpl_metadata;
BEGIN
FOR v_count IN 1 .. 20
LOOP
V_smpl_metadata: = smpl_metadata ('s '| v_count, 'A',' B ');
Pipe row (v_smpl_metadata );
End loop;
RETURN;
END query_smpl_metadata;
4. A more complex one with parameters:
Create or replace function query_smpl_metadata_detail (smpl_match varchar2)
RETURN smpl_metadata_table PIPELINED
IS
V_smpl_metadata smpl_metadata;
BEGIN
For x in
(
Select * from
(
Select 'A' smpl, 'B' lauren, 'C' grade from dual
)
Where smpl = smpl_match
)
Loop
V_smpl_metadata: = smpl_metadata (x. smpl, x. lauren, x. grade );
Pipe row (v_smpl_metadata );
End loop;
Return;
END query_smpl_metadata_detail;