SQL 有没有办法在 Oracle 10g 中的数据库之间复制 BLOB 记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6022706/
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
Is there a way to copy BLOB records between databases in Oracle 10g?
提问by ProfessionalAmateur
We have a production table that has millions of rows in it and contains a BLOB field, I would like to copy a smaller selection of these records into our development database without getting a DBA involved if possible. I tried the following COPY command but received a CPY-0012: Datatype cannot be copied
我们有一个生产表，其中包含数百万行并包含一个 BLOB 字段，如果可能，我想将这些记录的较小选择复制到我们的开发数据库中，而无需 DBA 参与。我尝试了以下 COPY 命令，但收到了一个CPY-0012: Datatype cannot be copied
COPY FROM user/password@prod_db TO user/password@dev_db -
INSERT TABLE_A (COL1, COL2, COL3, BLOB_COL) USING -
SELECT COL1, COL2, COL3, BLOB_COL -
FROM TABLE_A WHERE COL1='KEY'
Is there a way to copy records with a BLOB field between databases via SQL?
有没有办法通过 SQL 在数据库之间复制带有 BLOB 字段的记录？
回答by Datajam
Unfortunately you cannot copy BLOBvalues using the COPYcommand.
不幸的是，您无法BLOB使用该COPY命令复制值。
An alternative is to set up a DB link on the source database, and execute a SQL INSERTstatement:
另一种方法是在源数据库上设置一个数据库链接，并执行一条SQL INSERT语句：
CREATE DATABASE LINK link_to_prod CONNECT TO prod_user IDENTIFIED BY prod_password USING 'prod_db';
INSERT INTO TABLE_A@link_to_prod (COL1, COL2, COL3, BLOB_COL) SELECT COL1, COL2, COL3, BLOB_COL FROM TABLE_A
回答by OMG Ponies
Oracle's Data Pump (started 10g+)supports moving BLOB data.
Oracle 的数据泵（从 10g+ 开始）支持移动 BLOB 数据。
回答by John
I came up with a solution I like--this version has a 4000 character limitation on the CLOB.
我想出了一个我喜欢的解决方案——这个版本对 CLOB 有 4000 个字符的限制。
1) on the COPY TO database:
1) 在 COPY TO 数据库上：
create TABLE_A_TMP as
select COL1, COL2, COL3, cast(BLOB_COL as varchar2(4000)) BLOB_COL
from TABLE_A
where 1=0;
2) then run the copy command
2）然后运行复制命令
COPY FROM user/password@prod_db TO user/password@dev_db -
INSERT TABLE_A_TMP (COL1, COL2, COL3, BLOB_COL) USING -
SELECT COL1, COL2, COL3, cast(BLOB_COL as varchar2(4000)) -
FROM TABLE_A WHERE COL1='KEY'
3) on the COPY TO database:
3) 在 COPY TO 数据库上：
INSERT TABLE_A (COL1, COL2, COL3, BLOB_COL)
SELECT COL1, COL2, COL3, BLOB_COL
FROM TABLE_A_TMP
4) then drop the tmp table
4）然后删除tmp表
I had been struggling with this limitation, and this solution has helped me alot.
我一直在努力解决这个限制，这个解决方案对我帮助很大。
