Oracle：将 Long Raw 数据从一个表复制到另一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15988090/
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
Oracle: Copying Long Raw data from one table to another
提问by Farhan
Consider I have two tables Table1 and Table2 having the exact same structure. I want to copy data from Table1 to Table2. Both tables have a field with Long Raw data type. I am using the following insert statement to copy data.
考虑我有两个表 Table1 和 Table2 具有完全相同的结构。我想将数据从 Table1 复制到 Table2。两个表都有一个 Long Raw 数据类型的字段。我正在使用以下插入语句来复制数据。
INSERT INTO TABLE2
SELECT ID, NAME, TO_LOB(IMAGE_DATA) FROM TABLE1
The above insert statement is executing with no errors but the resulting data in the Image_Data column has 0 bytes in it; means nothing copied for this field. The data type for Image_Data is (Long Raw) in both tables. Can someone suggest the correct way of copying such data from one table to another.
上面的插入语句执行没有错误，但 Image_Data 列中的结果数据中有 0 个字节；表示没有为该字段复制任何内容。Image_Data 的数据类型在两个表中都是 (Long Raw)。有人可以建议将此类数据从一张表复制到另一张表的正确方法。
Many Thanks
非常感谢
回答by APC
The reason why Oracle deprecated LONG and LONG RAW - back in the last millennium - was precisely because of this problem: they were a complete pain-in-the-neck. Now might be a good time to bite the bullet and move to the modern (but not exactly cutting edge) BLOB implementation. It depends on how often you're going to work with these tables. Remember, to tolerate a problem is to insist on it.
Oracle 弃用 LONG 和 LONG RAW 的原因——早在上个千年——正是因为这个问题：它们是一个彻头彻尾的痛点。现在可能是咬紧牙关转向现代（但不是最前沿的）BLOB 实现的好时机。这取决于您使用这些表的频率。请记住，容忍问题就是坚持。
The alternatives are cumbersome. There are various mechanisms for converting LONGs into something more manageable but they don't work the other way. One approach which might work for you is:
替代方案很麻烦。有多种机制可以将 LONG 转换为更易于管理的东西，但它们不能以其他方式工作。一种可能对您有用的方法是：
- Export
Table1. As you like legacy technology you'll want to use EXP rather than Data Pump :) - Rename
Table1toTable2. - Import
Table1.
- 出口
Table1。当您喜欢传统技术时，您将希望使用 EXP 而不是数据泵 :) - 重命名
Table1为Table2. - 进口
Table1。
回答by haki
use pl/sql
使用 pl/sql
declare
l long;
begin
select long_column into l from some_table where pk = 1;
insert into some_other_table values (l);
commit;
end;
回答by PT_STAR
although long or long raw columns shouldn't be used any longer, there are lots of legacy tables to be handled. Check this out: Copy Tables with Long/LongRaw columns
尽管不应再使用长或长的原始列，但仍有许多遗留表需要处理。看看这个： 使用 Long/LongRaw 列复制表
declare
l_rec_long_table long_table%rowtype;
begin
select *
into l_rec_long_table
from long_table a
where a.long_id = 'some old id';
insert into long_table
values
('new id',
l_rec_long_table.long_column);
end;
回答by Joaquinglezsantos
SET DEFINE OFF;
DECLARE
fldLong VARCHAR2(10000);
idTable MYSCHEMA.MYTABLE.ID_MYTABLE%TYPE;
BEGIN
FOR r IN (SELECT LONGFIELD_MYTABLE, ID_MYTABLE FROM MYSCHEMA.MYTABLE) LOOP
fldLong := r.LONGFIELD_MYTABLE;
idTable := r.ID_MYTABLE;
DBMS_OUTPUT.PUT_LINE('Value field long: ' || fldLong );
Insert into MYSCHEMA.MY_ANOTHER_TABLE
(
ANOTHERFIELD1 [, ANOTHERFIELD2 ...], LONGFIELD_MY_ANOTHER_TABLE
)
SELECT FIELD1[, FIELD2 ...], fldLong
FROM MYSCHEMA.MYTABLE
WHERE ID_MYTABLE = idTable;
END LOOP;
END;
/
