Regexp_replace function fails in Connect-SQL
A Connect-SQL record browse operation fails with a lexical error after upgrade from Oracle 11R2, to Oracle 12C. The Oracle upgrade required a change from the Translate function to 'regexp_replace' function calls. Now a SQL query with the updated syntax fails to execute.
Caused by: com.pega.pegarules.data.internal.access.rdb.TokenMgrError: Lexical error at line 1, column 183. Encountered: "1" (49), after : "\\"
Steps to Reproduce
regexp_replace(listagg( to_char(REQUIREDRENEWALDATE,'mm/dd/yyyy'),',') within group (order by to_char(REQUIREDRENEWALDATE,'mm/dd/yyyy')),'([^,]+)(,\1)+', '\1') as REQUIREDRENEWALDATE
. . .
There was a syntax error in the SQL statement: Backslash character in the function parameters is not parsed properly as it is not preceded by an escape character.
Perform the following local-change: Change occurrences of '\1' in the query to '\\1'. The backslash is a necessary escape character.
0% found this useful