Salesforce, Python, SQL, & other ways to put your data where you need it

Need event music? 🎸

Live and recorded jazz, pop, and meditative music for your virtual conference / Zoom wedding / yoga class / private party with quality sound and a smooth technical experience

PL/SQL Nested Queries

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

Table of Contents

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:

person_id lname fname name_fml name_lfm ph emailaddr company
A1A1A1 Amjit Anush Anush Amjit Amjit, Anush 1111111 Apple
B2B2B2 Borges Benita Benita Borges Borges, Benita 2222222 Boiron
C3C3C3 Combs Cathy Cathy Combs Combs, Cathy 3333333 CVS
D4D4D4 Daher Darweesh Darweesh Daher Daher, Darweesh 4444444 Dell

Output format

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

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

PL/SQL code

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

   CURSOR cur_user_json(t_person_id_objects_param SCHEMA_NAME.PERSON_TBL_TYPE) IS
            '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
         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;
      OPEN cur_user_json(t_person_id_objects);
         FETCH cur_user_json INTO cur_user_json_rec;
         EXIT WHEN cur_user_json%notfound;
         -- Insert delimiter
         IF NOT l_first_rec
         END IF;
         -- Insert JSONified data
         -- Ensure delimiter stays on after first record
         l_first_rec := FALSE;
      END LOOP;
      CLOSE cur_user_json;
   END write_json;

   PROCEDURE people_use_sample IS
      BULK COLLECT INTO t_person_id_objects
      FROM people
      WHERE per_id_fk in ('A1A1A1','B2B2B2')
   END people_use_sample;


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

Sample output

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


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


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.


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.


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:


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

person_id in (
   SELECT r_personid
   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-00642Wisdom Of The Ancients” trouble!

--- ---