ORA-19112: error raised during evaluation while converting comma separated strings to rows using XMLTABLE

Question

I am trying to split comma separated strings into rows using XMLTABLE function in oracle. e.g.

 WITH MYTABLE AS
  (SELECT '"AB","PQ","XY"' TEXT FROM DUAL
  )
SELECT REPLACE((column_value).GETSTRINGVAL(),'"','') "OPTION_TEXT"
FROM MYTABLE,
  XMLTABLE(TEXT);

above query works fine. But when string with & is passed, it throws exception as

java.sql.SQLException: ORA-19112: error raised during evaluation:

 WITH MYTABLE AS
  (SELECT '"AB","P & Q","XY"' TEXT FROM DUAL
  )
SELECT REPLACE((column_value).GETSTRINGVAL(),'"','') "OPTION_TEXT"
FROM MYTABLE,
  XMLTABLE(TEXT);

Note that this query is being executed from java through prepared statement.

i am specifying it because i don't want you to consider options like SET define OFF; SET ESCAPE OFF; SET SCAN OFF; while giving answer — Krishnat Molawade, Dec 08 '15 at 07:53

score 1 · Accepted Answer · answered Dec 08 '15 at 08:22

The problem is with the evaluation of the XQuery expression.

You could split the comma delimited string into rows using REGEXP_SUBSTR.

Check out this SQL Fiddle

Working demo:

SQL> WITH mytable AS
  2    (SELECT '"AB","P & Q","XY"' TEXT FROM DUAL
  3    )
  4  SELECT trim(regexp_substr(TEXT, '[^,]+', 1, LEVEL)) TEXT
  5  FROM mytable
  6    CONNECT BY LEVEL <= regexp_count(TEXT, ',')+1;

TEXT
-----------------
"AB"
"P & Q"
"XY"

score 0 · Answer 2 · answered Dec 08 '15 at 08:16

I guess the problem is with special characters inside XML.

Quick (probably dirty) workaround is to use REPLACE to replace special chars:

WITH MYTABLE AS
  (SELECT '"AB","P  & Q","XY"' TEXT FROM DUAL
  )
SELECT REPLACE(extractValue(column_value,'/text()'),'"','') "OPTION_TEXT"
FROM MYTABLE,
  XMLTABLE(REPLACE(REPLACE(REPLACE(TEXT, '&', '&#38;'), '<', '&lt;'), '>', '&gt;'));

SqlFiddleDemo

ORA-19112: error raised during evaluation while converting comma separated strings to rows using XMLTABLE

2 Answers2