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.

I have not been succesful so far...help me..

 

[sem]

I don't think you're on the right way. Even if you add this "tail" to your statement, these variables should be declared within the same dynamic statement. Can you explain what you really need?

Regards, Dima

 

[BADCODER]

it works if I hard code that tail. The only thing is I need a way to add it programatically without hardcoding.
thanks