technology.amis.nl Open in urlscan Pro
178.20.253.78  Public Scan

Form analysis
1 forms found in the DOM

GET https://technology.amis.nl

<form method="get" id="searchform" class="search-form ajax-search" action="https://technology.amis.nl" _lpchecked="1">
  <fieldset> <input type="text" name="s" id="s" value="" placeholder="Search the site" autocomplete="off"> <button id="search-image" class="sbutton" type="submit" value="Search">Search</button></fieldset>
  <div class="ajax-search-results-container" style="display: none;"></div>
</form>

Text Content

 * Careers @ AMIS
 * About
 * News
 * Contact







Menu
 * Internet Of Things
 * Platforms
 * Microsoft Azure
 * Oracle Cloud
 * Search
   

 * Careers @ AMIS
 * About
 * News
 * Contact




Home » Database


SORTING PL/SQL COLLECTIONS, THE QUITE SIMPLE WAY (PART TWO: HAVE THE SQL ENGINE
DO THE HEAVY LIFTING)

Lucas Jellema May 31, 2006 Database, Oracle, PL/SQL 10 Comments

Yesterday I published an article on this blog describing an approach to sorting
the elements in PL/SQL Collections:Sorting PL/SQL Collections, the hard way, the
intermediate way and the quite simple way (part one) . This approach makes use
of the property of PL/SQL Associative Arrays that the keys are always kept in
sorted order. We do not have to do anything, just stuff the things we want to
have sorted into an Associative Array and when done, retrieve them again. They
will reappear properly sorted! With some additional logic, we can use this
mechanism to quickly sort large collections of complex records.

However, there are a few downsides to this approach: it will only sort on
VARCHAR2 and NUMBER fields – and the alphabetical sort may not be the one we
require. More complex sorting rules are not supported. Besides, the code may not
be as clear and elegant as we would like. So far I have not done any performance
tests – that would be the next step.

But first I want to demonstrate another way of sorting PL/SQL Collections. We
will make use of the TABLE, MULTISET and CAST operators to out-source the heavy
lifting to the SQL Engine! This means we have the full power of SQL at our
disposal for manipulating the PL/SQL collection. Note: not just for sorting,
also for filtering and aggregating! There is one important requirement: the
PL/SQL Collection must be based on a type that is defined in the database!

 

Let’s start with a very simple example:

create type num_tbl is table of number
/

declare
   l_num_tbl num_tbl:= num_tbl(6,12,9,1,54,21, 11, 2);


We have defined a type num_tbl and now we have an unsorted collection of that
type. Our first objective: get the elements of this collection in a properly
sorted order. Let’s see how simple that is:

 begin
   select cast ( multiset( select *
                          from table( l_num_tbl )
                          order by 1
                         ) as num_tbl)
   into l_num_tbl
   from dual;


Using the table operator we pretend that l_num_tbl is a table-like-recordset. We
can select records from this table-like-structure in an sequence determined by a
normal SQL ORDER BY clause. The result of that query is turned into a collection
again, using MULTISET, and subsequently casted into the desired collection type,
namely NUM_TABLE. The result is selected into the variable l_num_tbl that is the
same one as we started with.

Reporting the values in the l_num_tbl collection at this point:

   l_idx:= l_num_tbl.first;
   loop
     dbms_output.put_line(l_num_tbl(l_idx));
     l_idx:= l_num_tbl.next(l_idx);
     exit when l_idx is null;
   end loop;
end;
/
1
2
6
9
11
12
21
54

PL/SQL procedure successfully completed.


And we have done it! That’s even less code than in the comparable example in
Sorting PL/SQL Collections, the hard way, the intermediate way and the quite
simple way (part one). Note that this exchange with the SQL Engine is only
possible because l_num_tbl is based on a type that is created in the database
and not defined in PL/SQL.


SORTING COLLECTION OF MORE COMPLEX RECORDS

What we have seen so far was relatively trivial. Let’s now look at a collection
of more complex structures that we may want to sort in more advanced ways.
Again, the SQL engine can only sort collections of types that are defined in the
database. We define two helper types in the database:

create type sortable
is object
( value varchar2(50)
, pointer number
)
/
create type sortable_table
is table of sortable
/


These types are later used to perform the sort on the field in the records that
we want to have sorted.

Now let’s meet the complex record definition and the collection we want to sort:

declare
    type complex_type is record ( last_name varchar2(50)
                                , street varchar2(50)
                                , city varchar2(50)
                                , birthdate date
                                , zipcode varchar2(15)
                                , email_address varchar2(50)
                                , many_more_elements varchar2(4000)
                                );
    type complex_type_table_type is table of complex_type;
    -- define and initialize a complex Nested Table Collection
    l_cpx_tbl complex_type_table_type:= complex_type_table_type();
    l_cpx_rec complex_type;



The records in our collection are created and added as follows:

  begin
    -- first populate the collection with a few values.
    l_cpx_rec.last_name:= 'Jellema';
    l_cpx_rec.street:= 'Rembrandtlaan';
    l_cpx_rec.email_address:= 'toby231@hitmail.com';
    l_cpx_tbl.extend;
    l_cpx_tbl(l_cpx_tbl.last):= l_cpx_rec;
    l_cpx_rec.last_name:= 'Abramovic';
    l_cpx_rec.street:= 'Chelsea Road';
    l_cpx_rec.email_address:= 'abra1@notfail.com';
    l_cpx_tbl.extend;
    l_cpx_tbl(l_cpx_tbl.last):= l_cpx_rec;
    l_cpx_rec.last_name:= 'Rooney';
    l_cpx_rec.street:= 'ManYou 32';
    l_cpx_rec.email_address:= 'wayne@noavail.com';
    l_cpx_tbl.extend;
    l_cpx_tbl(l_cpx_tbl.last):= l_cpx_rec;
    l_cpx_rec.last_name:= 'Basten';
    l_cpx_rec.street:= 'Russia Park 1988';
    l_cpx_rec.email_address:= 'bassie@oneteam.com';
    l_cpx_tbl.extend;
    l_cpx_tbl(l_cpx_tbl.last):= l_cpx_rec;



We want to sort the collection by the last_name field in the complex_type
records. Currently, the order is rather random – well, actually it is the order
in which the records were added to the collection:

    l_idx:= l_cpx_tbl.first;
    loop
      dbms_output.put_line('**     '||l_cpx_tbl(l_idx).last_name
                         ||' email: '||l_cpx_tbl(l_idx).email_address);
      l_idx:= l_cpx_tbl.next(l_idx);
      exit when l_idx is null;
    end loop;
**     Jellema email: toby231@hitmail.com
**     Abramovic email: abra1@notfail.com
**     Rooney email: wayne@noavail.com
**     Basten email: bassie@oneteam.com


The sort logic makes use of a helper collection that contains records with two
field: the value field on which the complex_type records should be sorted and a
pointer field that refers to a position in the l_cpx_tbl.

  declare
    l_sort_tbl sortable_table:= sortable_table();
    l_idx integer;
  begin
    l_idx:= l_cpx_tbl.first;
    -- PREPARE L_SORT_TBL

    -- loop over all elements in the l_cpx_tbl collection
    -- that we want to sort. Use the last_name for every
    -- element in l_cpx_tbl as a key for the l_sort_tbl. Associate
    -- the key with a current element in the l_cpx_tbl collection.
    loop
      l_sort_tbl.extend;
      -- note: the first field in the sortable object is the expression used for sorting!!!
      --       the second field is a pointer to the current position of the element in the l_cpx_tbl
      l_sort_tbl( l_sort_tbl.last):= new sortable( l_cpx_tbl(l_idx).last_name, l_idx);
      l_idx:= l_cpx_tbl.next(l_idx);
      exit when l_idx is null;
    end loop;


The sort is again done by the SQL Engine. The sort is on the value field and the
most important result is the pointers in the sorted order.

   -- SORT L_SORT_TBL
   -- using the table operator we pretend that l_sort_tbl is a table-like-recordset;
   -- we can select records from this table-like-structure in an sequence determined by a
   -- normal SQL ORDER BY clause
   -- the result of that query is turned into a collection again, using MULTISET, and casted
   -- into the desired collection type, namely SORTABLE_TABLE
   select cast ( multiset( select *
                           from table( l_sort_tbl )
                           order by 1 -- note: here we have a very simple sort-operation;
                                      -- it could be much more complex obviously
                         ) as sortable_table)
   into l_sort_tbl
   from dual;
   -- at this point, l_sort_tbl is sorted



 

The set of pointers started out as 1,2,3,4 and is now set to 2,4,1,3:

   -- The hard work was done by the SQL engine!!
   -- brief inspection of the sort helper table l_sort_tbl:
      dbms_output.put_line('Brief look at the contents of the helper table l_sort_tbl:');
      l_idx:= l_sort_tbl.first;
      loop
        dbms_output.put_line('sort index: '||l_idx
                           ||' and sort value: '||l_sort_tbl(l_idx).value
                           ||' and pointer: '||l_sort_tbl(l_idx).pointer);
        l_idx:= l_sort_tbl.next(l_idx);
        exit when l_idx is null;
      end loop;

Brief look at the contents of the helper table l_sort_tbl:
sort index: 1 and sort value: Abramovic and pointer: 2
sort index: 2 and sort value: Basten and pointer: 4
sort index: 3 and sort value: Jellema and pointer: 1
sort index: 4 and sort value: Rooney and pointer: 3


At this point we can recreate the l_cpx_tbl collection with the elements in the
proper order:

  declare
    l_ctr integer:=1;
    l_temp_cpx_tbl complex_type_table_type:= complex_type_table_type();
  begin
    l_idx:= l_sort_tbl.first;
    loop
      l_temp_cpx_tbl.extend;
      -- feed into the current slot the element that is at the pointer-indicated position in the original l_cpx_tbl
      l_temp_cpx_tbl(l_temp_cpx_tbl.last):= l_cpx_tbl(l_sort_tbl(l_idx).pointer);
      l_idx:= l_sort_tbl.next(l_idx);
      exit when l_idx is null;
    end loop;
    l_cpx_tbl.delete;
    l_cpx_tbl:= l_temp_cpx_tbl;
    -- if l_cpx_tbl was a large collection
    -- then the delete operation on the collection let go of a lot of memory
    -- that we should make available to the pool
    dbms_session.free_unused_user_memory;
    -- DONE! At this point, l_cpx_tbl is properly sorted
  end;


The result when printed to the output is what we were looking for:

    dbms_output.put_line('**********************************************************');
    dbms_output.put_line('We proudly present the sorted results: ');
    l_idx:= l_cpx_tbl.first;
    loop
      dbms_output.put_line('**     '||l_cpx_tbl(l_idx).last_name
                         ||' email: '||l_cpx_tbl(l_idx).email_address);
      l_idx:= l_cpx_tbl.next(l_idx);
      exit when l_idx is null;
    end loop;
 end;
 /
**********************************************************
We proudly present the sorted results:
**     Abramovic email: abra1@notfail.com
**     Basten email: bassie@oneteam.com
**     Jellema email: toby231@hitmail.com
**     Rooney email: wayne@noavail.com

PL/SQL procedure successfully completed.


And we see that the sort was successful.


MORE ADVANCED SQL OPERATIONS ON OUR COLLECTION

There is still quite a bit of PL/SQL code involved. Besides: we can still only
sort on a single expression. And: if we leverage the SQL engine, can’t we make
use of other SQL operations on our Collection? The answer is a resounding YES!

Let’s now look at a further integration with the SQL engine. Note: it is my
expectation that given the more massive  data exchange from PL/SQL to SQL and
back, this approach might have issues with performance and resource usage when
the number or size of collection elements increases. Again, we should
investigate this.

Since we want the SQL engine to have full access to the elements in the
collection, we have to base our work on database types:

create type complex_type
is object
( last_name varchar2(50)
, street varchar2(50)
, city varchar2(50)
, birthdate date
, zipcode varchar2(15)
, email_address varchar2(50)
, many_more_elements varchar2(4000)
)
/

create type complex_type_table_type
is table of complex_type
/


Now the code to initializes the collection is slightly changed. Note though how
similar the PL/SQL RECORD and SQL OBJECT types are!

declare
    -- define and initialize a complex Nested Table Collection
    l_cpx_tbl complex_type_table_type:= complex_type_table_type();
  begin
    -- first populate the collection with a few values.
    l_cpx_tbl.extend;
    l_cpx_tbl(l_cpx_tbl.last):= complex_type('Jellema','Rembrandtlaan', 'Enschede', sysdate - 36*365, '7545 ZJ','toby231@hitmail.com',null );
    l_cpx_tbl.extend;
    l_cpx_tbl(l_cpx_tbl.last):= complex_type('Abramovic','Chelsea Road', 'London', sysdate - 43*365, 'MSCW','abra1@notfail.com',null );
    l_cpx_tbl.extend;
    l_cpx_tbl(l_cpx_tbl.last):= complex_type('Rooney','ManYou 32', 'Manchester', sysdate - 23*362, '32121','wayne@noavail.com',null );
    l_cpx_tbl.extend;
    l_cpx_tbl(l_cpx_tbl.last):= complex_type('Basten','Russia Park 1988', 'Utrecht', sysdate - 39*365+43, '2006 WK','bassie@oneteam.com',null );


Now that we have our collection in a format understood by the SQL engine,
operating on it becomes a breeze. Let’s look at an example that just sorts:

declare
    ...
    l_idx integer;
    l_sort_idx varchar2(50);
    l_cpx_rec complex_type;
  begin
  ...
    -- have the SQL engine do the heavy lifting in terms of sorting the collection!
    select cast ( multiset( select complex_type
                                   ( last_name
                                   , street
                                   , city
                                   , birthdate
                                   , zipcode
                                   , email_address
                                   , many_more_elements )
                            from table( l_cpx_tbl )
                            order by last_name -- note: here we have a very simple sort-operation;
                                               -- it could be much more complex obviously
                        ) as complex_type_table_type)
    into l_cpx_tbl
    from dual;
    -- DONE! At this point, l_cpx_tbl is properly sorted


The results of this operation look like this:

    dbms_output.put_line('**********************************************************');
    dbms_output.put_line('We proudly present the sorted results: ');
    l_idx:= l_cpx_tbl.first;
    loop
      dbms_output.put_line('**     '||l_cpx_tbl(l_idx).last_name
                         ||' email: '||l_cpx_tbl(l_idx).email_address);
      l_idx:= l_cpx_tbl.next(l_idx);
      exit when l_idx is null;
    end loop;

  end;
/
**********************************************************
We proudly present the sorted results:
**     Abramovic email: abra1@notfail.com
**     Basten email: bassie@oneteam.com
**     Jellema email: toby231@hitmail.com
**     Rooney email: wayne@noavail.com

PL/SQL procedure successfully completed.


A more advanced SQL operation on the same collection is just as easy to
implement:

    -- have the SQL engine do the heavy lifting in terms of sorting the collection!
    select cast ( multiset(  select complex_type
                                    ( last_name
                                    , street
                                    , city
                                    , birthdate
                                    , zipcode
                                    , email_address
                                    , many_more_elements
                                      ||' '||runcount
                                    )
                             from   ( select ct.*
                                      ,      count(*) over
                                                      (order by last_name desc) runcount
                                      from   table( l_cpx_tbl ) ct
                                      where  city <> 'London'
                                      order
                                      by     city
                                      ,      length(last_name)
                                      ,      sysdate - birthdate
                                      ,      2
                                    )
                        ) as complex_type_table_type)
    into l_cpx_tbl
    from dual;
    -- DONE! At this point, l_cpx_tbl is properly sorted


and the results are:

    dbms_output.put_line('**********************************************************');
    dbms_output.put_line('We proudly present the sorted results: ');
    l_idx:= l_cpx_tbl.first;
    loop
      dbms_output.put_line('**     '||l_cpx_tbl(l_idx).last_name
                         ||' city: '||l_cpx_tbl(l_idx).city
                         ||' email: '||l_cpx_tbl(l_idx).email_address
                         ||' birthdate: '||l_cpx_tbl(l_idx).birthdate
                         ||' many_more_elements: '||l_cpx_tbl(l_idx).many_more_elements
                         );
      l_idx:= l_cpx_tbl.next(l_idx);
      exit when l_idx is null;
    end loop;
  end;
/
 **********************************************************
We proudly present the sorted results:
**     Jellema city: Enschede email: toby231@hitmail.com birthdate: 09-JUN-70 many_more_elements:  2
**     Rooney city: Manchester email: wayne@noavail.com birthdate: 14-AUG-83 many_more_elements:  1
**     Basten city: Utrecht email: bassie@oneteam.com birthdate: 23-JUL-67 many_more_elements:  3

PL/SQL procedure successfully completed.


 


RESOURCES

Download the source code for this article: plsqlCollectionSortSQLScripts.sql

 


SHARE THIS:

 * Print
 * 
 * Share
 * 
 * Reddit
 * Email
 * 


LIKE THIS:

Like Loading...
Tweet Share WhatsApp Telegram

RELATED POSTS


HOW TO BUILD AN ORACLE DATABASE APPLICATION (THAT SUPPORTS APEX, JAVA, REACT OR
ANY OTHER FRONT-END) (4)


IMPLEMENTING CELL HIGHLIGHTING IN ADF 11G RICHTABLES


TOUR DE FRANCE 2011 – ANALYSIS USING ADF DVT GRAPHS – PART 2 – GAP WITH CADEL
FOR TOP 5 THROUGHOUT THE STAGES

ABOUT THE AUTHOR

LUCAS JELLEMA

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director
and Oracle Developer Champion. Solution architect and developer on diverse areas
including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and
microservices, events in various shapes and forms and many other things. Author
of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on
user groups and community events and conferences such as JavaOne, Oracle Code,
CodeOne, NLJUG JFall and Oracle OpenWorld.

10 COMMENTS

 1.  Janos Ujvari September 30, 2011
     
     Great post
     thank you very much.
     
     
 2.  shab August 24, 2011
     
     Hi Lucas
     Great article and examples -thanks.
     One issue though, I got his working for my needs on a relatively small
     dataset but when I tried to scale it up to sort a pl/sql table of around 4
     million rows I received the following error:
     ORA-22813: operand value exceeds system limits
     This error arises on the following statement.
     select cast(multiset(select t_pair
     (actual,
     predicted,
     original_index)
     from table(l_tbl)
     order by predicted desc, original_index asc) as t_pairs)
     into l_tbl
     from dual;
     I know that you said that you hadn’t tested it with a large data set, I
     guess i just did.   Any suggestions on to sorting larger pl/sql tables?
     Thanks
     shab
     
     
 3.  Trout May 14, 2010
     
     Combining this with analytical functions is very useful.  I am using the
     technique in a modeling package that uses bulk inserts to speed the
     processing of large volumes of rows (35 million).  By using this method I
     can get the analytical function results without having to go to the DB a
     second time for updates after the inital inserts.
     
     
 4.  Trout April 28, 2010
     
     Thanks for this.  Saved me a fair bit of time.
     
     
 5.  Thaha October 12, 2009
     
     Wonderful example.. Thanks a lot …
     
     
 6.  Daniel October 24, 2008
     
     Thanks for your simple collection sorting method. I have been looking for a
     way to do this without relying on an index.
     
     It’s so hard to find clear solutions for what should be simple Oracle
     tasks. No wonder I usually do things like this on the Java application
     side.
     
     
 7.  M.Bilal February 27, 2007
     
     It is really great, and lot of thanks for this article.
     
     
 8.  Patrick Sinke August 27, 2006
     
     I also encountered a way to do it without the necessity to create a type
     outside the package (which is annoying):
     
     DECLARE
     TYPE typ_sorttabIS TABLE OF NUMBER(1) index by VARCHAR2(100) ;
     l_order_tab typ_sorttab;
     ln_pos NUMBER(8) ;
     lv_input VARCHAR2(100) ;
     lv_input_string varchar2(100) ;
     lb_doorgaan boolean default TRUE;
     lv_result VARCHAR2(100) := ‘;’; — string begint met een ‘;’
     BEGIN
     lv_input_string := ‘;BLA;DIE;BLOE;TEST;’
     — 1e gedeelte leest de inputstring in een array
     ln_pos := instr(lv_input_string, ‘;’);
     WHILE lb_doorgaan
     LOOP
     lv_input := substr(lv_input_string, 1, ln_pos-1) ; — alles tot
     eerstvolgende scheidingsteken
     IF lv_input is not null
     THEN
     — deze controle is nodig omdat de eerste keer een lege string zal opleveren
     l_order_tab(lv_input) := 1 ;
     END IF;
     lv_input_string := substr(lv_input_string, ln_pos + 1) ; — input_string min
     input
     ln_pos := instr(lv_input_string, ‘;’) ;– volgende scheidingsteken
     lb_doorgaan := ln_pos > 0 ; — scheidingstekens nog aanwezig
     END LOOP ;
     — 2e gedeelte voor output
     lv_input := l_order_tab.first ;
     WHILE lv_input IS NOT NULL
     LOOP
     lv_result := lv_result || lv_input || ‘;’ ;
     lv_input := l_order_tab.next(lv_input) ;
     END LOOP ;
     —
     end;
     
     The trick is too create a table of number with index on the field to be
     sorted. In this example the array is filled manually from a comma-separated
     string and sorted by looping through the array in order of index.
     
     
 9.  Kate Johnson August 24, 2006
     
     Thank you so much for this article, I have been hunting for how to properly
     use TABLE/CAST and now you made it even easier with TABLE/CAST/MULTISET.
     Thank you very much. 2 days of searching is now over.
     
     Next, INSERT/UPDATE/DELETE examples….
     
     
 10. Francois May 31, 2006
     
     Great !
     Another solution to sort a collection of records by using the . (dot)
     notation:
     
     create or replace TYPE typ_emp_rec AS OBJECT( empno NUMBER(4), ENAME
     VARCHAR(10) )
     /
     
     create or replace TYPE typ_tab_emp_rec AS TABLE OF typ_emp_rec
     /
     
     SQL> declare
     2 tremp typ_tab_emp_rec := typ_tab_emp_rec( null);
     3 tremp2 typ_tab_emp_rec := typ_tab_emp_rec( null);
     4 nb pls_integer := 0 ;
     5 begin
     6
     7 tremp.extend(5) ;
     8 for i In reverse 1..5 loop
     9 nb := nb + 1 ;
     10 tremp(nb) := new typ_emp_rec(i, ‘Name’ || to_char(i) ) ;
     11 end loop ;
     12
     13 — output the collection non-sorted —
     14 dbms_output.put_line( ‘*** non-sorted collection ***’ ) ;
     15 for i In tremp.first .. tremp.last loop
     16 dbms_output.put_line( tremp(i).ename ) ;
     17 end loop ;
     18
     19 select cast ( multiset( select t.*
     20 from table( tremp ) t
     21 order by t.ename
     22 ) as typ_tab_emp_rec)
     23 into tremp2
     24 from dual;
     25 — output the collection sorted on ename —
     26 dbms_output.put_line( ‘*** sorted collection (field 2) ***’ ) ;
     27 for i In tremp2.first .. tremp2.last loop
     28 dbms_output.put_line( tremp2(i).ename ) ;
     29 end loop ;
     30 end ;
     31
     32 /
     *** non-sorted collection ***
     Name5
     Name4
     Name3
     Name2
     Name1
     *** sorted collection (field 2) ***
     Name1
     Name2
     Name3
     Name4
     Name5
     
     Procédure PL/SQL terminée avec succès.
     
     


LUCAS JELLEMA

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director
and Oracle Developer Champion. Solution architect and developer on diverse areas
including SQL, JavaScript, Kubernetes & Docker, Machine Learning, Java, SOA and
microservices, events in various shapes and forms and many other things. Author
of the Oracle Press book Oracle SOA Suite 12c Handbook. Frequent presenter on
user groups and community events and conferences such as JavaOne, Oracle Code,
CodeOne, NLJUG JFall and Oracle OpenWorld.

View all posts


20 years AMIS Technology Blog

 * Java
 * Architecture
 * Big Data
 * Cloud
 * Continuous Delivery
 * Internet Of Things
 * Microsoft Azure
 * Platform Technology
 * Python


BEKIJK ONZE VACATURES


CONTACT US

AMIS Technology Blog | Oracle - Microsoft Azure Copyright © 2024.
 


%d