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
|
1 2 3 4 5 |
SQL> select msg from aduruoz.message@remote_db; ERROR: ORA-22992: cannot use LOB locators selected from remote tables no rows selected |
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:
|
1 2 3 |
SQL> CREATE OR REPLACE FORCE VIEW MESSAGE_VIEW (MSG)AS SELECT dbms_lob.substr(msg,4000,1) FROM ADURUOZ.MESSAGE; View created. |
You can query this view from other database with dblink without error:
|
1 2 3 4 5 |
SQL> select * from aduruoz.message_view@remote_db; MSG -------------------------------------------------------------------------------- This is a message |
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:
|
1 2 3 |
SQL> create global temporary table message_global ( msg clob ); Table created. |
Insert data into the temp table:
|
1 2 3 |
SQL> insert into message_global select msg from aduruoz.message@remote_db; 1 row created. |
Query the Temp table:
|
1 2 3 4 5 |
SQL> select * from message_global; MSG -------------------------------------------------------------------------------- This is a message |
As can be seen, it is possible to query tables that contain a clob column from a remote database.
