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.




3 comments:

Patrick Wolf said...

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

Denes Kubicek said...

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

Denes Kubicek

Anonymous said...

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