Copying BLOBs between databases (ORA-22992)

[Fredzo]

Help!! I'm attempting to copy a BLOB from one database to another located on a seperate machine. I can insert an empty_blob() into the remote database but can not find any method of getting the actual BLOB there. If I attempt to DBMS_LOB.WRITE the record inserted with the empty_blob(), I receive an ORA-22992 error (cannot use LOB locators selected from remote tables) or ORA-22990: LOB locators cannot span transactions.

Does anyone have a method or code snippet of how to accomplish this? I dread having to write a file and do a read of the file from the remote database. KLUDGY!!

Any assistance is greatly appreciated.

 

[MikeJones]

This is only a thought but perhaps you could read the LOB into an array of either 32K varchar2's or 32K raw's depending on whether it was a BLOB or CLOB, then pass this along with other relative details to a SP on the remote machine, This could then actually do the insert. I would use the NOCOPY paramter on the array to make it go a bit faster and perhaps if you needed to know what row had been inserted you could make it a stored function and return the rowid??

HTH,

Mike.

 

[Fredzo]

Duh! A simple "INSERT INTO ... SELECT * FROM BLOB_TABLE WHERE ..." worked just fine! It's often the simple things that are the hardest to see.

Thanks for the reply.

 

[vijuvelu]

I am migrating data from one db to another. I have a link between the two and one of the tables have BLOB datatype for one of their attributes.
I need to cursor through this table and then insert the cursor variable values into the other db's table.
I get the ORA-22992 error when I run the script.
I tried the insert into...select * from...[as per Fredzo's solution] on the database directly hoping it would work, but I still get the ORA-22992 error.
My select though has the dblink in it, i.e., select * from blob_table@db.com
Please let me know of a solution if any.
Thank you!

 

[Fredzo]

Here's something from Oracles documentation:

Distributed LOBs are not supported. Specifically, this means that the user cannot use a remote locator in the SELECT and WHERE clauses. This includes using DBMS_LOB package functions. In addition, references to objects in remote tables with or without LOB attributes is not allowed.

For example, the following operations are invalid:

SELECT lobcol from table1@remote_site;
INSERT INTO lobtable select type1.lobattr from table1@remote_site;
SELECT dbms_lob.length(lobcol) from table1@remote_site;

Valid operations on LOB columns in remote tables include:

CREATE TABLE as select * from table1@remote_site;
INSERT INTO t select * from table1@remote_site;
UPDATE t set lobcol = (select lobcol from table1@remote_site);
INSERT INTO table1@remote...
UPDATE table1@remote...
DELETE table1@remote...

Check

http://technet.oracle.com/doc/server.804/a58241/ch_lob.htm#17185

for more info on this.