Query the table with the Clob column from the remote database

 

When a table containing a CLOB column is queried with a dblink from a remote database, it returns an error as ORA-22992. A table with a clob column can be queried from the remote database with two different method without this error.

When you query with dblink, the error will be taken as below.

ORA-22992: cannot use LOB locators selected from remote tables

First Method:

We create a view that queries the clob area with dbms_lob.substr in the database. Then we can query through this view without error from the remote database.

We create the view as below:

You can query this view from other database with dblink without error:

Second Method:

Create a global temp table in the remote database and insert the data in the database that we want to query into this global temp table. We can then query this global temp table.

We are creating a Temp table in the Remote database:

Insert data into the temp table:

Query the Temp table:

As can be seen, it is possible to query tables that contain a clob column from a remote database.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *