Reading XML from Long Datatype

2677588

We have a table which has a column of type 'Long'. This column stores xml which in turn contains tags with different values. We have a requirement to read few of these tags of the xml. I tried running the following query but it gave error as below-

SELECT

EXTRACTVALUE( XMLTYPE (to_lob(long_col_name)), '/xmlnodename')

FROM table_name;

Error: ORA-00932: inconsistent datatypes: expected - got LONG

Please advise and suggest alternative if any.

Moazzam

Check this:

Oracle 10g: Extract data (select) from XML (CLOB Type) - Stack Overflow

Sergei Krasnoslobodtsev

The column long_col_name is described  in  table as LONG?

Migrating Columns from LONGs to LOBs

https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:510622111991

odie_63

Please advise and suggest alternative if any.

Advise is to migrate the column to CLOB as soon as possible.

For a workaround, please answer the following questions :

- Database version (SELECT * FROM v$version) ?

- Do you know the maximum size of the XML you have to deal with? <= 32k ?