Tool to replace bind variables with v() syntax
On quite a few occasions I’ve had a SQL query in ApEx such as:
SELECT *
FROM persons
WHERE surname LIKE :PX_SURNAME
AND first_name LIKE :PX_FIRST_NAME
And needed, for one reason or another, to replace all references to my items which are made using the bind variable syntax [e.g. :MY_ITEM] with the v() syntax [e.g. v('MY_ITEM')]. For short queries like the one above, it doesn’t take long to do this by hand. For longer queries, however, this can be a pain.
So I made a (very) simple little tool using regular expressions and ApEx to do this automatically which can be found here: http://apex.oracle.com/pls/otn/f?p=666666:3
on April 23, 2009 on 2:04 pm
Can you please explain your reasons to replace bind variables with v() function? Was such replacements critical for ApEx application?
Thanks.
on October 30, 2008 on 7:17 pm
Martin,
The below code is what is used to do the replacing.
————————-
:P3_OUTPUT_STRING := regexp_replace(
:P3_INPUT_STRING
,’:([a-zA-Z0-9_]*)’
,’v(”\1”)’);
————————-
Information on back-referencing with Regular Expressions can be found here:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_regexp.htm#sthref562
on October 30, 2008 on 6:58 pm
Hi Andy,
Can you please post the code you used to do this?
Thank you,
Martin
on October 30, 2008 on 6:56 pm
Hi Andy,
Can you please post the code that you used to replace bind variables with the v() function?
Thank you,
Martin