Friday, 18 May 2007

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.




on

3 comments:

  1. Patrick Wolf18 May 2007 at 08:37

    Hi Denes,

    looks like that your code got corrupted, when you pasted it. Your goto label is missing.

    But honestly, I would avoid using GOTO in programs, you can also use a
    LOOP
    EXIT WHEN [...]
    END LOOP;
    instead too. Much nicer.

    Patrick

    ReplyDelete
  2. Denes Kubicek18 May 2007 at 11:45

    Is there any critical issue with GOTO, except it is "outdated"? This was my favorite from the times of fortran.

    Denes Kubicek

    ReplyDelete
  3. Anonymous7 July 2008 at 11:07

    Hi Denes,

    I've recently began working with ApEx here at our company (so I'm still learning at the moment) and I came across this post of yours while browsing ApEx blogs.
    Isn't it easier to just concatenate your :P_ITEM variables in your query at runtime instead of transforming them.
    The way I used it to build my collection was to just concatenate the query like this (this is just a little code from the where, the full query is rather big):
    ...
    dh.doccode not like ''DISPERSE'' and
    dh.cmpcode = ''' || to_char(:P2_SEARCH_COMPANY) ||''' and
    e.code like ''' || to_char(:P2_SEARCH_FILE) || ''' and
    dl.el2 like ''40%''
    GROUP BY
    ...

    It seems to work fine this way around (you just need to escape your ' with another ' in the query).


    Greetz and love your blog,
    Camstra

    ReplyDelete