Avatar of toooki
toooki
 asked on

Oracle Table joins with remote database tables with BLOB field

I am accessing a remote Oracle database via a Data base link:

insert into mytab1(f1, f2, f3)
select f1, to_char(substr(f2, 1, 4000)) f2 , f3 from rem_tab@mydblink;

The above works. The field f2 is a CLOB field in both database tables.

Now I want to run this query:
insert into mytab1(f1, f2, f3) t1 select f1, f2, f3 from rem_tab@mydblink t1_rem
where t1_rem.f1 = t1.f1;

The above does not work. Gives error:
ORA-22992: cannot use LOB locators selected from remote table.

Is it possible to make the 2nd query work?
(I know I could create a view of the table at the remote database end and then select from that view instead of the table -- this solution works but it is not an option for me)
Oracle DatabaseDatabases

Avatar of undefined
Last Comment
toooki

8/22/2022 - Mon
Wasim Akram Shaik

take an export from the database where you want the data and import it where ever you need.. you will not have that problem then..!!!
toooki

ASKER
I need this as part of a procedure that I want to call almost daily once. So export/import does not work for me.

Everything works except for the CLOB data type that breaks the matter.
ASKER CERTIFIED SOLUTION
Wasim Akram Shaik

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
toooki

ASKER
Thank you !
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck