Create the Public API

CREATE OR REPLACE PACKAGE string_tracker 
/* 
Overview: string_tracker allows you to keep track of whether a certain name has  
already been used within a particular list. 
 
Besides hopefully being useful to you, it is a nice little  demonstration of string-indexed  
associative arrays and nested collections (collections within collections) 
 
Author: Steven Feuerstein 
 
*/ 
IS 
   SUBTYPE maxvarchar2_t IS VARCHAR2 (32767); 
 
   SUBTYPE list_name_t IS maxvarchar2_t; 
 
   SUBTYPE value_string_t IS maxvarchar2_t; 
 
   PROCEDURE clear_all_lists; 
 
   PROCEDURE clear_list (list_name_in IN list_name_t); 
 
   PROCEDURE create_list ( 
      list_name_in IN list_name_t 
    , case_sensitive_in IN BOOLEAN DEFAULT FALSE 
    , overwrite_in IN BOOLEAN DEFAULT TRUE 
   ); 
 
   -- Is the string already in use? 
   FUNCTION string_in_use ( 
      list_name_in IN list_name_t 
    , value_string_in IN value_string_t 
   ) 
      RETURN BOOLEAN; 
 
   -- Mark this string as being used. 
   PROCEDURE mark_as_used ( 
      list_name_in IN list_name_t 
    , value_string_in IN value_string_t 
   ); 
END string_tracker; 

Imlpement the API

CREATE OR REPLACE PACKAGE BODY string_tracker 
/* 
Overview: string_tracker allows you to keep track of whether a 
certain name has already been used within a particular list. 
 
Author: Steven Feuerstein 
 
*/ 
IS 
   /* List of used strings - so why a collection of Booleans? 
      Because the string is the index value, so the element value 
      in the collection is of no importance. */ 
 
   TYPE used_aat IS TABLE OF BOOLEAN INDEX BY value_string_t; 
 
   /* No need to include the list name in the list record. 
      The index value that points to this record is the list name. */ 
   TYPE list_rt IS RECORD ( 
      case_sensitive   BOOLEAN 
    , list_of_values   used_aat 
   ); 
 
   TYPE list_of_lists_aat IS TABLE OF list_rt 
      INDEX BY list_name_t; 
 
   g_list_of_lists   list_of_lists_aat; 
 
   PROCEDURE assert (expr_in IN BOOLEAN, text_in IN VARCHAR2) 
   IS 
   BEGIN 
      IF NOT expr_in OR expr_in IS NULL 
      THEN 
         raise_application_error (-20000, text_in); 
      END IF; 
   END assert; 
 
   PROCEDURE clear_all_lists 
   IS 
   BEGIN 
      g_list_of_lists.DELETE; 
   END clear_all_lists; 
 
   PROCEDURE clear_list (list_name_in IN list_name_t) 
   IS 
   BEGIN 
      g_list_of_lists.DELETE (list_name_in); 
   END CLEAR_LIST; 
 
   PROCEDURE create_list ( 
      list_name_in        IN   list_name_t 
    , case_sensitive_in   IN   BOOLEAN DEFAULT FALSE 
    , overwrite_in        IN   BOOLEAN DEFAULT TRUE 
   ) 
   IS 
      l_create_list   BOOLEAN DEFAULT TRUE; 
      l_new_list      list_rt; 
   BEGIN 
      IF g_list_of_lists.EXISTS (list_name_in) 
      THEN 
         l_create_list := overwrite_in; 
      END IF; 
 
      IF l_create_list 
      THEN 
         l_new_list.case_sensitive := case_sensitive_in; 
         g_list_of_lists (list_name_in) := l_new_list; 
      END IF; 
   END create_list; 
 
   FUNCTION sensitized_value ( 
      list_name_in      IN   list_name_t 
    , value_string_in   IN   value_string_t 
   ) 
      RETURN value_string_t 
   IS 
   BEGIN 
      RETURN CASE g_list_of_lists (list_name_in).case_sensitive 
         WHEN TRUE 
            THEN value_string_in 
         ELSE UPPER (value_string_in) 
      END; 
   END sensitized_value; 
 
   FUNCTION string_in_use ( 
      list_name_in      IN   list_name_t 
    , value_string_in   IN   value_string_t 
   ) 
      RETURN BOOLEAN 
   IS 
   BEGIN 
      /* Don't assume inputs are valid! */ 
       
      assert (list_name_in IS NOT NULL 
            , 'You must provide a non-NULL name for your list!' 
             ); 
      assert (value_string_in IS NOT NULL 
            , 'You must provide a non-NULL string for tracking!' 
             ); 
      assert (g_list_of_lists.EXISTS (list_name_in) 
            ,    'You must create your list named "' 
              || list_name_in 
              || '" before you can use it.' 
             ); 
       
      RETURN g_list_of_lists (list_name_in).list_of_values.EXISTS 
                                            (sensitized_value (list_name_in 
                                                             , value_string_in 
                                                              ) 
                                            ); 
   END string_in_use; 
 
   PROCEDURE mark_as_used ( 
      list_name_in      IN   list_name_t 
    , value_string_in   IN   value_string_t 
   ) 
   IS 
   BEGIN 
      /* Don't assume inputs are valid! */ 
 
      assert (list_name_in IS NOT NULL 
            , 'You must provide a non-NULL name for your list!' 
             ); 
      assert (list_name_in IS NOT NULL 
            , 'You must provide a non-NULL name for your list!' 
             ); 
      assert (value_string_in IS NOT NULL 
            , 'You must provide a non-NULL string for tracking!' 
             ); 
      assert (g_list_of_lists.EXISTS (list_name_in) 
            ,    'You must create your list named "' 
              || list_name_in 
              || '" before you can use it.' 
             ); 
 
      g_list_of_lists (list_name_in).list_of_values 
                                            (sensitized_value (list_name_in 
                                                             , value_string_in 
                                                              ) 
                                            ) := TRUE; 
   END mark_as_used; 
END string_tracker;

Exercise the API

DECLARE 
   /* Create a constant with the list name to avoid multiple, 
      hard-coded references. Notice the use of the subtype 
      declared in the string_tracker package to declare the 
      list name. */ 
   c_list_name   CONSTANT string_tracker.list_name_t := 'outcomes'; 
BEGIN 
   /* Create the list, wiping out anything that was there before. */ 
   string_tracker.create_list (list_name_in        => c_list_name, 
                               case_sensitive_in   => FALSE, 
                               overwrite_in        => TRUE); 
 
   string_tracker.mark_as_used (list_name_in      => c_list_name, 
                                value_string_in   => 'abc'); 
 
   string_tracker.mark_as_used (list_name_in      => c_list_name, 
                                value_string_in   => 'def'); 
 
   IF string_tracker.string_in_use (list_name_in      => c_list_name, 
                                    value_string_in   => 'ABC') 
   THEN 
      DBMS_OUTPUT.put_line ('Case insenstive list - match found for ABC'); 
   END IF; 
 
   IF NOT string_tracker.string_in_use (list_name_in      => c_list_name, 
                                        value_string_in   => 'notinlist') 
   THEN 
      DBMS_OUTPUT.put_line ('No match found for notinlist'); 
   END IF; 
 
   /* Now case sensitive. */ 
   string_tracker.create_list (list_name_in        => c_list_name, 
                               case_sensitive_in   => TRUE, 
                               overwrite_in        => TRUE); 
   string_tracker.mark_as_used (list_name_in      => c_list_name, 
                                value_string_in   => 'abc'); 
 
   IF NOT string_tracker.string_in_use (list_name_in      => c_list_name, 
                                        value_string_in   => 'ABC') 
   THEN 
      DBMS_OUTPUT.put_line ('Case senstive list - match NOT found for ABC'); 
   END IF; 
EXCEPTION 
   WHEN OTHERS 
   THEN 
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack ()); 
END;