oracle 如何在动态sql中动态复制具有LONG数据类型的表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18735432/
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
how to dynamically copy a table with LONG datatype in dynamic sql?
提问by Donny
I'm about to learn pl/sql and currently I'm not understanding whats going wrong with my code. What I'm trying to do is to dynamically copy(backup) a specific table. So easy thing: I already created a backupTable, because I will use that quite often actually. So the first try was following:
我即将学习 pl/sql，目前我不明白我的代码出了什么问题。我想要做的是动态复制（备份）一个特定的表。这么简单的事情：我已经创建了一个备份表，因为我实际上会经常使用它。所以第一次尝试如下：
EXECUTE IMMEDIATE 'INSERT INTO '||sSchema_||'.backupTable
SELECT * FROM '||sSchema_||'.table'
This doesnt work as one of the columns contains LONG datatype
这不起作用，因为其中一列包含 LONG 数据类型
Exception ORA-00997: illegal use of LONG datatype
So the next step was trying to pack the thing into a loop and fetch each row individually:
所以下一步是尝试将事物打包成一个循环并单独获取每一行：
--Initialized as
TYPE cur_typ IS REF CURSOR;
cCursor cur_typ;
rRecord table%rowtype;
--Make sure cursor is closed
IF cCursor%ISOPEN THEN
CLOSE cCursor;
END IF;
--Run the copying
OPEN cCursor FOR 'SELECT * FROM '||sSchema_||'.table';
LOOP
FETCH cCursor INTO rRecord;
EXIT WHEN cCursor%NOTFOUND;
EXECUTE IMMEDIATE 'INSERT INTO '||sSchema_||'.updateTable 'VALUES rRecord';
END LOOP;
CLOSE cCursor;
Which is not being executed due to:
由于以下原因未执行：
ORA-03001: unimplemented feature
After that I tried to use different other ways to write that loop e.g.
之后我尝试使用不同的其他方式来编写该循环，例如
EXECUTE IMMEDIATE 'INSERT INTO '||sSchema_||'.updateTable 'VALUES :1' USING rRecord;
All with the same result: unimplemented feature.
所有结果都相同：未实现的功能。
So here comes the question: How do I create a dynamic copy of tables containg LONG datatype? Does anyone has any idea?
那么问题来了：如何创建包含 LONG 数据类型的表的动态副本？有没有人有任何想法？
Thanks a lot in advance
非常感谢提前
donny
唐尼
回答by the_slk
The target table should be using a LOB (CLOB or BLOB) type.
目标表应该使用 LOB（CLOB 或 BLOB）类型。
The LONG RAW datatype is provided for backward compatibility with existing applications. For new applications, use the BLOB and BFILE datatypes for large amounts of binary data.
提供 LONG RAW 数据类型是为了与现有应用程序向后兼容。对于新应用程序，对大量二进制数据使用 BLOB 和 BFILE 数据类型。
Oracle also recommends that you convert existing LONG RAW columns to LOB columns. LOB columns are subject to far fewer restrictions than LONG columns. Further, LOB functionality is enhanced in every release, whereas LONG RAW functionality has been static for several releases.
Oracle 还建议您将现有的 LONG RAW 列转换为 LOB 列。LOB 列受到的限制比 LONG 列少得多。此外，LOB 功能在每个版本中都得到了增强，而 LONG RAW 功能在几个版本中一直是静态的。
Source: Oracle Database Concepts
资料来源：Oracle 数据库概念
CREATE TABLE a_table
(
long_col LONG
);
CREATE TABLE a_backupTable
(
clob_col VARCHAR2(4000)
);
INSERT INTO a_table VALUES ('a');
-- 1 rows inserted.
DECLARE
l_cur SYS_REFCURSOR;
l_long LONG;
BEGIN
OPEN l_cur FOR SELECT long_col FROM a_table;
LOOP
FETCH l_cur INTO l_long;
EXIT WHEN l_cur%NOTFOUND;
INSERT INTO a_backupTable VALUES(l_long);
END LOOP;
CLOSE l_cur;
COMMIT;
END;
-- anonymous block completed
SELECT * FROM a_backupTable;
-- a
