CREATE OR REPLACE PACKAGE dyn_placeholder  
/*  
Dynamic PL/SQL Placeholder Analyzer Package  
  
Author: Steven Feuerstein, steven.feuerstein@oracle.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 dyn_placeholder;

CREATE OR REPLACE PACKAGE BODY 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 dyn_placeholder;

begin
   dyn_placeholder.show_placeholders (
      'update abc where col1 = :mycol and col2 = :mycol2 where col3 like :wildcard');
end;

begin
   dyn_placeholder.show_placeholders (
      'update abc where col1 = :mycol and col2 = :mycol where col3 like :wildcard');
end;

begin
   dyn_placeholder.show_placeholders (
      'begin
           update abc where col1 = :mycol and col2 = :mycol where col3 like :wildcard;
       end;',
      dyn_plsql_in => TRUE);
end;