How to sort plsql collection on the basis of one column

Question

My nested collection type is

TYPE Ty_Utils IS RECORD(
    Utils_Master  Ty_Util_Master,
    Utils_Details Ty_Tb_Utils_Details);

So, one one case I am getting the collection with values as in a loop

Ty_Utils.utils_details(k).seq_no:=2
Ty_Utils.utils_details(k).seq_no:=3
Ty_Utils.utils_details(k).seq_no:=1

I have to sort the all the values of above collection, i.e. Ty_Utils.utils_details according to seq_no in ascending order. Some of the seq_no will be null. I should keep those to the last.

Please help

Answer 1

It really depends how your referenced types are created. If Ty_Util_Master and Ty_Tb_Utils_Details are SQL types then you can do this.

You haven't provide any details for most of your schema, so all the objects in this demo are guesswork. Referenced types:

create or replace type Ty_Util_Master as object (
    id number,
    txn_date date
);
/
create or replace type Ty_Utils_Detail as object (
    seq_no number,
    amt number,
    txn_type varchar2(3)
);
/
create or replace type Ty_Tb_Utils_Details as table of Ty_Utils_Detail;
/

Sample package:

create or replace package tst as

    type  pl_Ty_Util is record (
        header  Ty_Util_Master
       , details Ty_Tb_Utils_Details
    );
    procedure process_utils  ( p_util in pl_Ty_Util);
end tst;    
/

create or replace package  body tst as

    procedure process_utils 
        ( p_util in pl_Ty_Util)
    is
    begin
        dbms_output.put_line('id = '||p_util.header.id);
        for idx in ( select t.* 
                     from table(p_util.details) t
                     order by t.seq_no nulls last)
        loop
             dbms_output.put_line('#'||idx.seq_no ||' '|| idx.amt ||' '|| idx.txn_type );
        end loop;
    end process_utils;
end tst;    
/

Test harness:

declare
    l_utils  tst.pl_Ty_Util;
begin
    l_utils :=  tst.pl_Ty_Util(Ty_Util_Master(42, sysdate)
                        , Ty_Tb_Utils_Details(
                             Ty_Utils_Detail(3, 3000, 'D')
                             , Ty_Utils_Detail(null, 275, 'C')
                             , Ty_Utils_Detail(1, 5000, 'C')
                             , Ty_Utils_Detail(4, 150, 'D')
                             , Ty_Utils_Detail(2, 2000, 'C')
                             )
                        );
     tst.process_utils(l_utils);       
end;
/

If you want to test it here is a LiveSQL demo (free Oracle TechNet account required).