Re: PL/SQL questions for experiece developper on a simple problem with tables...

[Quoted] smenard_at_avcorp.com writes:

> My Problem...
>
> i have a type...
>
> TYPE qty IS RECORD
> {
> date_rec date,
> ctrl item_specification.ctrl_spec_id%type,
> type_ent varchar2(3),
> ref_ent varchar2(30),
> ref_item varchar2(30),
> qty_ent number
> }
>
> TYPE qty_table is table of qty index by binary_integer;
>
> i have 4 procedure that return a list of TYPE qty_table....
>
> builpolist(part number,list qty_table );
> buildsolist(part number,list2 qty_table );
> buildwolist(part number, list3 qty_table);
> buildrelist(part number, list4 qty_table);
>
> i want to take the 4 list to create a list list5 sort by date
> (list5(i).date_rec....)
>
> what is the best way to do that?
> sql/table! temporary table? collection?
>
My 2 cents worth (not necessarily experienced plsql developer - bit [Quoted] over 6 months)

How you would do this really depends on some info not provided in the post - how many records are we talking about, how much additional processing is going to be done on the 5th list or is it just for reporting/display etc, how often will this be run, are the sub-lists already in any sort order etc.

If its only a relatively small number of records, I would be tempted just to write a sorting function which takes the 4 tables as parameters and returns a 5th sorted table. I'd only consider using an sql table (temporary or otherwise) if you had a really large number of records and the data was going to be accessed multiple times. I think its very rare you need to use temporary tables, but if you do use them, use "TEMPORARY GLOBAL TABLE" if your oracle version supports it or at least make sure your temporary table is in a temporary table space.

Another alternative might be to modify your four procedures and make the qty_table an IN OUT parameter and sort the data as you add it to the table and use the same table in the call to all four procedures - though this may result in a lot of element shifting, depending on your data and how "sorted" it is when you add it to the table etc.

Tim

--