{"id":614,"date":"2016-06-15T08:25:14","date_gmt":"2016-06-15T08:25:14","guid":{"rendered":"http:\/\/bar-solutions.com\/weblog\/?p=614"},"modified":"2016-06-15T08:25:55","modified_gmt":"2016-06-15T08:25:55","slug":"use-the-database-to-do-the-heavy-lifting","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=614","title":{"rendered":"Use the database to do the heavy lifting"},"content":{"rendered":"

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<\/a> database is doing this for ages and probably a lot smarter (after all these years) than you can ever do it.<\/p>\n

<\/p>\n

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.<\/p>\n

\n
CREATE OR REPLACE TYPE<\/span> emp_t AS<\/span> OBJECT<\/span>\r\n( empno number<\/span>(4<\/span>)\r\n, ename varchar2<\/span>(10<\/span>)\r\n, job varchar2<\/span>(9<\/span>)\r\n, mgr number<\/span>(4<\/span>)\r\n, hiredate DATE<\/span>\r\n, sal number<\/span>(7<\/span>,2<\/span>)\r\n, comm number<\/span>(7<\/span>,2<\/span>)\r\n, deptno number<\/span>(2<\/span>)\r\n)<\/pre>\n<\/div>\n
\n
CREATE OR REPLACE TYPE<\/span> emp_ntt AS<\/span> TABLE<\/span> OF<\/span> emp_t<\/pre>\n<\/div>\n
\n
CREATE OR REPLACE PACKAGE<\/span> emp_test IS<\/span>\r\n  FUNCTION<\/span> get_emp RETURN<\/span> emp_ntt;\r\n  PROCEDURE<\/span> emp_prc;\r\nEND<\/span>;<\/pre>\n<\/div>\n
\n
CREATE OR REPLACE PACKAGE<\/span> BODY<\/span> emp_test IS<\/span>\r\n  PROCEDURE<\/span> separator\r\n  IS<\/span>\r\n  BEGIN<\/span>\r\n    dbms_output.put_line('-=-=-=-=-<\/span>');\r\n  END<\/span>;\r\n--<\/span>\r\n  FUNCTION<\/span> get_emp RETURN<\/span> emp_ntt\r\n  IS<\/span>\r\n    l_returnvalue emp_ntt;\r\n  CURSOR<\/span> c_emp IS<\/span>\r\n  SELECT<\/span> emp_t(empno, ename, job, mgr, hiredate, sal, comm, deptno)\r\n    FROM<\/span> emp;\r\n  BEGIN<\/span>\r\n    OPEN<\/span> c_emp;\r\n    FETCH<\/span> c_emp BULK<\/span> COLLECT<\/span> INTO<\/span> l_returnvalue;\r\n    CLOSE<\/span> c_emp;\r\n    RETURN<\/span> l_returnvalue;\r\n  END<\/span>;\r\n--<\/span>\r\n  PROCEDURE<\/span> emp_prc IS<\/span>\r\n    l_emps emp_ntt;\r\n    l_emps_sorted emp_ntt;\r\n    CURSOR<\/span> c_emp_sort(emps_in emp_ntt)\r\n    IS<\/span>\r\n    SELECT<\/span> emp_t(empno, ename, job, mgr, hiredate, sal, comm, deptno)\r\n      FROM<\/span> table(emps_in)\r\n     ORDER<\/span> BY<\/span> ename;\r\n  BEGIN<\/span>\r\n    l_emps := get_emp;\r\n    FOR<\/span> rec IN<\/span> (select ename FROM<\/span> table(l_emps)) loop\r\n      dbms_output.put_line(rec.ename);\r\n    END<\/span> loop;\r\n--<\/span>\r\n    separator;\r\n--<\/span>\r\n    OPEN<\/span> c_emp_sort(l_emps);\r\n    FETCH<\/span> c_emp_sort BULK<\/span> COLLECT<\/span> INTO<\/span> l_emps_sorted;\r\n    CLOSE<\/span> c_emp_sort;\r\n    FOR<\/span> indx IN<\/span> l_emps_sorted.first .. l_emps_sorted.last loop\r\n      dbms_output.put_line(l_emps_sorted(indx).ename);\r\n    END<\/span> loop;\r\n  END<\/span>;\r\nEND<\/span>;<\/pre>\n<\/div>\n
If I run this code<\/p>\n
\n
EXEC<\/span> emp_test.emp_prc<\/pre>\n<\/div>\n
the output looks like this:<\/p>\n
\n
SMITH\r\nALLEN \r\nWARD \r\nJONES \r\nMARTIN \r\nBLAKE \r\nCLARK \r\nSCOTT \r\nKING \r\nTURNER \r\nADAMS \r\nJAMES \r\nFORD \r\nMILLER \r\n-=-=-=-=- \r\nADAMS \r\nALLEN \r\nBLAKE \r\nCLARK \r\nFORD \r\nJAMES \r\nJONES \r\nKING \r\nMARTIN \r\nMILLER \r\nSCOTT \r\nSMITH \r\nTURNER \r\nWARD<\/pre>\n<\/div>\n
This is a great way of using the SQL engine to do the work you need done.<\/p>\n
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.<\/p>\n
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.<\/p>\n
\n
PACKAGE<\/span> emp_test IS<\/span>\r\n  TYPE<\/span> emp_aat IS<\/span> TABLE<\/span> OF<\/span> emp%rowtype INDEX<\/span> BY<\/span> PLS_INTEGER<\/span>;\r\n  FUNCTION<\/span> get_emp RETURN<\/span> emp_aat;\r\n  PROCEDURE<\/span> emp_prc;\r\nEND<\/span>;<\/pre>\n<\/div>\n
\n
PACKAGE<\/span> BODY<\/span> emp_test IS<\/span>\r\n  PROCEDURE<\/span> separator\r\n  IS<\/span>\r\n  BEGIN<\/span>\r\n    dbms_output.put_line('-=-=-=-=-<\/span>');\r\n  END<\/span>;\r\n--<\/span>\r\n  FUNCTION<\/span> get_emp RETURN<\/span> emp_aat\r\n  IS<\/span>\r\n  l_returnvalue emp_aat;\r\n  CURSOR<\/span> c_emp IS<\/span>\r\n  SELECT<\/span> *\r\n    FROM<\/span> emp;\r\n  BEGIN<\/span>\r\n    OPEN<\/span> c_emp;\r\n    FETCH<\/span> c_emp BULK<\/span> COLLECT<\/span> INTO<\/span> l_returnvalue;\r\n    CLOSE<\/span> c_emp;\r\n    RETURN<\/span> l_returnvalue;\r\n  END<\/span>;\r\n--<\/span>\r\n  PROCEDURE<\/span> emp_prc IS<\/span>\r\n    l_emps emp_aat;\r\n    l_emps_sorted emp_aat;\r\n    CURSOR<\/span> c_emp_sort(emps_in emp_aat)\r\n    IS<\/span>\r\n    SELECT<\/span> *\r\n      FROM<\/span> table(emps_in)\r\n     ORDER<\/span> BY<\/span> ename;\r\n  BEGIN<\/span>\r\n    l_emps := get_emp;\r\n    FOR<\/span> rec IN<\/span> (select ename FROM<\/span> TABLE<\/span>(l_emps)) LOOP<\/span>\r\n      dbms_output.put_line(rec.ename);\r\n    END<\/span> LOOP<\/span>;\r\n--<\/span>\r\n    separator;\r\n--<\/span>\r\n    OPEN<\/span> c_emp_sort(l_emps);\r\n    FETCH<\/span> c_emp_sort BULK<\/span> COLLECT<\/span> INTO<\/span> l_emps_sorted;\r\n    CLOSE<\/span> c_emp_sort;\r\n    FOR<\/span> indx IN<\/span> l_emps_sorted.first .. l_emps_sorted.last LOOP<\/span>\r\n      dbms_output.put_line(l_emps_sorted(indx).ename);\r\n    END<\/span> LOOP<\/span>;\r\n  END<\/span>;\r\nEND<\/span>;<\/pre>\n<\/div>\n
If I run this code on my 11Gr2 database it results in the following error:<\/p>\n
\n
No errors for PACKAGE DEMO.EMP_TEST \r\nWarning: Package body created with compilation errors <\/span>\r\nErrors for PACKAGE BODY DEMO.EMP_TEST: \r\nLINE\/COL ERROR \r\n-------- --------------------------------------------------------------------------- \r\n27\/5 PL\/SQL: SQL Statement ignored \r\n28\/12 PL\/SQL: ORA-22905: cannot access rows from a non-nested table item \r\n28\/18 PLS-00382: expression is of wrong type \r\n33\/17 PL\/SQL: SQL Statement ignored \r\n33\/35 PL\/SQL: ORA-22905: cannot access rows from a non-nested table item \r\n33\/41 PLS-00382: expression is of wrong type \r\n34\/7 PL\/SQL: Statement ignored \r\n34\/28 PLS-00364: loop index variable 'REC' use is invalid<\/pre>\n<\/div>\n
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:<\/p>\n
\n
SMITH \r\nALLEN \r\nWARD \r\nJONES \r\nMARTIN \r\nBLAKE \r\nCLARK \r\nSCOTT \r\nKING \r\nTURNER \r\nADAMS \r\nJAMES \r\nFORD \r\nMILLER \r\n-=-=-=-=- \r\nADAMS \r\nALLEN \r\nBLAKE \r\nCLARK \r\nFORD \r\nJAMES \r\nJONES \r\nKING \r\nMARTIN \r\nMILLER \r\nSCOTT \r\nSMITH \r\nTURNER \r\nWARD<\/pre>\n<\/div>\n
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.<\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,5,9],"tags":[],"class_list":["post-614","post","type-post","status-publish","format-standard","hentry","category-oracle","category-plsql","category-sql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/614","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=614"}],"version-history":[{"count":3,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/614\/revisions"}],"predecessor-version":[{"id":669,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/614\/revisions\/669"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=614"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=614"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=614"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}