PL/SQL Nested Queries

14 Jul 2020 🔖 databases sql integration api json instructure
💬 EN

I’m working on a middleware tool to extract “people of interest” from an Oracle database and convert the resulting data into JSON-formatted text ready to load into Instructure’s Bridge learning management system as users via Bridge’s API. Writing PL/SQL to extract the data with reusable “nested cursors” for maintainability was quite an adventure.

It’s not done, but I’ve managed to wrap up a nice skeleton that demonstrates solutions to the hard problems.

Input data

For tutorial purposes, everything Bridge could want to know about a given person is in a handy Oracle database table called person_details:

Output format

Bridge’s API is expecting an HTTP request body filled with JSON-formatted text like this:

{
  "users": [
    {
      "uid": "[email protected]",
      "email": "[email protected]",
      "last name": "Amjit",
      "first_name": "Anush",
      "full_name": "Anush Amjit",
      "name": "Anush Amjit",
      "sortable_name": "Amjit, Anush"
    },
    {
      "uid": "[email protected]",
      "email": "[email protected]",
      "last name": "Borges",
      "first_name": "Benita",
      "full_name": "Benita Borges",
      "name": "Benita Borges",
      "sortable_name": "Borges, Benita"
    }
  ]
}

PL/SQL code

DECLARE
   v_json_start CHAR(10) := '{"users":[';
   v_json_end CHAR(2) := ']}';
   -- I only care about the r_personid part of the following data type:
   t_person_id_objects SCHEMA_NAME.PERSON_TBL_TYPE := SCHEMA_NAME.PERSON_TBL_TYPE();

   CURSOR cur_user_json(t_person_id_objects_param SCHEMA_NAME.PERSON_TBL_TYPE) IS
      SELECT
         json_object(
            'uid' value nls_lower(emailaddr)
            , 'email' value nls_lower(emailaddr)
            , 'last name' value lname
            , 'first_name' value fname
            , 'full_name' value name_fml
            , 'name' value name_fml
            , 'sortable_name' value name_lfm
            format json
         ) as bridge_one_user_json_obj
      FROM person_details
      WHERE active_ind = 'A'
      AND person_id in (
         SELECT r_personid
         FROM
         TABLE (
            CAST (
               t_person_id_objects_param as SCHEMA_NAME.PERSON_TBL_TYPE
            )
         )
      )
   ;
   
   cur_user_json_rec cur_user_json%ROWTYPE;
   
   PROCEDURE write_json IS
      l_first_rec BOOLEAN := TRUE;
   BEGIN
      dbms_output.put_line(v_json_start);
      OPEN cur_user_json(t_person_id_objects);
      LOOP
         FETCH cur_user_json INTO cur_user_json_rec;
         EXIT WHEN cur_user_json%notfound;
         -- Insert delimiter
         IF NOT l_first_rec
         THEN
		    dbms_output.put_line(',');
         END IF;
         -- Insert JSONified data
         dbms_output.put_line(cur_user_json_rec.bridge_one_user_json_obj);
         -- Ensure delimiter stays on after first record
         l_first_rec := FALSE;
      END LOOP;
      CLOSE cur_user_json;
      dbms_output.put_line(v_json_end);
   END write_json;

   PROCEDURE people_use_sample IS
   BEGIN
      SELECT
         SCHEMA_NAME.PERSON_REC_TYPE(per_id_fk, NULL, NULL, NULL, NULL)
      BULK COLLECT INTO t_person_id_objects
      FROM people
      WHERE per_id_fk in ('A1A1A1','B2B2B2')
      ;
   END people_use_sample;

--=================================================== 
-- MAIN
--=================================================== 

BEGIN
   
   BEGIN
      -- Pick an appropriate way to populate "t_person_id_objects"
      people_use_sample();
      DBMS_OUTPUT.put_line (t_person_id_objects.COUNT || ' people in the pool');
   END;
   
   write_json();
   
END;

Sample output

2 people in the pool
{"users":[
{"uid":"[email protected]","email":"[email protected]","last name":"Amjit","first_name":"Anush","full_name":"Anush Amjit","name":"Anush Amjit","sortable_name":"Amjit, Anush"},
,
{"uid":"[email protected]","email":"[email protected]","last name":"Borges","first_name":"Benita","full_name":"Benita Borges","name":"Benita Borges","sortable_name":"Borges, Benita"}
]}

Comments

It’s a long story, but I can’t write any of my PL/SQL as compiled stored procedures or functions for this project. Everything has to fit neatly into an anonymous block of PL/SQL.

I expect that there will be a lot of different “person pools” (e.g. ('A1A1A1','B2B2B2')) whom I’ll want to feed into a WHERE person_id IN (...) clause of the same generic SQL query against person_details over the years.

Therefore, my goal was to make it easy to use system parameters passed to my PL/SQL runtime to choose one of a variety of nested procedures named people_use_...() according to a given business context.

In my first draft, it was easy to set up a variable t_person_ids as follows:

t_person_ids IS TABLE OF person_details.person_id%TYPE

Unfortunately, I encountered an Oracle error message I hadn’t seen before the hard way.

If I take an Oracle SQL query, such as the SELECT ... FROM person_details found in my cur_user_json cursor and filter it with a “PL/SQL table-typed” collection like this:

WHERE person_id in (t_person_ids)

Oracle throws a PLS-00642 error:

PLS-00642: local collection types not allowed in SQL statements

Bummer.

I would’ve been completely out of luck, but luckily the database’s maintainer was able to find me 2 Oracle SQL types (as opposed to uncompiled types defined within my PL/SQL block) that had been precompiled into the database and, therefore, could be used in this clever workaround from AskTom:

1. PERSON_REC_TYPE, which was a 5-item “object” made up of an item called r_person_id of type person_details.person_id%TYPE … followed by 4 things I couldn’t care less about.
2. PERSON_TBL_TYPE, which was a “table” or array of PERSON_REC_TYPEs.

Jackpot!

I replaced the person_id%TYPE-typed variable t_person_ids with a PERSON_TBL_TYPE-typed variable called t_person_id_objects and initialized it with an empty array to ensure the cursor is happy.

t_person_id_objects SCHEMA_NAME.PERSON_TBL_TYPE := SCHEMA_NAME.PERSON_TBL_TYPE();

Any time I SELECT a “people-pool” ID field that serves as a foreign key to person_details.person_id – e.g. per_id_fk – I wrap it up as follows:

SCHEMA_NAME.PERSON_REC_TYPE(per_id_fk, NULL, NULL, NULL, NULL)

Then my WHERE clause against person_details has to read as follows:

person_id in (
   SELECT r_personid
   FROM
   TABLE (
      CAST (
         t_person_id_objects as SCHEMA_NAME.PERSON_TBL_TYPE
      )
   )
)

(Technically, my code is a bit different from the snippet above. I also parameterize the cursor to make sure Oracle doesn’t cache the values of t_person_id_objects before I’ve filled it in with a call to one of my people_use_...() procedures. Maybe I don’t need to do that, but it’s been a long day and it got things working.)

There’s more to do. Nevertheless, I’m really happy with this framework and hope I can save you a bit of PLS-00642 “Wisdom Of The Ancients” trouble!

Links

• AskTom: Running SQL against Table of Records - Tom Kyte addresses “PLS-00642: local collection types not allowed in SQL statements” on AskTom, pointing out that “the types must be defined at the SQL level for SQL to operate on them.”
• Oracle collection in where clause - Alex Poole’s wording, on StackOverflow, with the same answer about PLS-00642
• Different Types of Cursors in PL/SQL - code snippets by Steven Fuerstein, Oracle
• PL/SQL Anonymous Blocks - code snippets by Mike Hichwa, Oracle
• PL/SQL declare function within an anonymous block by Boobal Ganesan
• PL/SQL IS RECORD data types - official Oracle documentation
• PL/SQL IS TABLE OF data types - official Oracle documentation
• PL/SQL Collections and Records by “Naveen” – jump to “Checking if a Collection Is Null” for an example of a PL/SQL table type made from a PL/SQL record type
• JSON Object - official Oracle documentation