Utility for Rewriting query parameters

Last month I have worked a lot with charts in Apex. As it was a BI application on research data the queries were long and contained many input parameters.

Starting in Apex I used bind variables to reference the input items.

select name from table where id = :P100_ID

Then I move the query to a package and I had to rewrite the bind variables to calls of the Apex v function.

select name from table where id = v('P100_ID')

For some queries I preferred to pass the parameters as PL/SQL procedure parameters so I had to change the query to:

select name from table where id = p_id

And for defining a ref cursor I prefer to use substitution parameters:

select name from table where id = [id]

Now this is just a short query with just one parameter.
But it is a lot of work for queries with  20+ lines with 5+ parameters.

And then it takes time to convert the parameters. And I make mistakes, I forget to change a parameter or I make a typo. Most of the time I find the error quite fast but sometimes it is not so obvious.

Enough reasons for me to automate this process. And make the solution publicly available:

http://www.speech2form.com/ords/f?p=OPFG:QUERY_VARIABLES

On the page you can paste your query. If possible the source parameter type is recognized.

You can indicate to what type of parameters are the target of the conversion.
Further you need to specify the delimiters for the substitution variables or the prefixes for PL/SQL and Apex ( for bind variables and v-function).

Then hit Generate, clip the code and use it.
For me most of the time I can use the code without modification.

Happy apexing...