Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now
Course Of The Month
8 days, 13 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Oracle - Convert BLOB field to VARCHAR2
Posted on 2014-03-06
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.
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.
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
3
6 Comments
Active 1 day ago
>>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_VARCHAR
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_VARCHAR
Active 1 day ago
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.
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.
Active 1 day ago
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.
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.
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.
Thank you so much for your help.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Why doesn't the Oracle optimizer use my index?
Querying too much data
Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table.
So, the obvious side…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows how to Export data from an Oracle database using the Datapump Export Utility. The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility. The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month8 days, 13 hours left to enroll
Earn Certification
Certified Information Systems Auditor (CISA)
$99.00$89.10
617 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.