Oracle - Convert BLOB field to VARCHAR2

iamnamja
iamnamja used Ask the Experts™
on
Hi,

I'm trying to move over a remote table to the server I'm working on by 'DATABASE LINK' and 'SELECT FROM'.
However, the remote table contains a BLOB column and it gives the following error:
'ORA-22992: cannot use LOB locators selected from remote tables'.
How can I convert the BLOB field to VARCHAR2 so that I can move over the table and/or SELECT FROM that table from remote server?

Thank you.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
>>so that I can move over the table

You can use the sqlplus COPY command to copy the table locally:
http://docs.oracle.com/cd/E11882_01/server.112/e16604/apb.htm#i641251

As far as converting it to a varcahr2, that would need to be done on the remote server and will limit you to 4000 characters.  Also, BLOBs are typically used for Binary data.  A UTL_RAW.CONVERT_TO_VARCHAR2 call would likely mess with the data.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Why the "B" grade?

What additional information did you require before providing an "A" grade?

Please review:
http://support.experts-exchange.com/customer/portal/articles/481419-what-grade-should-i-award-?b_id=44

B is the grade given for acceptable solutions, or a link to an acceptable solution. A B grade means the solution given lacked some information or required you to do a good amount of extra work to resolve the problem. When closing the question, the asker should explain why a B grade was awarded.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
You cannot select a BLOB across a database link.

You can try a few of the suggestions on asktom.com.
There are several threads on remote BLOBs.

See if you can do anything here:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5322964030684

Once you figure out how to get the BLOB local, then we can work on converting it to varchar2.

Author

Commented:
I'm accepting this as the best solution and closing this question, because this would normally work. The problem I have is that my remote table is just too big (90GB), and that's why I ended up doing the task without moving the table and without converting BLOB to Varchar2.

Thank you so much for your help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial