StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSearching and replacing brackets in a string in Oracle 10G
text
Body
copied!<p>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.</p> <p>Is it possible to skip contents of every bracket found in a string using REGEXP_SUBTR or REGEXP_REPLACE?</p> <p>Many thanks in advance for your prompt and kind suggestions.</p> <pre><code>-- 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 := '||'',''||'; </code></pre> <p>currently, it returns:</p> <pre><code>select a || ',' || b || ',' || to_char(sysdate || ',' || 'dd/mm/yyyy') from demo </code></pre> <p>but should return something like:</p> <pre><code>select a || ',' || b || ',' || to_char(sysdate, 'dd/mm/yyyy') from demo </code></pre> <p>Many thanks Rene, your query worked but I have one more question and here it is</p> <pre><code>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; </code></pre> <p>where p_arglist is a varchar2 varray p_arglist := ('demo@demo.com','2001')</p> <pre><code>v_query := 'SELECT A, B, C FROM DEMO WHERE USERID = :USERID AND YEAR = :YEAR'; </code></pre> <p>Currently, it returns</p> <pre><code>v_query := SELECT A, B, C FROM DEMO WHERE USERID = :USERID AND YEAR = 2001 </code></pre> <p>and skips the first in the list which is the userid. many thanks for your anticipated help</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload