Convert LONG to VARCHAR2 or some text data type
As we all know, LONG has long been deprecated in Oracle, but Oracle itself still uses this datatype in its views.
So if I need to change LONG to some text data type, how can I achieve this.
I am trying to query this and get the error.
ORA-00932: inconsistent datatypes: expected - got LONG
Request -
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'
source to share
There are several methods, one of which create table
uses TO_LOB
. It is designed to convert LONG or LONG RAW column to CLOB or BLOB respectively. Other methods used PL/SQL
, DBMS_XMLGEN
. You can also use TO_LOB
in insert instructions.
See how to convert LONG
to 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 a table t
with the column data type ys LONG
.
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 limitation.
Use TO_LOB
to convert it to 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>
You now have a table with a column LONG
converted to CLOB
.
source to share
this is silly (how probably inefficient), but works for long y lengths (i.e. <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 a LONG column .. then substituting the value back.
this only works for small contents of the LONG column. but that was all I had and it worked for me.
source to share