Sometimes the best way to see the familiar is to go far from it
– Wisdom of the desert

In previous articles (collections I and collections II) I described the different types of collections, their possibilities and their limits. One of the limits was the possibility to use an Associative Array in a SQL statement. If you are building your code from scratch, it’s easy enough to make the right choice, but if you are working with existing code and you need to make adjustment and/or you are not allowed to create type objects in the database, and still want to use the SQL power the database offers, since version 12c you can now use Associative Arrays to solve your problem.

If you have a rather large associative array in your program and you want this sorted one way or another you have a couple of options.

  • Write your own sort method. This is a rather cumbersome and complex option and chances are you will not get this to work flawlessly in the first couple of tries.
  • Copy all the records to a new collection using the field you want to sort on as the index. A problem might be duplicate index values. Using this method you will loose any duplicates. Check the results of the following script, displaying what happens when you have duplicates:
    DECLARE
      TYPE emp_rt IS record
      (Empno emp.empno%type
      ,ename emp.ename%type
      ,sal emp.sal%type);
      TYPE EmpsByNumber_tt IS TABLE OF emp_rt INDEX BY pls_integer;
      TYPE EmpsByVarchar_tt IS TABLE OF emp_rt INDEX BY emp.ename%type;
    --
    
      l_Emps EmpsByNumber_tt;
      l_EmpsByEmpno EmpsByNumber_tt;
      l_EmpsByEname EmpsByVarchar_tt;
      l_EmpsBySal EmpsByNumber_tt;
      PROCEDURE pl(what_in IN VARCHAR2)
      IS
      BEGIN
        Dbms_output.put_line(what_in);
      END pl;
      PROCEDURE display(coll_in IN EmpsByNumber_tt)
      IS
      BEGIN
        Pl(coll_in.count);
        Pl('first item :'||coll_in(coll_in.first).empno||'|'||coll_in(coll_in.first).ename||'|'||coll_in(coll_in.first).sal);
        Pl('last item  :'||coll_in(coll_in.last).empno||'|'||coll_in(coll_in.last).ename||'|'||coll_in(coll_in.last).sal);
      END display;
      PROCEDURE display_vc(coll_in IN EmpsByvarchar_tt)
      IS
      BEGIN
        Pl(coll_in.count);
        Pl('first item :'||coll_in(coll_in.first).empno||'|'||coll_in(coll_in.first).ename||'|'||coll_in(coll_in.first).sal);
        Pl('last item  :'||coll_in(coll_in.last).empno||'|'||coll_in(coll_in.last).ename||'|'||coll_in(coll_in.last).sal);
      END display_vc;
    BEGIN
      SELECT empno, ename, sal
      BULK COLLECT INTO l_Emps
      FROM emp;
      Display(l_Emps);
      Pl('--Now sort--');
    
      FOR indx IN l_emps.first .. l_emps.last loop
        l_EmpsByEmpno(l_emps(indx).empno) := l_emps(indx);
        l_EmpsByEname(l_emps(indx).ename) := l_emps(indx);
        l_EmpsBySal(l_emps(indx).sal) := l_emps(indx);
      END loop;
      Pl('--empno--');
    
      display(l_EmpsByEmpno);
      Pl('--ename--');
    
      display_vc(l_EmpsByEname);
      Pl('--sal--');
    
      display(l_EmpsBySal);
    END;
    /
    14
    first item :7369|SMITH|800
    last item  :7934|MILLER|1300
    --Now sort--
    --empno--
    14
    first item :7369|SMITH|800
    last item  :7934|MILLER|1300
    --ename--
    14
    first item :7876|ADAMS|1100
    last item  :7521|WARD|1250
    --sal--
    12
    first item :7369|SMITH|800
    last item  :7839|KING|5000
    
    PL/SQL procedure successfully completed.

    Notice the number of records still available when sorting by salary. It is only 12 instead of the expected 14. This happens because there are duplicates and since we are using the SAL column as an index value, duplicates get overwritten. Only the last one survives.

  • Insert the records into a physical table, preferably a global temporary table since you won’t get the overhead of redo logs etc., and use SQL to sort the records. Of course you will use the bulk capabilities of the FORALL statement to insert the records in a single pass.

    First create a global temporary table to hold our data.

    CREATE GLOBAL TEMPORARY TABLE emp_gtt (
      empno NUMBER(4,0)
    , ename VARCHAR2(10)
    , sal NUMBER(7,2)
    )

    Then check the following script:

    DECLARE
      TYPE emp_rt IS record
      (Empno emp.empno%type
      ,ename emp.ename%type
      ,sal emp.sal%type);
      TYPE EmpsByNumber_tt IS TABLE OF emp_rt INDEX BY pls_integer;
    --
    
      l_Emps EmpsByNumber_tt;
      PROCEDURE pl(what_in IN VARCHAR2)
      IS
      BEGIN
        Dbms_output.put_line(what_in);
      END pl;
      PROCEDURE display(coll_in IN EmpsByNumber_tt)
      IS
      BEGIN
        Pl(coll_in.count);
        Pl('first item :'||coll_in(coll_in.first).empno||'|'||coll_in(coll_in.first).ename||'|'||coll_in(coll_in.first).sal);
        Pl('last item  :'||coll_in(coll_in.last).empno||'|'||coll_in(coll_in.last).ename||'|'||coll_in(coll_in.last).sal);
      END display;
    BEGIN
      SELECT empno, ename, sal
      BULK COLLECT INTO l_Emps
      FROM emp;
      Display(l_Emps);
      Pl('--Insert into global temporary Table--');
    
      FORALL indx IN l_Emps.first .. l_Emps.last
        INSERT INTO emp_gtt (empno, ename, sal)
        VALUES (l_Emps(indx).empno, l_Emps(indx).ename, l_Emps(indx).sal);
      Pl('--Now select with order by--');
    
      SELECT empno, ename, sal
      BULK COLLECT INTO l_Emps
      FROM emp_gtt
      ORDER BY empno;
      Pl('--empno--');
    
      display(l_Emps);
      SELECT empno, ename, sal
      BULK COLLECT INTO l_Emps
      FROM emp_gtt
      ORDER BY ename;
      Pl('--ename--');
    
      display(l_Emps);
      SELECT empno, ename, sal
      BULK COLLECT INTO l_Emps
      FROM emp_gtt
      ORDER BY sal;
      Pl('--sal--');
    
      display(l_Emps);
    END;
    /
    14
    first item :7369|SMITH|800
    last item  :7934|MILLER|1300
    --Insert into Global Temporary Table--
    --Now select with order by--
    --empno--
    14
    first item :7369|SMITH|800
    last item  :7934|MILLER|1300
    --ename--
    14
    first item :7876|ADAMS|1100
    last item  :7521|WARD|1250
    --sal--
    14
    first item :7369|SMITH|800
    last item  :7839|KING|5000
    
    PL/SQL procedure successfully completed.

    Notice that there are always the expected 14 records available. Of course you would run an insert-as-select statement to fill up the global temporary table, but for keeping the code similar in all scripts it is done this way.

  • Copy your data to a Nested Table and use that in a SQL statement. First make sure there is an object type and a nested table defined in the database:
    CREATE OR REPLACE TYPE emp_ot AS OBJECT
      ( empno NUMBER(4,0)
    , ename VARCHAR2(10)
    , sal NUMBER(7,2)
      )
    CREATE OR REPLACE TYPE emps_nt IS TABLE OF emp_ot

    Then check out this code to see what needs to be done to sort the data:

    DECLARE
      TYPE emp_rt IS record
      (Empno emp.empno%type
      ,ename emp.ename%type
      ,sal emp.sal%type);
      TYPE EmpsByNumber_tt IS TABLE OF emp_rt INDEX BY pls_integer;
      l_Emps EmpsByNumber_tt;
      L_empsnt emps_nt;
      PROCEDURE pl(what_in IN VARCHAR2)
      IS
      BEGIN
        Dbms_output.put_line(what_in);
      END pl;
      PROCEDURE display(coll_in IN EmpsByNumber_tt)
      IS
      BEGIN
        Pl(coll_in.count);
        Pl('first item :'||coll_in(coll_in.first).empno||'|'||coll_in(coll_in.first).ename||'|'||coll_in(coll_in.first).sal);
        Pl('last item  :'||coll_in(coll_in.last).empno||'|'||coll_in(coll_in.last).ename||'|'||coll_in(coll_in.last).sal);
      END display;
    BEGIN
      SELECT empno, ename, sal
      BULK COLLECT INTO l_Emps
      FROM emp;
      Display(l_Emps);
      L_empsnt := emps_nt();
      FOR indx IN l_emps.first .. l_emps.last loop
        L_empsnt.extend;
        L_empsnt(l_empsnt.last) := emp_ot(l_emps(indx).empno,l_emps(indx).ename,l_emps(indx).sal);
      END loop;
      Pl('--Now SELECT WITH ORDER by--');
    
      SELECT empno, ename, sal
      BULK COLLECT INTO l_Emps
      FROM table(l_empsnt)
      ORDER BY empno;
      Pl('--empno--');
    
      display(l_Emps);
      SELECT empno, ename, sal
      BULK COLLECT INTO l_Emps
      FROM table(l_empsnt)
      ORDER BY ename;
      Pl('--ename--');
    
      display(l_Emps);
      SELECT empno, ename, sal
      BULK COLLECT INTO l_Emps
      FROM table(l_empsnt)
      ORDER BY sal;
      Pl('--sal--');
    
      display(l_Emps);
    END;
    /
    14
    first item :7369|SMITH|800
    last item  :7934|MILLER|1300
    --Now select with order by--
    --empno--
    14
    first item :7369|SMITH|800
    last item  :7934|MILLER|1300
    --ename--
    14
    first item :7876|ADAMS|1100
    last item  :7521|WARD|1250
    --sal--
    14
    first item :7369|SMITH|800
    last item  :7839|KING|5000
    PL/SQL procedure successfully completed

    These last two approaches only work when you are allowed to create objects ((GLOBAL TEMPORARY) TABLES and TYPES) in the database.

With Oracle 12c you now have the possibility to use an Associative Array in a SQL statement where this was not possible before. There are a couple of things you should take into account when using this technique. First of all, the Associative Array should be declared in a package body. The Associative Array must be of a scalar type, a rowtype of an object in the database or an object type defined at schema level. You cannot base the Associative Array on a Record Type defined in your PL/SQL code.

PACKAGE emp_tp IS
  TYPE Emp_rt IS RECORD (
    EMPNO NUMBER(4)
  , ENAME VARCHAR2(10)
  , SAL   NUMBER(7,2)
  );
  TYPE EmpsByNumber_tt IS TABLE OF Emp_rt INDEX BY PLS_INTEGER;
  PROCEDURE sorttest;
END emp_tp;
/
PACKAGE BODY emp_tp IS
  PROCEDURE pl(what_in IN VARCHAR2)
  IS
  BEGIN
    Dbms_output.put_line(what_in);
  END pl;
  PROCEDURE display(coll_in IN EmpsByNumber_tt)
  IS
  BEGIN
    Pl(coll_in.count);
    Pl('first item :'||coll_in(coll_in.first).empno||'|'||coll_in(coll_in.first).ename||'|'||coll_in(coll_in.first).sal);
    Pl('last item  :'||coll_in(coll_in.last).empno||'|'||coll_in(coll_in.last).ename||'|'||coll_in(coll_in.last).sal);
  END display;
  PROCEDURE sorttest IS
    l_emps EmpsByNumber_tt;
    l_empsorted EmpsByNumber_tt;
  BEGIN
    SELECT empno
         , ename
         , sal
      BULK COLLECT INTO l_emps
      FROM emp;
    display(l_emps);
    Pl('--Now SELECT WITH ORDER by--');

    SELECT empno, ename, sal
    BULK COLLECT INTO l_empsorted
    FROM table(l_emps)
    ORDER BY empno;
    Pl('--empno--');

    display(l_empsorted);
    SELECT empno, ename, sal
    BULK COLLECT INTO l_empsorted
    FROM table(l_emps)
    ORDER BY ename;
    Pl('--ename--');

    display(l_empsorted);
    SELECT empno, ename, sal
    BULK COLLECT INTO l_empsorted
    FROM table(l_emps)
    ORDER BY sal;
    Pl('--sal--');

    display(l_empsorted);
  END sorttest;
END emp_tp;
/
EXEC emp_tp.sorttest
14
first item :7369|SMITH|800
last item  :7934|MILLER|1300
--Now select with order by--
--empno--
14
first item :7369|SMITH|800
last item  :7934|MILLER|1300
--ename--
14
first item :7876|ADAMS|1100
last item  :7521|WARD|1250
--sal--
14
first item :7369|SMITH|800
last item  :7839|KING|5000
PL/SQL procedure successfully completed

By using either of these approaches you let the database do all the heavy lifting for you. Chances that you will write a better and faster sort algorithm than the one(s) already in the database are probably zero. But if you do, you are probably soon to be employed by Oracle.

Ref:
http://docs.oracle.com/database/121/LNPLS/release_changes.htm#LNPLS111

Tags: ,