Oracle Table joins with remote database tables with BLOB field

toooki
toooki used Ask the Experts™
on
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)
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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..!!!

Author

Commented:
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.
then check this out.. this link may help you..

you may need to create a view as suggested in the tom kyte blog

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:52297289480186

Author

Commented:
Thank you !

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial