Oracle DBLink access Lob field ORA-22992 Solution

This test shows how to access a table containing Blob fields through DBLink.

I. Simulated Problems

1.1In the instance1Operation on:

Create a test table containing blob:

1. /* FormattedOn10:18:05 (QP5 v5.185.11230.41888 )*/
2. CREATE TABLELob1
3. (
4. Line NUMBERPrimary Key,
5. Text CLOB
6. );
8. INSERT INTOLob1
9. SELECT DistinctLine, textFROMAll_sourceWhereRownum: <500;
11. SELECTSegment_name,
12. Segment_type,
13. Tablespace_name,
14. SUM(Bytes)/1024/1024 |'M' AS "SIZE"
15. FROMUser_segments
16. WHERESegment_name ='Lob1'
17. GROUP BYSegment_name, segment_type, tablespace_name;

The LOB table information is as follows:

1. SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAMESIZE
2. -------------------------------------------------------------------------
3. LOB1TABLESYSTEM 9 M
5. SQL>SetWrapOff;
6. SQL>Select*FromLob1WhereRownum = 1;
8. LINE TEXT
9. -------------------------------------------------------------------------------
10. 1 package STANDARD AUTHIDCURRENT_USER Is-- Care

1.2In the instance2On

Create DBLINK:

1. CREATE PUBLIC DATABASELINK lob_linkCONNECT TODave IDENTIFIEDBYDave
2. USING '(DESCRIPTION =
3. (ADDRESS_LIST =
4. (ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.2.222) (PORT = 1521 ))
5. )
6. (CONNECT_DATA =
7. (SERVICE_NAME = dave)
8. )
9. )';
11. SQL>Select*FromV $ version;
13. BANNER
14. ----------------------------------------------------------------
15. OracleDatabase10g Enterprise Edition Release 10.2.0.1.0-Prod
16. PL/SQL Release 10.2.0.1.0-Production
17. CORE 10.2.0.1.0 Production
18. TNSForLinux: Version 10.2.0.1.0-Production
19. NLSRTL Version 10.2.0.1.0-Production
21. SQL>Select Count(*)FromLob1 @ lob_link;
22. COUNT(*)
23. ----------
24. 58228

The total number of records queried here is normal. However, when we query the content in a specific LOB field, an error is reported as follows:

1. SQL>Select*FromLob1 @ lob_linkWhereRownum = 1;
2. ERROR:
3. ORA-22992: cannot use LOB locators selectedFromRemote tables
5. No RowsSelected
7. [Oracle @ localhost ~] $ Oerr ora 1, 22992
8. 22992,000 00,"Cannot use LOB locators selected from remote tables"
9. // * Cause: A remote LOBColumnCannot be referenced.
10. //*Action: RemoveReferences ToLOBsInRemote tables.

1 2 3 4 Next Page