SQL 将 LONG 转换为 VARCHAR2 或某种文本数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27835104/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert LONG into VARCHAR2 or some text datatype
提问by Ashish
As we all know LONG is deprecated in Oracle a long back but Oracle itself is still using this datatype in their views.
众所周知，Oracle 很久以前就弃用了 LONG，但 Oracle 本身仍在其视图中使用这种数据类型。
So if I have to change LONG into some kind of text datatype how can I achieve that.
因此，如果我必须将 LONG 更改为某种文本数据类型，我该如何实现。
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'
回答by Lalit Kumar B
There are several methods, one such is create tableusing 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_LOBin insert statements.
有几种方法，其中一种是create table使用TO_LOB. 它旨在分别将 LONG 或 LONG RAW 列转换为 CLOB 或 BLOB。其他方法正在使用PL/SQL, DBMS_XMLGEN。您也可以TO_LOB在插入语句中使用。
Let's see how to convert LONGinto CLOB-
让我们看看如何转换LONG为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 twith column y s LONGdata type.
所以，我们有一个t列 ysLONG数据类型的表。
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.
我们可以看到 LONG 限制。
Let's use TO_LOBto convert it into CLOB.
让我们用TO_LOB它来转换成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 LONGcolumn converted to CLOB.
现在您有同一个表，其中的LONG列转换为CLOB.
回答by ShoeLace
this is stupid (as in probably not efficient) but it works for samll lengths of y (ie < 2000 characters)..
这很愚蠢（因为可能效率不高）但它适用于 y 的小长度（即 < 2000 个字符）。
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.
它的工作原理是使用 dbms_xmlgen 基于 LONG 列生成一个 clob ..然后将值减去。
this only works for small contents of the LONG column. but that is all i had and this worked for me.
这仅适用于 LONG 列的小内容。但这就是我所拥有的，这对我有用。
