Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2384
  • Last Modified:

Oracle - Convert BLOB field to VARCHAR2

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.
0
iamnamja
Asked:
iamnamja
  • 3
1 Solution
 
slightwv (䄆 Netminder) 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.
0
 
slightwv (䄆 Netminder) 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.
0
 
slightwv (䄆 Netminder) 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.
0
 
iamnamjaAuthor 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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now