Select data from rows into collection of oracle udt objects

Question

I have a normal table called test, it has a bunch of columns.

Then I have a UDT whose rows correspond to those columns.

what i would like to do is create a procedure which fetches the data from the table, then for each row creates a new instance of that UDT with that data, and then returns a list of those udt's.

I have searched far and wide but i'm an absolute beginner to all of this and i havent found an answer.

Can anyone point me in the right direction?

Answer 1

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE Test ( A, B, C, D, E ) AS
SELECT LEVEL, LEVEL * 500, SQRT( LEVEL ), CHR( 64 + LEVEL ), RPAD( CHR( 64 + LEVEL ), 8, CHR( 64 + LEVEL ) )
FROM DUAL
CONNECT BY LEVEL <= 26
/

CREATE TYPE Test_Record AS OBJECT (
  A NUMBER,
  B NUMBER,
  C NUMBER,
  D CHAR(1),
  E CHAR(8)
)
/

CREATE TYPE Test_Record_Table AS TABLE OF Test_Record
/

CREATE PROCEDURE get_Table_Of_Test_Records (
  p_records OUT Test_Record_Table
)
IS
BEGIN
  SELECT Test_Record( A, B, C, D, E )
  BULK COLLECT INTO p_records
  FROM   Test;
END get_Table_Of_Test_Records;
/

Query 1:

DECLARE
  trt Test_Record_Table;
BEGIN
  get_Table_Of_Test_Records( trt );

  -- Do something with the collection.
END;