Oracle unable to convert from LONG datatype to CLOB datatype( incosistent datatype error)

Question

Oracle 11g is giving me the following error while trying to convert a long datatype to a clob. I try: select to_lob(long_col_name) from table1. I get :

[Error] Execution (1: 39): ORA-00932: inconsistent datatypes: expected - got LONG

What am i doing wrong here?

Solution 1

Found the answer here with the help of a colleague: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions185.htm But no idea why this restriction is in place

Solution 2

You can apply this function only to a LONG or LONG RAW column, and only in the select list of a subquery in an INSERT statement.

Solution 3

I suggest a workaround like this, hope this helps to somebody.

 SELECT substr(Y.longtoclob,
          43 + length('ALIASLONG'),
          DBMS_LOB.GETLENGTH(Y.longtoclob) -
          2 * (32 + length('ALIASLONG'))) longtoclob
  from dual,
   (select (dbms_xmlgen.getxml('SELECT t.column_long ALIASLONG 
  FROM TABLE_LONG_CLOB t WHERE t.id = 2')) longtoclob
      from dual) Y where DBMS_LOB.GETLENGTH(Y.longtoclob) > 0

Comments

[burnDB]

that is a good workaround.thanks for the sharing. I have worked in a task with old DB using LONG type, and we found out it's better to change column type.