Re: PL/SQL questions for experiece developper on a simple problem with tables...
From: <smenard_at_avcorp.com>
Date: Mon, 22 Jul 2002 10:59:06 -0400
Message-ID: <20020722.105900.76065818.25079_at_avcorp.com>
> My 2 cents worth (not necessarily experienced plsql developer - bit 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
> Received on Mon Jul 22 2002 - 16:59:06 CEST
Date: Mon, 22 Jul 2002 10:59:06 -0400
Message-ID: <20020722.105900.76065818.25079_at_avcorp.com>
ths that help me ...
In article <87adok8viu.fsf_at_blind-bat.une.edu.au>, "Tim Cross"
<tcross_at_nospam.une.edu.au> wrote:
> 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 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
> Received on Mon Jul 22 2002 - 16:59:06 CEST