pl/sql string manipulation...

[BADCODER]

I am trying to write a function that should accept a query as a parameter and return all the bound columns in that query.

Function return_bound_cols(p_sql Varchar2) RETURN VARCHAR2

Example:

If the parameter p_sql=

'SELECT C1, C2
FROM T
WHERE C3 = :b3 AND C4=:b4 AND C5= 5';

The function should return the string: 'b_C3,b_C4'

so that I can later open a ref cusrsor 'USING b_C3,b_C4'.

The parameter queries are stored in a table, hence I have to generate 'USING b_C3,b_C4' dynamically.

no success so far, please help..

 

[lewisp]

This could be done with a recursive function. The code would be quite complicated, but in essence the crux of the code would parse each clause in the WHERE individually.

The function would strip out the first element in the clause and determine if there was a bind variable associated with it. If so, the element would be processed to convert the string to your required variable names, then concatenated to another call to the same function passing in whats left of the WHERE clause. The recursion would stop when all of the elements have been processed.

As I say, complicated but doable.

 

[BADCODER]

can functions REPLACE,TRANSLATE etc be used to make it eaiser? I don't know how to though.

 

[lewisp]

Yes they can. For example you can strip out all spaces like this

[tt]Replace('C3 = :b3',' ',NULL);[/tt]