DEV Community

Katie
Katie

Posted on • Originally published at katiekodes.com on

PL/SQL Nested Queries

#database #sql #oracle #tips

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 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 filled with JSON-formatted text like this:

{ "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 

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:

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

Top comments (0)