#type body

I wanted to both store an array of objects in a table and return it via a pipelined function in oracle. I was previously returning a type from that function but it was based off a table of scalars (varchar2). I tried simply storing my values as a comma delimited string and splitting them apart when I needed them but this was slow and ugly (millions of rows). Tables with object types seemed like a nice semi-native option. It turned out to be significantly faster too!

After a few days of playing with these I've managed to:

-Create a table using an object type as a column -Create a type that is a table of the new object type -Return that a bunch of scalars as the table type via a pipeline and cast back to a table -Select distinct against the type.

So what did it take? Define my base type:

create or replace  TYPE row_set_type AS object (identifier1 VARCHAR2 (100) , identifier2 VARCHAR2 (100), identifier3 VARCHAR2 (100), map member function mapping_function return varchar2); /

create or replace  TYPE BODY row_set_type as map member function mapping_function return varchar2 is     begin        --This is used to identify an object as unique. Without this a distinct can't be performed on an object.         --This implements the map function expected by the distinct or order commands.         return to_char(identifier1 || ',' || identifier2 || ',' || identifier3);     end; end;

Define the table type: create or replace type row_table_type as table of row_set_type;

Build a function which returns that table type as pipelined.

Realize that you oracle doesn't treat your table type as a scalar. When you cast from that using table(yourfunctionhere). The built in column_name pseudonym column is not available. If you select * from this you will see it actually dereferences the table_type and returns the fields from your base row_set_type. This was unexpected. I built another function that returned the attr_name ordered by attr_no from the system table. This lets me build a list of fields within my set type dynamically and place the identifier fields back into a row_set_type object. I can then insert this row_set_type object into my table.

Confusing right? I thought so. This will get you information about your type    select  attr_name     from    all_type_attrs     where   type_name = 'TYPENAMEHERE'     order   by attr_no;