ORA-06502: PL/SQL : numeric or value error
ORA-06502: at "SYS.XMLType", line 254
ORA-06502: at line 1
This error was what I was getting in Oracle.
This was my incorrect query:
SELECT *
FROM
(
SELECT XMLTYPE(p.personxml).extract('//Persons/Person/IsSpecific/text()').GetStringVal() elem
from person p
)
WHERE elem = '1'
However, the column personxml can be NULL. And that is a problem to Oracle.
The solution is a NULL value check, and use an empty XML-string in that case:
SELECT *
FROM
(
SELECT XMLTYPE(nvl(p.personxml, '<Persons></Persons>')).extract('//Persons/Person/IsSpecific/text()').GetStringVal() elem
from person p
)
WHERE elem = '1'
