2

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?

Victor
  • 16,609
  • 71
  • 229
  • 409

4 Answers4

3

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

Victor
  • 16,609
  • 71
  • 229
  • 409
  • This is a link-only answer. While this may (or may not) answer the question, the details are all hidden in a third-party location and if the link dies then this reply has no valuable content. It would be much better if you summarised the solution in your answer. – MT0 Jun 19 '23 at 08:22
3

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.

papa.kostas
  • 29
  • 2
1

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
Manolete
  • 11
  • 4
0

You can't directly fetch LONG to LOB. You might want to convert it to VARCHAR2 first

burnDB
  • 54
  • 2
  • 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. – burnDB Sep 07 '12 at 18:34