SELECT with a LOB and DBLink returns an ORA-22992: cannot use LOB locators selected from remote tables

Visit the Below Website to access unlimited exam questions for all IT vendors and Get Oracle Certifications for FREE
http://www.free-online-exams.com

Problem: SELECT with a LOB and DBLink returns an ORA-22992: cannot use LOB locators selected from remote tables

Symptoms:

Executing a SELECT that includes a LOB and uses a DBLink to another Database and returns the following error.

declare
v_blob blob;
begin
for rec in (select blob_field from BLOB_TEST@LOB_TEST_LINK) loop
v_blob := rec.blob_field;
end loop;
end ;
/

ERROR at line 1:
ORA-22992: cannot use LOB locators selected from remote tables
ORA-06512: at line 4

Cause

The error is expected because the use of DBLinks and LOBs via the SELECT from PL/SQL is not supported.

Note 119897.1 (ORA-22992 When Trying To Select Lob Columns Over A Database Link) states this feature is not supported.

Solution:

The following Notes discuss this issue, and give code snippets as example to work-around the restriction using the INTO clause. Keep in mind, if working with large LOB sizes, it may be best to implement the work-around of using a Materialized View.

Note 459557.1 (ORA-1406: Fetched Column Value was Truncated When Selecting Remote Column into Local BLOB Variable) discusses using a Materialized View in the scenarios where the BLOB will be larger than 2Meg.

Note 119897.1 (ORA-22992 When Trying To Select Lob Columns Over A Database Link) states this feature is not supported but then at the end of the Note it states starting with 10.2 there is a work-around of using LONG and LONG RAW types.

Note 436707.1 (Ora-22992 has a workaround in 10gR2) illustrates a work-around (using LONG RAW) as mentioned was possible in the previous note.

Note 796282.1 (Workaround for ORA-22992) provides 3 work-arounds.

The following code is one that illustrates a complete example which is not provided in the above listed documentation.

The following sample uses 2 schemas (Scott & Testcase). The Scott schema has the table with the Blob data. The Testcase schema has the DBLink and a procedure to Select the Blob (within the INTO clause which is the work-around) then insert it to a table.


As SCOTT - Create table and procedure to load BLOB data via files on the OS.

SQL> CREATE TABLE BLOB_TEST (FIELD1 NUMBER, BLOB_FIELD BLOB);

As SCOTT - Create a directory alias and procedure to load test data.

SQL> create directory BLOB_DIR as 'C:/';

CREATE OR REPLACE PROCEDURE LOAD_BLOB (IN_REC_NUM NUMBER, IN_FILE VARCHAR2) IS
lobd BLOB;
fils BFILE := BFILENAME('BLOB_DIR', IN_FILE); --'bigBold.bmp');
amt INTEGER;
BEGIN
DBMS_LOB.FILEOPEN(fils, dbms_lob.file_readonly);
amt:=DBMS_LOB.getlength(fils);
DBMS_OUTPUT.PUT_LINE('The BLOB File is of size: ' || amt);
insert into BLOB_TEST values (IN_REC_NUM, empty_blob()) returning BLOB_FIELD into lobd;
DBMS_LOB.LOADFROMFILE(lobd, fils, amt);
COMMIT;
DBMS_LOB.FILECLOSE(fils);
END;
/

As SCOTT - Load 3 files of varying sizes (less than 32k)

SQL> SET SERVEROUTPUT ON

SQL> EXECUTE LOAD_BLOB (1, 'SMALLBINARYFILE1.BMP');
The BLOB File is of size: 16190

PL/SQL procedure successfully completed.

SQL> EXECUTE LOAD_BLOB (2, 'SMALLBINARYFILE2.JPG');
The BLOB File is of size: 28668

PL/SQL procedure successfully completed.

SQL> EXECUTE LOAD_BLOB (3, 'SMALLBINARYFILE3.BMP');
The BLOB File is of size: 9950

PL/SQL procedure successfully completed.

SQL> COMMIT;

As SCOTT - Verify the Blob sizes just inserted

SQL> SELECT FIELD1, DBMS_LOB.GETLENGTH(BLOB_FIELD) LENGTH FROM BLOB_TEST;

FIELD1 LENGTH
---------- ----------
1 16190
2 28668
3 9950

As TESTCASE - Create table and DBLink to fetch BLOB data

SQL> CREATE TABLE BLOB_TEST_INSERT (FIELD1 NUMBER, BLOB_FIELD BLOB);

SQL> CREATE DATABASE LINK LOB_TEST_LINK CONNECT TO SCOTT IDENTIFIED BY TIGER USING 'G11';

As TESTCASE - Create the Stored Procedure that uses the INTO clause

CREATE OR REPLACE PROCEDURE SELECT_REMOTE_BLOB IS
v_Field1 NUMBER;
v_blob_field RAW (32765);
CURSOR C1 IS SELECT FIELD1, BLOB_FIELD FROM BLOB_TEST@LOB_TEST_LINK ORDER BY 1;
v_data BLOB ;
v_data_amount INTEGER;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO v_Field1, v_blob_field;
EXIT WHEN c1%NOTFOUND;
v_data_amount := UTL_RAW.LENGTH (v_blob_field);
DBMS_OUTPUT.PUT_LINE('Size of BLOB: ' || v_data_amount || ' bytes');
INSERT INTO BLOB_TEST_INSERT VALUES (v_Field1, EMPTY_BLOB()) RETURNING BLOB_FIELD INTO v_data;
DBMS_LOB.WRITE(v_data, v_data_amount, 1, v_blob_field);
END LOOP;
CLOSE C1;
COMMIT;
END;
/

As TESTCASE - Execute the procedure

SQL> SET SERVEROUTPUT ON

SQL> EXECUTE SELECT_REMOTE_BLOB;
Size of BLOB: 16190 bytes
Size of BLOB: 28668 bytes
Size of BLOB: 9950 bytes

PL/SQL procedure successfully completed.

As TESTCASE - Verify the Blob sizes

SQL> SELECT FIELD1, DBMS_LOB.GETLENGTH(BLOB_FIELD) LENGTH FROM BLOB_TEST_INSERT;

FIELD1 LENGTH
---------- ----------
1 16190
2 28668
3 9950

References:

SELECT with a LOB and DBLink returns an ORA-22992: cannot use LOB locators selected from remote tables [ID 1234893.1]

NOTE:119897.1 - ORA-22992 When Trying To Select Lob Columns Over A Database Link
NOTE:436707.1 - Ora-22992 has a workaround in 10gR2
NOTE:459557.1 - ORA-1406: Fetched Column Value was Truncated When Selecting Remote Column into Local BLOB Variable
NOTE:796282.1 - Workaround for ORA-22992

Get Oracle Certifications for all Exams
Free Online Exams.com