SQL 将 Long 转换为 Varchar2
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31004844/
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
Converting Long to Varchar2
提问by Kriti
I am trying to insert into varchar2 column from a long column. here is the below example, TEXT.TEXT_COL = VARCHAR2(4000)and NOTE.TEXT_NOTE = LONG.
我正在尝试从长列插入 varchar2 列。这是下面的示例，TEXT.TEXT_COL = VARCHAR2(4000)并且NOTE.TEXT_NOTE = LONG.
INSERT INTO TEXT(ROW_ID, TEXT_COL)
SELECT 1, TEXT_NOTE FROM NOTE;
When i run the above sql i get error
当我运行上面的 sql 时出现错误
SQL Error: ORA-00997: illegal use of LONG datatype
SQL 错误：ORA-00997：非法使用 LONG 数据类型
I used TO_LOB()too, but still the same error.
我也用过TO_LOB()，但还是同样的错误。
Is there any function which simply coverts longand put it in varchar2. Let me know your thoughts.
是否有任何功能可以简单地long将其隐藏并放入varchar2. 让我知道你的想法。
回答by Martin
Converting from longto varchar2right away using a single statement is not possible, as longhas certain restrictions.
从转换long到varchar2马上用一个单独的语句是不可能的，因为long有一定的限制。
You can either Create a temporary tableor use PL/SQL codeto solve your problem:
您可以创建临时表或使用PL/SQL 代码来解决您的问题：
Temporary Table:
CREATE TABLE TABLE2 AS SELECT TO_LOB(COLUMN1) COLUMN FROM TABLE1;PL/SQL Code:
DECLARE VAR1 LONG; VAR2 VARCHAR2(4000); BEGIN SELECT TEXT INTO VAR1 FROM USER_VIEWS WHERE ROWNUM = 1; VAR2 := SUBSTR(VAR1, 1, 4000); DBMS_OUTPUT.PUT_LINE(VAR2); END;
临时表：
CREATE TABLE TABLE2 AS SELECT TO_LOB(COLUMN1) COLUMN FROM TABLE1;PL/SQL 代码：
DECLARE VAR1 LONG; VAR2 VARCHAR2(4000); BEGIN SELECT TEXT INTO VAR1 FROM USER_VIEWS WHERE ROWNUM = 1; VAR2 := SUBSTR(VAR1, 1, 4000); DBMS_OUTPUT.PUT_LINE(VAR2); END;
回答by garbuya
It looks like Oracle internally converts LONG to something else (probably CLOB) when you select LONG in FOR loop. I did not find any explanations in Oracle documentation, but this works
当您在 FOR 循环中选择 LONG 时，看起来 Oracle 在内部将 LONG 转换为其他内容（可能是 CLOB）。我在 Oracle 文档中没有找到任何解释，但这有效
BEGIN
FOR V IN (SELECT ROWID,TEXT_NOTE FROM NOTE)
LOOP
INSERT INTO TEXT VALUES(V.ROWID, SUBSTR(V.TEXT_NOTE, 1, 4000) );
END LOOP;
COMMIT;
END;
This is an example how to copy all views from another schema to your schema
这是如何将所有视图从另一个架构复制到您的架构的示例
BEGIN
FOR V IN (SELECT VIEW_NAME, TEXT_LENGTH, TEXT FROM ALL_VIEWS WHERE OWNER = 'PROD')
LOOP
EXECUTE IMMEDIATE 'CREATE OR REPLACE FORCE VIEW '||V.VIEW_NAME||' AS '||SUBSTR(V.TEXT, 1, V.TEXT_LENGTH);
DBMS_OUTPUT.PUT_LINE('View '||V.VIEW_NAME||' created');
END LOOP;
END;
For some reason it only works for FOR loop and does not work if you use WITH or select from another query
出于某种原因，它仅适用于 FOR 循环，如果您使用 WITH 或从另一个查询中选择则不起作用
INSERT INTO TEXT
WITH V AS(SELECT ROWID ROW_ID,TEXT_NOTE FROM NOTE)
SELECT V.ROW_ID, SUBSTR(V.TEXT_NOTE, 1, 4000) FROM V;
INSERT INTO TEXT
SELECT ROW_ID, SUBSTR(TEXT_NOTE, 1, 4000)
FROM (SELECT ROWID ROW_ID,TEXT_NOTE FROM NOTE);
Both inserts raise the same error
两个插入都引发相同的错误
ORA-00932: inconsistent datatypes: expected CHAR got LONG
ORA-00932: 不一致的数据类型：预期的 CHAR 得到了 LONG
