CREATE OR REPLACE PACKAGE qctod#dyn_placeholder
/*
Dynamic PL/SQL Placeholder Analyzer Package

Author: Steven Feuerstein, steven@stevenfeuerstein.com
Created: December 20, 2005

Overview: Helps you analyze strings for placeholders, which is defined as the
colon character ":" followed by anything but =.

Most programs have a parameter named dyn_plsql_in. If your string is part of
a dynamic PL/SQL block, you should pass TRUE for this argument. If you pass
NULL and the string ends with a semi-colon, then I will assume it is a
dynamic PL/SQL block. Why bother with this? In a dynamic SQL block, you need
to provide a bind variable for each placeholder, even if there are multiple
with the same name. In a dynamic PL/SQL block, you only provide a value for
the first occurrence of each placeholder (by name).

Note: these programs do not take into account : characters that occur
within a comment or within a literal.
*/
IS
   /* Return two pieces of information about each placeholder:
      1. The name (without the colon) upper-cased.
      2. The location in the string (starting with the colon)
   */
   TYPE placeholder_rt IS RECORD (
      NAME VARCHAR2 (100)
    , POSITION PLS_INTEGER
   );

   -- Associative array type to hold information about all placeholders.
   TYPE placeholder_aat IS TABLE OF placeholder_rt
      INDEX BY PLS_INTEGER;

   -- Return the number of placeholders found in the string.
   FUNCTION count_in_string (
      string_in IN VARCHAR2
    , dyn_plsql_in IN BOOLEAN DEFAULT FALSE
   )
      RETURN PLS_INTEGER DETERMINISTIC;

   -- Return the nth placeholder in the string.
   FUNCTION nth_in_string (
      string_in IN VARCHAR2
    , nth_in IN PLS_INTEGER
    , dyn_plsql_in IN BOOLEAN DEFAULT FALSE
   )
      RETURN placeholder_rt DETERMINISTIC;

   -- Return all the placeholders in an associative array.
   FUNCTION all_in_string (
      string_in IN VARCHAR2
    , dyn_plsql_in IN BOOLEAN DEFAULT FALSE
   )
      RETURN placeholder_aat DETERMINISTIC;

   -- Display all the placeholders.
   PROCEDURE show_placeholders (
      list_in IN placeholder_aat
    , dyn_plsql_in IN BOOLEAN DEFAULT FALSE
   );

   PROCEDURE show_placeholders (
      string_in IN VARCHAR2
    , dyn_plsql_in IN BOOLEAN DEFAULT FALSE
   );

   FUNCTION eq (
      rec1_in IN placeholder_rt
    , rec2_in IN placeholder_rt
    , nullq_eq_in IN BOOLEAN DEFAULT TRUE
   )
      RETURN BOOLEAN DETERMINISTIC;
END qctod#dyn_placeholder;
/

SHO ERR

CREATE OR REPLACE PACKAGE BODY qctod#dyn_placeholder
IS
   FUNCTION only_identifier_from (
      string_in IN VARCHAR2
    , position_in IN PLS_INTEGER
   )
      RETURN VARCHAR2
   IS
      c_delimiters CONSTANT VARCHAR2 (100)
         := '!@%^&*()-=+\|`~{[]};:''",<.>/? ' || CHR (10) || CHR (13)
            || CHR (9);
      l_end PLS_INTEGER;
   BEGIN
      l_end :=
         INSTR (TRANSLATE (string_in
                         , c_delimiters
                         , RPAD (CHR (2), LENGTH (c_delimiters), CHR (2))
                          )
              , CHR (2)
              , position_in
               );

      IF l_end = 0
      THEN
         RETURN SUBSTR (string_in, position_in);
      ELSE
         RETURN SUBSTR (string_in, position_in, l_end - position_in);
      END IF;
   END only_identifier_from;

   FUNCTION all_in_string (
      string_in IN VARCHAR2
    , dyn_plsql_in IN BOOLEAN DEFAULT FALSE
   )
      RETURN placeholder_aat
   IS
      c_is_dynplsql CONSTANT BOOLEAN
                            := NVL (dyn_plsql_in, SUBSTR (string_in, -1) =
                                                                          ';');
      l_start PLS_INTEGER := 1;
      l_loc PLS_INTEGER;
      l_placeholders placeholder_aat;

      PROCEDURE add_placeholder (loc_in IN PLS_INTEGER)
      IS
         l_row PLS_INTEGER := l_placeholders.FIRST;
         c_last CONSTANT PLS_INTEGER := l_placeholders.LAST;
         l_name VARCHAR2 (32767);
         l_already_used BOOLEAN := FALSE;
      BEGIN
         l_name := UPPER (only_identifier_from (string_in, loc_in + 1));

         IF c_is_dynplsql
         THEN
            WHILE (NOT l_already_used AND l_row <= c_last)
            LOOP
               l_already_used := l_name = l_placeholders (l_row).NAME;
               l_row := l_row + 1;
            END LOOP;
         END IF;

         IF NOT l_already_used
         THEN
            l_row := l_placeholders.COUNT + 1;
            l_placeholders (l_row).NAME := l_name;
            l_placeholders (l_row).POSITION := loc_in;
         END IF;
      END add_placeholder;
   BEGIN
      LOOP
         l_loc := INSTR (string_in, ':', l_start);
         EXIT WHEN l_loc = 0 OR l_loc IS NULL;

         IF SUBSTR (string_in, l_loc + 1, 1) != '='
         THEN
            add_placeholder (l_loc);
         END IF;

         l_start := l_loc + 1;
      END LOOP;

      RETURN l_placeholders;
   END all_in_string;

   FUNCTION count_in_string (
      string_in IN VARCHAR2
    , dyn_plsql_in IN BOOLEAN DEFAULT FALSE
   )
      RETURN PLS_INTEGER
   IS
      l_placeholders placeholder_aat;
   BEGIN
      l_placeholders := all_in_string (string_in, dyn_plsql_in);
      RETURN l_placeholders.COUNT;
   END count_in_string;

   FUNCTION nth_in_string (
      string_in IN VARCHAR2
    , nth_in IN PLS_INTEGER
    , dyn_plsql_in IN BOOLEAN DEFAULT FALSE
   )
      RETURN placeholder_rt
   IS
      l_placeholders placeholder_aat;
   BEGIN
      l_placeholders := all_in_string (string_in);

      IF nth_in > l_placeholders.COUNT
      THEN
         RETURN NULL;
      ELSE
         RETURN l_placeholders (nth_in);
      END IF;
   END nth_in_string;

   PROCEDURE show_placeholders (
      list_in IN placeholder_aat
    , dyn_plsql_in IN BOOLEAN DEFAULT FALSE
   )
   IS
      l_index PLS_INTEGER := list_in.FIRST;
   BEGIN
      WHILE (l_index IS NOT NULL)
      LOOP
         DBMS_OUTPUT.put_line (   list_in (l_index).NAME
                               || ' - '
                               || list_in (l_index).POSITION
                              );
         l_index := list_in.NEXT (l_index);
      END LOOP;
   END show_placeholders;

   PROCEDURE show_placeholders (
      string_in IN VARCHAR2
    , dyn_plsql_in IN BOOLEAN DEFAULT FALSE
   )
   IS
   BEGIN
      show_placeholders (all_in_string (string_in, dyn_plsql_in));
   END show_placeholders;

   FUNCTION eq (
      rec1_in IN placeholder_rt
    , rec2_in IN placeholder_rt
    , nullq_eq_in IN BOOLEAN DEFAULT TRUE
   )
      RETURN BOOLEAN
   IS
   BEGIN
      RETURN (    (   rec1_in.NAME = rec2_in.NAME
                   OR (rec1_in.NAME IS NULL AND rec2_in.NAME IS NULL)
                  )
              AND (   rec1_in.POSITION = rec2_in.POSITION
                   OR (rec1_in.POSITION IS NULL AND rec2_in.POSITION IS NULL
                      )
                  )
             );
   END eq;
END qctod#dyn_placeholder;
/

SHO ERR