Is there a way to copy BLOB records between databases in Oracle 10g?

Question

We have a production table that has millions of rows and contains a BLOB field. I would like to copy a smaller selection of these records into our development database without the possibility of participating in it DBA. I tried the following COPY command but got 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?

Answer 1

Unfortunately, you cannot copy BLOB values ​​using the COPY .

An alternative is to configure the database link in the source database and execute the 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 

Answer 2

Oracle Data Pump (10g + started) supports BLOB data movement.

Answer 3

I came up with a solution that I like - this version has a 4000 character limit for CLOB.

1) in the COPY TO database:

 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

 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) in the COPY TO database:

 INSERT TABLE_A (COL1, COL2, COL3, BLOB_COL) SELECT COL1, COL2, COL3, BLOB_COL FROM TABLE_A_TMP 

4), then cancel the tmp table

I struggled with this limitation, and this solution helped me a lot.