Friday, March 24, 2017

Speed up execution of your functions inside SQL statements with UDF pragma

Oracle Database makes it easy to not only write and execute SQL from within PL/SQL, but also to execute your own user-defined functions inside SQL. Suppose, for example, I have built the following function to return a sub-string between start and end locations:

FUNCTION betwnstr (
   string_in      IN   VARCHAR2
 , start_in       IN   INTEGER
 , end_in         IN   INTEGER
)
   RETURN VARCHAR2
IS
BEGIN
   RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END betwnstr;

I can then call it in a SQL statement:

SELECT bewtnstr (last_name, 3, 6)
  FROM employees

Nice, right?

But there's a catch (well, of course, right? No free lunches.). When the SQL engine encounters the PL/SQL function, it has to switch context to the PL/SQL engine to execute the function. Before it can do the switch or hand-off, it must also prepare the values to pass as actual arguments to the formal parameters of the function.

All of that takes time. And we'd much rather it didn't. Since, however, we live in the real world and not a fantasy world, the best we can hope for is that the PL/SQL dev team would do their darnedest to reduce the overhead of that context switch.

Introducing (in Oracle Database 12c Release 1) the UDF pragma. Add this statement to your function as follows:

FUNCTION betwnstr (
   string_in      IN   VARCHAR2
 , start_in       IN   INTEGER
 , end_in         IN   INTEGER
)
   RETURN VARCHAR2
IS
   PRAGMA UDF;
BEGIN
   RETURN (SUBSTR (string_in, start_in, end_in - start_in + 1));
END betwnstr;

And you will, in effect, be telling the PL/SQL compiler:
I plan to call this function mostly (or maybe even always) from a SQL statement. So please do some of the work you'd usually do at run-time right now, at compile-time.
And - wonder of wonders! - the PL/SQL compiler listens to your request and does indeed take some steps at compile-time, thereby reducing the runtime overhead of the context switch.

For an excellent, in depth exploration of the performance impact of UDF, check out this blog post from Martin Widlake. Here's the summary in terms of his performance example:

Version                      Run Time average (secs)
Traditional PL/SQL           0.33
PRAGMA UDF PL/SQL            0.08

Nice. Very nice. And with such a small change to your code!

One thing to keep in mind: the performance of the UDF-ied function could actually degrade a bit when run natively in PL/SQL (outside of a SQL statement). So the use of this pragma is best reserved for those cases when you are quite certain the function will almost always be executed from within SQL.

Tuesday, March 21, 2017

Tightening security in your PL/SQL code with 12c new features, part 1

Oracle Database 12c offers several enhancements to improve security in your PL/SQL program units.  These features include:
  • Code-based access control: fine-tune access to database objects inside program units by granting roles to program units, rather than - or in addition to - roles granted to schemas.
  • Avoid privilege escalation: Use the INHERIT [ANY] PRIVILEGES privilege to make it impossible for a lower-privileged user to take advantage of a higher-privileged user via an invoker rights unit.
In part 1, I will explore the use of INHERIT [ANY] PRIVILEGES to clamp down on possible privilege escalation.

Which means, of course, that I should first give you an example of what privilege escalation is, how it can come about, and what sorts of damage it can do.

Suppose that there is a schema named POWERFUL_BOSS in the database instance, which is the boss's schema and has lots of privileges on many critical database objects, including the PERFORMANCE_REVIEWS table. 

The instance also have a schema named LOWLY_WORKER, the owner of which works for POWERFUL_BOSS. I'll call them LW and PB for short.

PB has given LW a new task: create an invoker rights procedure to display a person's to-do list. In this fine company, each schema has its own TODO table, with the tasks for the person who owns the schema.

Here's the code to create the database objects in the PB schema:

CONNECT powerful_boss/pb

CREATE TABLE performance_reviews
(
   review_for    VARCHAR2 (100),
   star_rating   INTEGER
)
/

BEGIN
   INSERT INTO performance_reviews (review_for, star_rating)
        VALUES ('POWERFUL_BOSS', 5);

   INSERT INTO performance_reviews (review_for, star_rating)
        VALUES ('LOWLY_WORKER', 1);

   COMMIT;
END;
/

CREATE TABLE todo
(
   id      NUMBER GENERATED ALWAYS AS IDENTITY,
   title   VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO todo (title)
        VALUES ('Criticize LW.');

   INSERT INTO todo (title)
        VALUES ('Finish next FY budget.');

   COMMIT;
END;
/

And now the database objects in the LW schema:

CREATE TABLE todo
(
   id      NUMBER GENERATED ALWAYS AS IDENTITY,
   title   VARCHAR2 (100)
)
/

BEGIN
   INSERT INTO todo (title)
        VALUES ('Write todo procedure.');

   INSERT INTO todo (title)
        VALUES ('Debug the boss''s code.');

   COMMIT;
END;
/

CREATE OR REPLACE PROCEDURE show_todos
   AUTHID CURRENT_USER
IS
BEGIN
   FOR rec IN (  SELECT title
                   FROM todo
               ORDER BY title)
   LOOP
      DBMS_OUTPUT.put_line (rec.title);
   END LOOP;
EXCEPTION
   WHEN OTHERS
   THEN
      /* Bad! No re-raise. But just a demo script. */
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
/

GRANT EXECUTE ON show_todos TO PUBLIC
/

And since the show_todos procedure is an invoker rights program unit, we see the different contents of the todo tables for both PB and LW, depending on the schema in which the procedure is executed:

CONNECT powerful_boss/pb

BEGIN
   lowly_worker.show_todos;
END;
/

Criticize LW.
Finish next FY budget.

CONNECT lowly_worker/lw

BEGIN
   show_todos;
END;
/

Debug the boss's code.
Write todo procedure.

You'd think PB would congratulate LW on getting that procedure built so quickly, but no no - all LW ever hears are complaints. PB doesn't like LW much, and the feeling is mutual. LW feels like PB is constantly giving her unjustifiably poor performance reviews. A month or two goes by. The show_todos procedure is used by everyone, constantly.

LW decides to take action. She modifies the todo procedure as follows (changes in bold and blue):

CREATE OR REPLACE PROCEDURE show_todos
   AUTHID CURRENT_USER
IS
BEGIN
   FOR rec IN (  SELECT title
                   FROM todo
               ORDER BY title)
   LOOP
      DBMS_OUTPUT.put_line (rec.title);
   END LOOP;

   IF SYS_CONTEXT ('userenv', 'current_user') = 'POWERFUL_BOSS'
   THEN
      EXECUTE IMMEDIATE '
       begin 
          update performance_reviews 
             set star_rating = -100 
         where review_for = :username; 
          commit; 
       end;'
         USING SYS_CONTEXT ('userenv', 'current_user');
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      /* Bad! No re-raise. But just a demo script. */
      DBMS_OUTPUT.PUT_LINE (SQLERRM);
END;
/

That's one mean performance review! Note that the update is performed via a dynamic PL/SQL block. As a result, the procedure compiles just fine, even though LW has no privileges on the performance_reviews table. In addition, the update will only be executed when the procedure is run by PB.

Okey dokey. The procedure is moved into production (that's right - they have very lax code review procedures in their group. How about you?).

The very next day, PB decides to check his to-do list.

He runs the procedure and sees pretty much what he expected:

CONNECT powerful_boss/pb

BEGIN
   lowly_worker.show_todos;
END;
/

Criticize LW.
Finish next FY budget.

And of course there is no reason for the boss to check the contents of the performance_reviews table, but if he did he would see:

SELECT review_or, star_rating FROM performance_reviews
/

REVIEW_FOR     STAR_RATING
-------------  -----------
POWERFUL_BOSS  -100
LOWLY_WORKER   1

Ha, ha, jokes on you, PB (but probably not for long).

Well, you get the idea, right? Once an invoker rights program unit has been put into place, it can (usually) be more easily and quietly modified. And by using dynamic SQL, one could "slip in" undesirable functionality that depends on privilege escalation - the fact that when another schema executes an invoker rights unit, that unit is executed with the privileges of the invoking schema, which could be considerably greater than those of the defining schema.

What's a security conscious dev team to do?

Make it impossible to inherit privileges from the invoking schema, unless the program unit is owned by a "trusted user." You can do this using Controlling Invoker's Rights Privileges for Procedure Calls and View Access (link to doc) with the INHERIT [ANY] PRIVILEGES privilege.

In this scenario, PB tells his DBA to revoke this privilege from LW:


CONNECT system/manager

REVOKE INHERIT ANY PRIVILEGES FROM lowly_worker
/

Or you can be more selective and revoke privileges more specifically (but only if the specific privilege had previously been granted):

CONNECT system/manager

REVOKE INHERIT PRIVILEGES ON USER powerful_boss FROM lowly_worker
/

And now when PB tries to see his list of to-dos, he gets an error:

BEGIN
   lowly_worker.show_todos;
END;
/

ORA-06598: insufficient INHERIT PRIVILEGES privilege
ORA-06512: at "SCOTT.SHOW_TODOS", line 1

First, notice that even with "exception-swallowing" WHEN OTHERS clause, this exception is propagated out unhandled from the procedure. Oracle wants to make very sure you are aware of this possibly insecure situation, and take appropriate action.

In terms of action, well, obviously, if PB no longer trusts LW, he is also not going to have the LW schema owning common code. Any invoker rights code will have to be relocated to a trusted schema.

Note, however, that LW can still call her own procedure (for all the "good" it will do her). There is no inheritance of privileges going on in that scenario.

Here are some additional details on the INHERIT [ANY] PRIVILEGES feature, from the doc:

How the INHERIT [ANY] PRIVILEGES Privileges Control Privilege Access

The INHERIT PRIVILEGES and INHERIT ANY PRIVILEGES privileges regulate the privileges used when a user runs an invoker's rights procedure or queries a BEQUEATH CURRENT_USER view that references an invoker's rights procedure.

When a user runs an invoker's rights procedure, Oracle Database checks it to ensure that the procedure owner has either the INHERIT PRIVILEGES privilege on the invoking user, or if the owner has been granted the INHERIT ANY PRIVILEGES privilege. If the privilege check fails, then Oracle Database returns an ORA-06598: insufficient INHERIT PRIVILEGES privilege error.

The benefit of these two privileges is that they give invoking users control over who can access their privileges when they run an invoker's rights procedure or query a BEQUEATH CURRENT_USER view.

More to Come

In my next post on security-related enhancements in PL/SQL for Oracle Database 12c, I will explore code-based access control (granting roles to program units).

In the meantime, I hope you will agree that one lesson to take away from the above scenario is:
All modifications to code should be closely reviewed before applying them to your production application.

Tuesday, March 14, 2017

Playing Championships on the Oracle Dev Gym

We've been designing a new, modern, responsive UI for the taking quizzes on Oracle Database. The "old" site is the PL/SQL Challenge. The new site is the Oracle Dev Gym.

At the beginning of each year, we hold championship tournaments for the top 50 ranked players in each of our focus areas (currently: SQL, PL/SQL, Database Design and Logic).

This will be the first year in which you can choose to take the championship on the PL/SQL Challenge or the Dev Gym. 

So I thought it might be helpful to provide a tour of the Dev Gym's championship flow.

If you qualified to play in a championship, you should have already received emails inviting you to confirm your participation in the championships. Once you have done that, you will see the championship on the Tournaments page, when it is within a week of the championship taking place.


If you click on the championship card before it is time to start, you will see either:

1. The confirmation page, in which you can change your mind, and tell you can't play the championship after all.

2. The "launch page" for the championship. The launch page is available 30 minutes before the championship is going to start. Use that time to review the assumptions and instructions for the quizzes, so you will not be wondering about "the basics" during the championship.



The launch page contains a countdown clock. When it hits 00:00:00, the button to Start the Championship will be enabled. Click on that button and off you go!




Championships generally consist of five tough quizzes. You can use the Next and Previous buttons at the bottom of the page to move between the quizzes. You can also use the navigation list in the right sidebar.

You will see another countdown clock on the right. That tells you how much time you have left before the championship is over. When the click hits 00:00:00, your current selections will be automatically submitted.



Once you have submitted your answers, you can review the quizzes and let us know if you feel there are any mistakes or ambiguities in the quizzes. If there are, we will make corrections and apply credit as necessary before any ranking is done.

To review quizzes, simply click on the Tournaments tab, and click on your championship (either in the top list of cards or the bottom section "Recently Completed Tournaments").

Once all players accept the quizzes as error-free, you will be given an opportunity to review the rankings and make sure you don't see any aberrations there, either. 

When all players have accepted rankings, we will publish the results.

Thursday, March 9, 2017

Latest UI for Oracle Dev Gym

As some of you may know, we've been working on a new "skin" for plsqlchallenge.oracle.com, one that is more modern and responsive, and that makes it easier to quickly take a quiz.

The Oracle Dev Gym is still in an "early adaptor" state; you can take quizzes, including our weekly competitive quizzes, there. You can set up workouts and so forth. But we are not yet offering it as a day-by-day alternative to the "traditional" PL/SQL Challenge.

Here's a quick update:

1. The URL devgym.oracle.com is now configured, so you can more easily go directly to the Oracle Dev Gym (instead of going "through" the PL/SQL Challenge.

2. We've come up with a simpler, more immediate design for our home page. Rather than having to choose "Take a Quiz" from the home page, and then go through another layer of selection from there, the home page now offers immediate access to quizzes.

The previous home page:



The new home page:



To take a competitive, weekly quiz, click on Tournaments. 

Please give it a try and let us know what you think. 


Tuesday, March 7, 2017

A Roundup of New PL/SQL Features in Oracle Database 12c Release 2


I've been publishing Oracle Magazine articles, blog posts and LiveSQL scripts on new PL/SQL features in Oracle Database 12c Release 2 (there, are those enough hyperlinks?). As have others.

I thought it might be helpful to provide a single reference post from which you could check out all the others.

I also include links to content from other experts who have posted on the same topics. I will update this post as more resources are published.

First some overview articles that you will find as solid starting points:

12 Things Developers Will Love About Oracle Database 12c Release 2, covering SQL and PL/SQL features, from Chris Saxon. It even comes with an infographic!

The Power of Cloud PL/SQL, my Oracle Magazine roundup article. No, I did not choose the title of the article.

And now for specific enhancements....

PL/Scope Discovers SQL!

PL/Scope is a compiler tool that gathers information about identifiers (as of 11.1) and SQL statements (as of 12.2) in your PL/SQL code. You can do all sorts of amazing deep-dive analysis of your code with PL/Scope, answering questions like:
  • Where is a variable assigned a value in a program? 
  • What variables are declared inside a given program? 
  • Which programs call another program (that is, you can get down to a subprogram in a package)? 
  • Find the type of a variable from its declaration. 
  • [New in 12.2] Show where specific columns are referenced. 
  • [New in 12.2] Locate all SQL statements containing hints. 
  • [New in 12.2] Find all dynamic SQL usages – ideal for getting rid of SQL injection vulnerabilities. 
  • [New in 12.2] Show all locations in your code where you commit or rollback. 
  • [New in 12.2] And my latest favorite: Locate multiple appearances of same "canonical" SQL statement.
Example: Show all program units with same SQL in more than one place

SELECT object_name, 
       line, 
       text 
  FROM user_statements 
 WHERE sql_id IN (  SELECT sql_id 
                      FROM user_statements 
                     WHERE sql_id IS NOT NULL 
                  GROUP BY sql_id 
                    HAVING COUNT (*) > 1) 
 ORDER BY object_name, line

Powerful Impact Analysis: Oracle Magazine article on PL/Scope's ability to analyze SQL statements in your PL/SQL code.

Impact Analysis with PL/Scope: a presentation on PL/Scope for 12.2, offered to you on SlideShare.

Find duplicate SQL statements with PL/Scope in 12.2, from this blog.

LiveSQL Scripts
ACCESSIBLE BY clause Enhancements

The ACCESSIBLE BY clause specifies a list of PL/SQL units that are considered safe to invoke the subprogram, and blocks all others.

Starting with Oracle Database 12c release 2 (12.2), the accessor list can be defined on individual subprograms in a package. This list is checked in addition to the accessor list defined on the package itself (if any). This list may only restrict access to the subprogram – it cannot expand access. This code management feature is useful to prevent inadvertent use of internal subprograms. For example, it may not be convenient or feasible to reorganize a package into two packages: one for a small number of procedures requiring restricted access, and another one for the remaining units requiring public access.

Example: Different ways to specify whitelists at subprogram level

CREATE OR REPLACE PACKAGE pkg 
   AUTHID DEFINER 
IS 
   PROCEDURE do_this; 
 
   PROCEDURE this_for_proc_only 
      ACCESSIBLE BY (PROCEDURE generic_name); 
 
   PROCEDURE this_for_trigger_only 
      ACCESSIBLE BY (TRIGGER generic_name); 
 
   PROCEDURE this_for_any_generic_name 
      ACCESSIBLE BY (generic_name); 
END;

Enhanced Whitelist Management in 12.2
PL/SQL Expressions Enhancements

Starting with Oracle Database 12c release 2 (12.2), expressions may be used in declarations where previously only literal constants were allowed. Static expressions can now be used in subtype declarations.

The definition of static expressions is expanded to include all the PL/SQL scalar types and a much wider range of operators. Character operands are restricted to a safe subset of the ASCII character set. Operators whose results depend on any implicit NLS parameter are disallowed.

Expanded and generalized expressions have two primary benefits for PL/SQL developers: (1) Programs are much more adaptable to changes in their environment. (2) Programs are more compact, clearer, and substantially easier to understand and maintain.

Example: Soft-coding VARCHAR2 length

CREATE OR REPLACE PACKAGE pkg 
   AUTHID DEFINER 
IS 
   c_max_length constant integer := 32767; 
   SUBTYPE maxvarchar2 IS VARCHAR2 (c_max_length); 
END;  
/

DECLARE 
   l_big_string1 VARCHAR2 (pkg.c_max_length) := 'So big....'; 
   l_big_String2 pkg.maxvarchar2 := 'So big via packaged subtype....'; 
   l_half_big VARCHAR2 (pkg.c_max_length / 2) := 'So big....'; 
BEGIN    
   DBMS_OUTPUT.PUT_LINE (l_big_string1); 
   DBMS_OUTPUT.PUT_LINE (l_big_string2); 
END;
/

Avoid hard-coding maximum length of VARCHAR2 (and more), from this blog

LiveSQL script: Use static expressions
Support for SQL JSON operators in PL/SQL

This feature makes it easier to work with JSON documents stored in an Oracle Database and to generate JSON documents from relational data.
Oracle Database support for storing and querying JSON documents in the database is extended by the addition of new capabilities, including the ability to declaratively generate JSON documents from relational data using SQL and the ability to manipulate JSON documents as PL/SQL objects. SQL JSON operators are supported in PL/SQL with a few exceptions.

I'ver barely gotten started with JSON in PL/SQL, so I will add more links here soon, but in the meantime, some excellent resources from Arup Nanda and Tim Hall:

JSON in Motion by Arup Nanda

JSON Support in Oracle Database 12c Release 2 (12.2) by Tim Hall
Support for Longer Identifiers

The maximum length of all identifiers used and defined by PL/SQL is increased to 128 bytes, up from 30 bytes in previous releases. If the COMPATIBLE parameter is set to a value of 12.2.0 or higher, the representation of the identifier in the database character set cannot exceed 128 bytes. If the COMPATIBLE parameter is set to a value of 12.1.0 or lower, the limit is 30 bytes.

A new function ORA_MAX_NAME_LEN_SUPPORTED has been introduced to check this limit.
EXEC DBMS_OUTPUT.PUT_LINE(ORA_MAX_NAME_LEN_SUPPORTED);
128

A new constant ORA_MAX_NAME_LEN defines the name length maximum. New subtypes DBMS_ID and DBMS_QUOTED_ID define the length of identifiers in objects for SQL, PL/SQL and users.

LiveSQL script:  Identifiers can now be up to 128 bytes in length!
PL/SQL Deprecation Pragma

The DEPRECATE pragma marks a PLSQL program element as deprecated. The compiler warnings tell users of a deprecated element that other code may need to be changed to account for the deprecation.

Example: Marking a package as deprecated

CREATE OR REPLACE PACKAGE pkg 
   AUTHID DEFINER 
AS 
   PRAGMA DEPRECATE(pkg); 
 
   PROCEDURE proc; 
   FUNCTION func RETURN NUMBER; 
END;

12.2 Helps You Manage Persistent Code Base w/New Deprecate Pragma, from this blog

LiveSQL script: Use DEPRECATE Pragma to Document Deprecated Units


DBMS_SQL Binding to PL/SQL Datatypes

With 12.2, DBMS_SQL catches up with 12.1 enhancements for native dynamic SQL (EXECUTE IMMEDIATE) in its support for user-defined PL/SQL datatypes. Now you can bind records and associative arrays....and Booleans!

Oracle Magazine article: Get Up to Speed with DBMS_SQL

LiveSQL script: DBMS_SQL Binding to PL/SQL Datatypes

Example


CREATE OR REPLACE PACKAGE rec_t 
   AUTHID DEFINER  
AS 
   TYPE rec1 IS RECORD (n NUMBER); 
 
   TYPE rec IS RECORD 
   ( 
      n    NUMBER, 
      n1   NUMBER 
   ); 
END rec_t;
/

DECLARE 
/* Bind record as IN */ 
   stmt_1   CONSTANT VARCHAR2 (2000) 
      := q'[ 
DECLARE 
   v2   rec_t.rec; 
BEGIN 
   v2 := :v1; 
   DBMS_OUTPUT.put_line ( 
      'rec.n =  ' || v2.n || '  rec.n1 =  ' || v2.n1); 
END;]' ; 
 
   dummy             NUMBER; 
   cur               NUMBER; 
   v1                rec_t.rec; 
BEGIN 
   v1.n := 100; 
   v1.n1 := 200; 
 
   cur := DBMS_SQL.open_cursor (); 
   DBMS_SQL.parse (cur, stmt_1, DBMS_SQL.native); 
 
   /* Remember: with dynamic PL/SQL blocks, you bind by name, not position. */ 
   DBMS_SQL.bind_variable_pkg (cur, 'v1', v1); 
 
   dummy := DBMS_SQL.execute (cur); 
   DBMS_SQL.close_cursor (cur); 
END;

Altogether, a whole lot of useful stuff for you to review and think about how it can be applied in your applications and in your development environments. Enjoy!

Thursday, March 2, 2017

Here's a great way to put an infinite loop into your code.


Isn't that something you always wanted to do?

:-)

No, it's not. And I did that yesterday in my dev environment (well, of course, such a thing could never make it to production!). It is an enormous pain. 

You press the Run button. 

The process doesn't return in the usual 2 seconds.

You think back over the changes you just made and feel sweat break out on your forehead. Because you can see right away what you did and.....oh, how could I be so stupid?



Well, not stupid. Just in too much of a hurry. And careless. And over-confident. And thinking about too many things at once. You know, the sorts of things, "gurus" do all the time as a way of maintaining their high level of excellent to show to the world. :-(

So yes, I did this yesterday, and I thought I'd share with you my mistake to hopefully help you avoid doing the same thing in the future.

I am writing a program to automatically generate workouts for the Oracle Dev Gym (which will soon take over from the PL/SQL Challenge as an "expertise through exercise" learning platform).

I am relying heavily on collections (PL/SQL arrays). Now, I don't know about all of you, but I often go through several iterations of the design of those collections:
  • Use a collection of IDs? No, a collection of records. 
  • Use an integer indexed array? Hmmm, no, wait, maybe it should be string indexed...?
  • Oh, here's a great opportunity to use a nested collection!
And so on. It's all great fun, and the end result is usually less code and a cleaner algorithm. But along the way, it's kind of messy.

In this particular instance of an infinite loop, I had started out with a nested table to hold comma-delimited lists of quiz IDs. This nested table was densely-filled and so my loop looked like this:

PROCEDURE create_workouts_for_sets (
   resource_in    IN ov.ov_resources_external%ROWTYPE,
   quiz_sets_in   IN quiz_sets_t)
IS
BEGIN
   FOR indx IN 1 .. quiz_sets_in.COUNT
   LOOP
      create_workout;
      parse_list (quiz_sets_in(indx), l_quizzes);
      load_workout_actitivies (l_quizzes);
   END LOOP;
END;

Except that I didn't actually create those nested subprograms (create_workout, etc.). Instead, the body of the loop contained all the logic and extended for 100+ lines of code (thereby violating one of my personal favorite best practices: keep your executable sections tiny and highly readable). This point will become important in a moment.

OK, so as I built more of the algorithm, I realized that I needed to make sure I wasn't generating multiple workouts with the same list of quizzes. How to check for duplication? I suppose I could compare those comma-delimited lists....but, wait! Why I am creating a comma-delimited list to begin with? Why not have a collection of the selected quizzes?

And, another brainstorm: why not use that comma-delimited list instead as the index for the array? Then it is transparently easy to tell if there is duplication: does an element exist at that location in my now-string indexed array?

That sounds like fun! So I switched to a collection of records indexed by string (associative array):

SUBTYPE quiz_list_index_t IS VARCHAR2 (4000);

TYPE quiz_set_rt IS RECORD
   (
      maximum_time    INTEGER,
      difficulty_id   INTEGER,
      quizzes         numbers_nt
   );

TYPE quiz_sets_t IS TABLE OF quiz_set_rt
      INDEX BY quiz_list_index_t;

Then I changed the loop as follows:

PROCEDURE create_workouts_for_sets (
   resource_in    IN ov.ov_resources_external%ROWTYPE,
   quiz_sets_in   IN quiz_sets_t)
IS
   l_index quiz_list_index_t := quiz_sets_in.FIRST;
BEGIN
   WHILE l_index IS NOT NULL 
   LOOP
      create_workout;
      parse_list (quiz_sets_in(indx), l_quizzes);
      load_workout_actitivies (l_quizzes);
   END LOOP;
END;

And then after making a whole bunch more edits, and getting the package to compile, I decided to try it out.

I executed the parent procedure of create_workouts_for_sets....and it disappeared into NeverLand, never to return. Can you see the problem? Hopefully, it was instantly clear for you since the executable section above is so small:
I never change the value of l_index. Now that, dear friends, is one tight little infinite loop, right there.
In my program, however, because I had not yet refactored the 120-line body into nested subprograms, the END LOOP was "off the page", out of view, and therefore out of thought.

I needed to move on to the next-defined element in the collection, as follows:

PROCEDURE create_workouts_for_sets (
   resource_in    IN ov.ov_resources_external%ROWTYPE,
   quiz_sets_in   IN quiz_sets_t)
IS
   l_index quiz_list_index_t := quiz_sets_in.FIRST;
BEGIN
   WHILE l_index IS NOT NULL 
   LOOP
      create_workout;
      parse_list (quiz_sets_in(indx), l_quizzes);
      load_workout_actitivies (l_quizzes);
      l_index := quiz_sets_in.NEXT (l_index);
   END LOOP;
END;

You saw that, right? If not, you see it now, correct?

And that, readers, brings me to the point of this post:
When you are switching from dense to sparse collections, you will also likely need to shift from a numeric for loop to a simple or while loop, to iterate through the collection. 
When you make that change, you must not only change the header of the loop, but also add the necessary code to cause loop termination.
Or as is often said in programming circles: D'oh!