Resolved: Issue Extracting Data from Oracle 19 CLOB column


I am having issues extracting data from a CLOB column in Oracle 19c.
My query is:
  • Column Type=CLOB

The value I need is this:
Below is the CLOB xml


Your FROM is in the wrong place, and you’re missing a comma after the xmlnamespaces clause. But with those fixed your query returns:
because you are filtering on the entire CLOB, not the specific value. You could filter on the value instead, as in @dr’s answer:
but you probably actually want to look for the value corresponding to the NetWorkLocationKey whether that starts with Instem or not:
As @OldProgrammer commented, extracting that value as a CLOB seems strange – the value looks like it would be constrained to be much smaller than 4k so could be handled as a properly-sixed varchar2. Perhaps you were having to deal with values for other keys that could be longer – which wouldn’t be necessary if you filter on the key.

If you have better answer, please add a comment about this, thank you!