Solved

Oracle - Convert BLOB field to VARCHAR2

Posted on 2014-03-06
6
2,245 Views
Last Modified: 2014-03-11
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
Comment
Question by:iamnamja
  • 3
6 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 39909801
>>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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39910618
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39910789
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
 

Author Closing Comment

by:iamnamja
ID: 39920478
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Oracle Database Upgrade 13 60
Oracle SQL Select within a Where Clause 9 56
PL/SQL - Leading zeros 7 57
Web Service from a stored procdure oracle 10 49
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now