Searching and replacing brackets in a string in Oracle 10G

Question

I am currently developing a function that is meant to execute dynamically created SQL statements. This is done by concatenating the columns and fetching them via cursors. The problem is that when there is a function with a comma between its arguments, the concat concatenates the contents of the functions inclusive.

Is it possible to skip contents of every bracket found in a string using REGEXP_SUBTR or REGEXP_REPLACE?

Many thanks in advance for your prompt and kind suggestions.

-- strips out the select list 
src_str := REGEXP_SUBSTR(v_sql, 'SELECT ([[:graph:]]+\ ?){1,1000000}/?');    

-- Replace the commas in the select list with the concat symbol for concatenation 
rep_str := REGEXP_REPLACE(src_str, ', ', p_dot);    

-- Replace the select list with the replace string 
v_query := REPLACE(v_sql, src_str, rep_str); 

v_sql := select a, b, to_char(sysdate, 'dd/mm/yyyy') from demo; 

p_dot := '||'',''||';

currently, it returns:

select a || ',' || b || ',' || to_char(sysdate || ',' || 'dd/mm/yyyy') from demo

but should return something like:

select a || ',' || b || ',' || to_char(sysdate, 'dd/mm/yyyy') from demo

Many thanks Rene, your query worked but I have one more question and here it is

for i in 1 .. p_arglist.count
loop

    -- Search for : in the query
    src_sym := REGEXP_SUBSTR(v_query, ':[[:graph:]]+\ ?', i,i);

    -- Replace the : with each value of p_arglist passed
    v_querymult := REGEXP_REPLACE(v_query, src_sym , p_arglist(i),i,i);

 end loop;
 return v_query;

where p_arglist is a varchar2 varray p_arglist := ('[email protected]','2001')

v_query := 'SELECT A, B, C FROM DEMO WHERE USERID = :USERID AND YEAR = :YEAR';

Currently, it returns

v_query := SELECT A, B, C FROM DEMO WHERE USERID = :USERID AND YEAR = 2001

and skips the first in the list which is the userid. many thanks for your anticipated help

Answer 1

something like this should do if I understood your requirement correctly:

-- multiple replacements to accomodate for functions with more
-- than two parameters (and accordingly more than one comma)
src_str := regexp_replace(src_str, '(\([^)]+),', '\1##comma-in-function##');
src_str := regexp_replace(src_str, '(\([^)]+),', '\1##comma-in-function##');
src_str := regexp_replace(src_str, '(\([^)]+),', '\1##comma-in-function##');

-- replace the left-over commas
src_str := replace(src_str, ', ', p_dot);

-- turn commas within function call back to commas:
src_str := replace(src_str, '##comma-in-function##', ',');

Answer 2

Have you thought about using DBMS_SQL, that should parse the SQL and allow you to bind variables.

See these links for further reading

Oracle Docs

Ask Tom Example