Re: Which has the lowest overhead?

Jack Addington wrote:
> 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

6 of one ... half-a-dozen of the other. Do what works best for you.

BTW: Any single OUT parameter procedure can be wrapped as a function and any function wrapped as a procedure.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)