Solution to ORA-22992 reported by Oracle LOB field for accessing remote database via DBLink

Recently, I am working on a photo collection function. The photo collection function is an Internet library. After the Internet collects the photos, the Intranet reads the photos in the Internet library (the photo field is BLOB type), such

Recently, I am working on a photo collection function. The photo collection function is an Internet library. After the Internet collects the photos, the Intranet reads the photos in the Internet library (the photo field is BLOB type), such

Recently, I am working on a photo collection function. The photo collection function is an Internet library. After the Internet collects the photos, the Intranet reads the photos in the Internet library (the photo field is BLOB type ), if the Intranet directly queries the photos of the Internet library through the select statement, the ORA-22992: cannot use LOB locators selected from remote tables error is reported, the solution is checked online, recorded for later viewing.

Method 1:

Create a table with large fields (BLOB) in the Intranet, and then use inser... Select... From... @ Dblink: insert the data to the Intranet database table and directly operate the intranet database table. For example:

Create a table:

Insert data:

In this way, the data in the outer table outer_table is written to the inner_table in the Intranet table.

Method 2:

Create a global temporary table with the same remote end as dblink locally and query the temporary table:

-- Create a temporary table:

Insert data:

In this way, the data is written to the temporary table, but the data is deleted after submission (the feature of the temporary table ).
In fact, both methods are similar, except that a temporary table is used and a permanent table is used.