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

Question

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

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 command.

An alternative is to set up a DB link on the source database, and execute a SQL INSERT statement:

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's Data Pump (started 10g+) supports moving BLOB data.