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)
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)
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..!!!
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.
Everything works except for the CLOB data type that breaks the matter.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you !