Home > ApEx, PL/SQL > Tool to replace bind variables with v() syntax

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

Advertisements
Categories: ApEx, PL/SQL
  1. Renat
    April 23, 2009 at 2:04 pm

    Can you please explain your reasons to replace bind variables with v() function? Was such replacements critical for ApEx application?
    Thanks.

  2. Andrew Tulley
    October 30, 2008 at 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

  3. Martin D'Souza
    October 30, 2008 at 6:58 pm

    Hi Andy,

    Can you please post the code you used to do this?

    Thank you,

    Martin

  4. Martin D'Souza
    October 30, 2008 at 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

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: