Convert LONG into VARCHAR2 or some text datatype

Question

As we all know LONG is deprecated in Oracle a long back but Oracle itself is still using this datatype in their views.

So if I have to change LONG into some kind of text datatype how can I achieve that.

I am trying to query this and getting error.

ORA-00932: inconsistent datatypes: expected - got LONG

Query -

SELECT NVL(ie.column_expression, ic.column_name)
from user_ind_columns ic left join user_ind_expressions ie  on ic.index_name = ie.index_name and ic.table_name = ie.table_name 
where ic.table_name = 'Some Table'

Solution 1

There are several methods, one such is create table using TO_LOB. It is designed to convert a LONG or LONG RAW column to a CLOB or BLOB, respectively. Other methods are using PL/SQL, DBMS_XMLGEN. You can also use TO_LOB in insert statements.

Let's see how to convert LONG into CLOB-

SQL> CREATE TABLE t (x INT, y LONG);

Table created.

SQL>
SQL> INSERT INTO t VALUES (1, RPAD('*',9,'!'));

1 row created.

SQL> INSERT INTO t VALUES (2, RPAD('*',9,'@'));

1 row created.

SQL> INSERT INTO t VALUES (3, RPAD('*',9,'#'));

1 row created.

SQL> COMMIT;

Commit complete.

SQL>

So, we have table t with column y s LONG data type.

SQL> CREATE TABLE t1
  2  AS
  3  SELECT * FROM t
  4  /
SELECT * FROM t
       *
ERROR at line 3:
ORA-00997: illegal use of LONG datatype

SQL>

We can see the LONG restriction.

Let's use TO_LOB to convert it into CLOB.

SQL> CREATE TABLE t1
  2  AS
  3  SELECT x,
  4         to_lob(y) as y
  5  FROM t
  6  /

Table created.

SQL> desc t1;
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 X                                                              NUMBER(38)
 Y                                                              CLOB

SQL>

Now you have the same table with the LONG column converted to CLOB.

Solution 2

this is stupid (as in probably not efficient) but it works for samll lengths of y (ie < 2000 characters)..

CREATE TABLE t (x INT, y LONG);

INSERT INTO t VALUES (1, RPAD('*',9,'!'));

CREATE TABLE t1
AS
SELECT x,
       regexp_substr(SYS.DBMS_XMLGEN.GETXML('select y from t where rowid = '''||rowid||''''),'<Y>(.*)</Y>',1,1,'in',1) y
FROM t
/

it works by using dbms_xmlgen to generate a clob based on the LONG column.. then substr-ing the value back out.

this only works for small contents of the LONG column. but that is all i had and this worked for me.