Use the database to do the heavy lifting

Suppose you have data in your PL/SQL program stored in a collection. I am using the EMP table (14 records) but you should imagine you have many, many more records in your collection. If you want to order (sort) your collection in a different manner somewhere in your code you can of course write your own sorting routine but the Oracle database is doing this for ages and probably a lot smarter (after all these years) than you can ever do it.

If you are running an Oracle pre 12c database you can use the SQL-engine when you have your data in a Nested Table. I have created this little test script to try this. Again, the EMP table has only 14 records defined, but it is used to drive the point home.

CREATE OR REPLACE TYPE emp_t AS OBJECT
( empno number(4)
, ename varchar2(10)
, job varchar2(9)
, mgr number(4)
, hiredate DATE
, sal number(7,2)
, comm number(7,2)
, deptno number(2)
)
CREATE OR REPLACE TYPE emp_ntt AS TABLE OF emp_t
CREATE OR REPLACE PACKAGE emp_test IS
  FUNCTION get_emp RETURN emp_ntt;
  PROCEDURE emp_prc;
END;
CREATE OR REPLACE PACKAGE BODY emp_test IS
  PROCEDURE separator
  IS
  BEGIN
    dbms_output.put_line('-=-=-=-=-');
  END;
--
  FUNCTION get_emp RETURN emp_ntt
  IS
    l_returnvalue emp_ntt;
  CURSOR c_emp IS
  SELECT emp_t(empno, ename, job, mgr, hiredate, sal, comm, deptno)
    FROM emp;
  BEGIN
    OPEN c_emp;
    FETCH c_emp BULK COLLECT INTO l_returnvalue;
    CLOSE c_emp;
    RETURN l_returnvalue;
  END;
--
  PROCEDURE emp_prc IS
    l_emps emp_ntt;
    l_emps_sorted emp_ntt;
    CURSOR c_emp_sort(emps_in emp_ntt)
    IS
    SELECT emp_t(empno, ename, job, mgr, hiredate, sal, comm, deptno)
      FROM table(emps_in)
     ORDER BY ename;
  BEGIN
    l_emps := get_emp;
    FOR rec IN (select ename FROM table(l_emps)) loop
      dbms_output.put_line(rec.ename);
    END loop;
--
    separator;
--
    OPEN c_emp_sort(l_emps);
    FETCH c_emp_sort BULK COLLECT INTO l_emps_sorted;
    CLOSE c_emp_sort;
    FOR indx IN l_emps_sorted.first .. l_emps_sorted.last loop
      dbms_output.put_line(l_emps_sorted(indx).ename);
    END loop;
  END;
END;

If I run this code

EXEC emp_test.emp_prc

the output looks like this:

SMITH
ALLEN 
WARD 
JONES 
MARTIN 
BLAKE 
CLARK 
SCOTT 
KING 
TURNER 
ADAMS 
JAMES 
FORD 
MILLER 
-=-=-=-=- 
ADAMS 
ALLEN 
BLAKE 
CLARK 
FORD 
JAMES 
JONES 
KING 
MARTIN 
MILLER 
SCOTT 
SMITH 
TURNER 
WARD

This is a great way of using the SQL engine to do the work you need done.

But what if you don’t have your data in a Nested Table, but in an Associative Array, which is PL/SQL only. If you are using Database 12c you’re in luck, because you can now use your Associative Array in de SQL statement.

I have created a similar script as the one before, except that I cannot define a TYPE in the SQL layer, it should be defined in a package specification.

PACKAGE emp_test IS
  TYPE emp_aat IS TABLE OF emp%rowtype INDEX BY PLS_INTEGER;
  FUNCTION get_emp RETURN emp_aat;
  PROCEDURE emp_prc;
END;
PACKAGE BODY emp_test IS
  PROCEDURE separator
  IS
  BEGIN
    dbms_output.put_line('-=-=-=-=-');
  END;
--
  FUNCTION get_emp RETURN emp_aat
  IS
  l_returnvalue emp_aat;
  CURSOR c_emp IS
  SELECT *
    FROM emp;
  BEGIN
    OPEN c_emp;
    FETCH c_emp BULK COLLECT INTO l_returnvalue;
    CLOSE c_emp;
    RETURN l_returnvalue;
  END;
--
  PROCEDURE emp_prc IS
    l_emps emp_aat;
    l_emps_sorted emp_aat;
    CURSOR c_emp_sort(emps_in emp_aat)
    IS
    SELECT *
      FROM table(emps_in)
     ORDER BY ename;
  BEGIN
    l_emps := get_emp;
    FOR rec IN (select ename FROM TABLE(l_emps)) LOOP
      dbms_output.put_line(rec.ename);
    END LOOP;
--
    separator;
--
    OPEN c_emp_sort(l_emps);
    FETCH c_emp_sort BULK COLLECT INTO l_emps_sorted;
    CLOSE c_emp_sort;
    FOR indx IN l_emps_sorted.first .. l_emps_sorted.last LOOP
      dbms_output.put_line(l_emps_sorted(indx).ename);
    END LOOP;
  END;
END;

If I run this code on my 11Gr2 database it results in the following error:

No errors for PACKAGE DEMO.EMP_TEST 
Warning: Package body created with compilation errors 
Errors for PACKAGE BODY DEMO.EMP_TEST: 
LINE/COL ERROR 
-------- --------------------------------------------------------------------------- 
27/5 PL/SQL: SQL Statement ignored 
28/12 PL/SQL: ORA-22905: cannot access rows from a non-nested table item 
28/18 PLS-00382: expression is of wrong type 
33/17 PL/SQL: SQL Statement ignored 
33/35 PL/SQL: ORA-22905: cannot access rows from a non-nested table item 
33/41 PLS-00382: expression is of wrong type 
34/7 PL/SQL: Statement ignored 
34/28 PLS-00364: loop index variable 'REC' use is invalid

As mentioned before, you cannot access an Associative Array in the SQL layer. To use the SQL layer anyway you can of course create the necessary objects (record type and nested table type), copy all the data from the Associative Array to a Nested Table, run the SQL statement and copy the results back to an Associative Array. This means a lot of coding and the risk of errors. But if I run the same code on my 12Cr1 database, it completes successfully and displays the following result:

SMITH 
ALLEN 
WARD 
JONES 
MARTIN 
BLAKE 
CLARK 
SCOTT 
KING 
TURNER 
ADAMS 
JAMES 
FORD 
MILLER 
-=-=-=-=- 
ADAMS 
ALLEN 
BLAKE 
CLARK 
FORD 
JAMES 
JONES 
KING 
MARTIN 
MILLER 
SCOTT 
SMITH 
TURNER 
WARD

I think Oracle does a good job extending the possibilities of the PL/SQL language, especially integrating to SQL features into the PL/SQL layer. Although this might look like a small enhancement, but it can have a big impact on the performance of your program.

Leave a Reply

Your email address will not be published. Required fields are marked *