Which has the lowest overhead?

given the following:

type uo_data object as (some_key number, some_more_data ...); type t_uo_data as table of uo_data;

function somePipeFunc ( parms ...) return pipelined t_uo_data{...};

I have a function that takes the piped data and returns a non-piped version of it along with a table of just the keys. I need both the piped_data and a collection of the keys to do some bulk insert processing. I have a bulk insert that does a hierarchial query based on the some_key column but I cannot use the t_uo_data because I get a PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND table of records.

Thus I need to populate a separate table/collection with just the some_key column.

I am getting my t_uo_data from the piped function with the following sql:

select uo_data_object(...)
bulk collect into lt_uo_data
from table(cast(pipeFunc( ... ) as t_uo_data));

I see two options for extracting my set of keys

1. extend the sql above to parse out the keys during the bulk load

    select some_key, uo_data_object( some_key, ...)     bulk collect into lt_key , lt_uo_data     from table(cast(pipeFunc( ... ) as t_uo_data));

or 2) do an in-memory parse of my uo_data table and pull out the data.

for all idx in lt_uo_data.first .. lt_uo_data.last loop

    lt_key.extend;
    lt_key.(lt_key.count) := lt_uo_data(idx).some_key. end loop;

Is there any significant difference? The size of the table varies from a few dozen to a few thousand rows. The downside of the first approach is that I have to change the function to a procedure and I lose some generic functionality. For the 2nd I'm not sure what the impact is? As I type this long post I have the feeling both are probably much the same impact but the 2nd offers more flexibility for me.

thx

jack Received on Wed Nov 09 2005 - 22:39:45 CST