ApEx - Replacing Binds Procedure

If you need to use the apex_collection.crate_collection_from_query package, you will need to replace your :P_ITEM binds with v('P_ITEM') in your SQL, in order to get it working. Sometimes, it could be a pain. Using the following procedure will do the work for you:

CREATE OR REPLACE PROCEDURE replace_binds (
   p_sql_in    IN       VARCHAR2,
   p_sql_out   OUT      VARCHAR2
)
IS
   v_sql      VARCHAR2 (32767);
   v_names    DBMS_SQL.varchar2_table;
   v_pos      NUMBER;
   v_length   NUMBER;
   v_exit     NUMBER;
BEGIN
   v_sql := p_sql_in;
   v_names := wwv_flow_utilities.get_binds (v_sql);

   FOR i IN 1 .. v_names.COUNT
   LOOP

      <<do_it_again>>
      v_pos := INSTR (LOWER (v_sql), LOWER (v_names (i)));
      v_length := LENGTH (LOWER (v_names (i)));
      v_sql :=
            SUBSTR (v_sql, 1, v_pos - 1)
         || v_names (i)
         || SUBSTR (v_sql, v_pos + v_length);
      v_sql :=
         REPLACE (v_sql,
                  UPPER (v_names (i)),
                  'v(''' || LTRIM (v_names (i), ':') || ''')'
                 );

      IF INSTR (LOWER (v_sql), LOWER (v_names (i))) > 0
      THEN
         GOTO do_it_again;
      END IF;
   END LOOP;

   p_sql_out := v_sql;
END replace_binds;

And you no longer need to worry about modifying your code.