PL/SQL Nested Queries
Tags: database, sql, oracle, tips
Published: 2020-07-15 12:38:29

---
title: PL/SQL Nested Queries
description:  "Generating JSON for Instructure's Bridge"
published: true
date: 2020-07-14 15:00:00 UTC
tags: database,sql,oracle,tips
canonical_url: https://katiekodes.com/plsql-nested-cursors/
---

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](https://www.instructure.com/bridge/) 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 | 111@example.com | Apple |
| B2B2B2 | Borges | Benita | Benita Borges | Borges, Benita | 2222222 | 222@example.com | Boiron |
| C3C3C3 | Combs | Cathy | Cathy Combs | Combs, Cathy | 3333333 | 333@example.com | CVS |
| D4D4D4 | Daher | Darweesh | Darweesh Daher | Daher, Darweesh | 4444444 | 444@example.com | Dell |

---

## Output format

Bridge's API is expecting an [HTTP request body](https://katiekodes.com/http-intro/#4---body) filled with JSON-formatted text like this:

```json
{
  "users": [
    {
      "uid": "111@example.com",
      "email": "111@example.com",
      "last name": "Amjit",
      "first_name": "Anush",
      "full_name": "Anush Amjit",
      "name": "Anush Amjit",
      "sortable_name": "Amjit, Anush"
    },
    {
      "uid": "222@example.com",
      "email": "222@example.com",
      "last name": "Borges",
      "first_name": "Benita",
      "full_name": "Benita Borges",
      "name": "Benita Borges",
      "sortable_name": "Borges, Benita"
    }
  ]
}
```

---

## PL/SQL code

```sql
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":"111@example.com","email":"111@example.com","last name":"Amjit","first_name":"Anush","full_name":"Anush Amjit","name":"Anush Amjit","sortable_name":"Amjit, Anush"},
,
{"uid":"222@example.com","email":"222@example.com","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:

```sql
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:

```sql
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](https://docs.oracle.com/cd/E11882_01/appdev.112/e25519/create_type.htm#LNPLS01375) _(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](https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:774225935270):

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_TYPE`s.

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.

```sql
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:

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

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

```sql
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](https://xkcd.com/979/)" trouble!

---

## Links

* [AskTom:  Running SQL against Table of Records](https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:774225935270) - 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](https://stackoverflow.com/a/16185941) - Alex Poole's wording, on StackOverflow, with the same answer about `PLS-00642`
* [Different Types of Cursors in PL/SQL](https://livesql.oracle.com/apex/livesql/file/content_C81136WLRFYZF8ION6Q57GWE1.html) - code snippets by Steven Fuerstein, Oracle
* [PL/SQL Anonymous Blocks](https://livesql.oracle.com/apex/livesql/file/tutorial_KS0KNKP218J86THKN85XU37.html) - code snippets by Mike Hichwa, Oracle
* [PL/SQL declare function within an anonymous block](http://www.dba-oracle.com/t_adv_plsql_declare_function_anonymous_block.htm) by Boobal Ganesan
* [PL/SQL `IS RECORD` data types](https://docs.oracle.com/database/121/LNPLS/composites.htm#LNPLS-GUID-7DC81088-1265-4C46-A04D-E3AD10BCC04F) - official Oracle documentation
* [PL/SQL `IS TABLE OF` data types](https://docs.oracle.com/database/121/LNPLS/composites.htm#GUID-8060F01F-B53B-48D4-9239-7EA8461C2170) - official Oracle documentation
* [PL/SQL Collections and Records](https://intellipaat.com/blog/tutorial/oracle-plsql-tutorial/plsql-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](https://docs.oracle.com/en/database/oracle/oracle-database/18/sqlrf/JSON_OBJECT.html#GUID-1EF347AE-7FDA-4B41-AFE0-DD5A49E8B370) - official Oracle documentation