CREATE OR REPLACE PACKAGE dyn_placeholder  
Dynamic PL/SQL Placeholder Analyzer Package  
Author: Steven Feuerstein, [email protected]  
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.  
   /* 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;

Package created.

FUNCTION only_identifier_from ( string_in IN VARCHAR2 , position_in IN PLS_INTEGER RETURN VARCHAR2 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 RETURN SUBSTR ( string_in, position_in ); 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 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 ) 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 WHILE ( NOT l_already_used AND l_row <= c_last ) l_already_used := l_name = l_placeholders ( l_row ).NAME; l_row := l_row + 1; END LOOP; END IF; IF NOT l_already_used 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 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 ) != '=' 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 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 l_placeholders placeholder_aat; BEGIN l_placeholders := all_in_string ( string_in ); IF nth_in > l_placeholders.COUNT RETURN NULL; 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 l_index PLS_INTEGER := list_in.FIRST; BEGIN WHILE ( l_index IS NOT NULL ) 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 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 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;