Oracle accesses the LOB field of the remote database through DBLink to report ORA-22992 solution

Oracle accesses the LOB field of the remote database through DBLink to report ORA-22992 solution
the
Recently, I am working on a photo collection function. The photo collection is in the external network database. After the external network collects the photos, the internal network will read the photos in the external network database (the photo field is BLOB type). If the internal network directly passes select If the statement queries the photos of the external network library, the error of ORA-22992: cannot use LOB locators selected from remote tables will be reported. I checked the solution online and recorded it for later viewing.
Method 1: www.2cto.com
the
Create a table containing large fields (BLOB) on the intranet, and then insert the data into the intranet database table through insert into … select … from …@dblink, and directly operate the intranet database table. like:
the
Create table:
[sql]
SQL>create table inner_table select *from outer_table@dblink
Insert data:
the
[sql]
SQL>insert into inner_table select *from outer_table@dblink
In this way, the data in the outer table outer_table is written to the inner table inner_table.
www.2cto.com
Method Two:
the
Create a global temporary table locally that is the same as the remote side of dblink, and then query the temporary table:
the
— Create a temporary table:
the
[sql]
SQL>create global temporary table tem_table( … ) on commit delete rows;
Insert data:
the
[sql]
SQL> insert into tem_table select * fromouter_table@dblink;
xxx rows created.
In this way, the data is written to the temporary table, but the data is deleted after submission (the characteristics of the temporary table). the
In fact, these two methods are similar, except that one uses a temporary table and the other uses a permanent table.
the
the
Author mhmyqn