Oracle dblink access lob field ORA-22992 Solution

 

 

This test shows how to access a table containing BLOB fields through dblink. For more information about dblink and LOB, see the following link:

 

Oracle dblink

Http://blog.csdn.net/tianlesoftware/article/details/4698642

 

Remove dblink error ORA-02024: database link not found Solution

Http://blog.csdn.net/tianlesoftware/article/details/6160196

 

Oracle lob large object processing

Http://blog.csdn.net/tianlesoftware/article/details/5070981

 

Oracle lob details

Http://blog.csdn.net/tianlesoftware/article/details/6905406

 

I. Simulated Problems

 

1.1 operations on instance 1:

 

Create a test table containing BLOB:

/* Formatted on 2012/6/19 10:18:05 (QP5 v5.185.11230.41888) */CREATE TABLE lob1(   line   NUMBER primary key,   text   CLOB);INSERT INTO lob1   SELECT distinct line, text FROM all_source where rownum<500;SELECT segment_name,         segment_type,         tablespace_name,         SUM (bytes) / 1024 / 1024 || 'M' AS "SIZE"    FROM user_segments   WHERE segment_name = 'LOB1'GROUP BY segment_name, segment_type, tablespace_name;

The lob table information is as follows:

SEGMENT_NAME    SEGMENT_TYPE       TABLESPACE_NAME                SIZE--------------- ------------------ ------------------------------ ----------LOB1            TABLE              SYSTEM                         9MSQL> set wrap off;SQL> select * from lob1 where rownum=1;   LINE TEXT---------- ---------------------------------------------------------------------   1 package STANDARD AUTHID CURRENT_USER is              -- care

 
1.2 operate on instance 2

 

Create dblink:

CREATE PUBLIC DATABASE LINK lob_link CONNECT TO dave IDENTIFIED BY daveUSING '(DESCRIPTION =          (ADDRESS_LIST =           (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.2.222)(PORT = 1521))        )           (CONNECT_DATA =         (SERVICE_NAME = dave)     )  )';SQL> select * from v$version;BANNER----------------------------------------------------------------Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProdPL/SQL Release 10.2.0.1.0 - ProductionCORE    10.2.0.1.0      ProductionTNS for Linux: Version 10.2.0.1.0 - ProductionNLSRTL Version 10.2.0.1.0 - ProductionSQL> select count(*) from lob1@lob_link;  COUNT(*)----------     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:

SQL> select * from lob1@lob_link where rownum=1;ERROR:ORA-22992: cannot use LOB locators selected from remote tablesno rows selected[oracle@localhost ~]$ oerr ora 2299222992, 00000, "cannot use LOB locators selected from remote tables"// *Cause:  A remote LOB column cannot be referenced.// *Action:  Remove references to LOBs in remote tables.

 
Ii. Description on MOS

 

2.1 ORA-22992 when tryingto select lob columns over a database link [ID 119897.1]

In this article on the official website, the Oracle 8i-based explanation shows that the error is reported because this feature is not supported.

 

(A) You cannot actually select a lob column (I. e. clob column) from a table

Using remote database link. Thisis not a supported feature.

 

(B) Also, these are the invalid operations on a lob column:

-- The following operations are not supported either.

1. Select lobcol from Table1 @ remote_site;

2. insert into lobtable select type1.lobattr from Table1 @ remote_site;

3. Select dbms_lob.getlength (lobcol) from Table1 @ remote_site;

 

 

2.2 ORA-22992 in sqlusing dblink and nvl2 function in 10g. [ID 427239.1]

 

In Oracle 9i/10g, The bug.5185187 exists, so when using the nvl2 function in dblink, A ORA-22992 error occurs.

This bug has been fixed in Oracle 11gr2.

 

You can also use the following method to indirectly solve this problem:

Original SQL:

Select nvl2 ('A', 'B', 'C') from dual @ test;

Modified SQL:

Selectto_char (nvl2 ('A', 'B', 'C') from dual @ test;

 

 

 

2.3 select with a lob anddblink returns an ORA-22992: cannot use lob locators selected from remotetables [ID 1234893.1]

This article also mentions the cause: Because the use ofdblinks and lobs via the select from PL/SQL is not supported.

 

This article also provides some solutions:

The followingnotes discuss this issue, and give code snippets as example to work-around und therestriction using the into clause. keep in mind, if working with large lobsizes, it may be best to implement the work-around of using a materializedview.

-- If the lob field is large, it is best to use the materialized view to solve this problem.

 

Note 459557.1 (ORA-1406: fetchedcolumn value was truncated when selecting remote column into local blobvariable) discusses using a materialized view in the scenarios where the blobwill be larger than 2meg.

 

Note 119897.1 (ORA-22992 when trying to selectlob columns over a database link) states this feature is not supported but thenat The End Of The note it states starting with 10.2 there is a work-around und ofusing long and long raw types.

Note 436707.1 (Ora-22992 has a workaround in10gr2) extends strates a work-around (using long raw) As mentioned was possible inthe previous note.

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

 

Iii. Solution

 

3.1 Save the received lob to a char (cbob) or raw (BLOB) local variable.

Mos article:

Ora-22992 has a workaround in 10gr2 [ID 436707.1]

Workaround for ORA-22992 [ID 796282.1]

 

Starting from 10g the select from alob object through a database link is supportedby inserting ing the lob objects into variables defined as char orraw.

-- Starting from Oracle10g, dblink select can be received by local Char or raw type variables.

 

(1) Selecting a clob objectthrough the dblink:

 

Set serveroutput on

Declare
My_ad varchar (2000 );
Begin
Select OBJ into my_ad from test @ torem where id = 1;
Dbms_output.put_line (my_ad );
End;
/

 

The test environment here is cblob. The example is as follows:

SQL> declare

2 my_ad varchar (2000 );

3 begin

4 select text into my_ad fromlob1 @ lob_link where rownum = 1;

5 dbms_output.put_line (my_ad );

6 end;

/

7

Package standard authidcurrent_user is -- careful onthis line;

Sed edit occurs!

 

PL/SQL procedure successfully completed.

-- The content in clob is output here.

 

(2) Selecting a BLOB Object through thedblink:

 

Declare
My_ad raw (50 );
Begin
Select OBJ into my_ad from Test2 @ torem where id = 1;
End;
/

 

3.2 use materialized views

Mos article: ORA-1406: fetched column value was truncated when selecting remotecolumn into local blob variable [ID 459557.1]

 

In 3.1, we can use local variables to receive the lob value in dblink, but this method is limited. When the value of the received lob field is greater than 32 KB, the following error is reported:

ORA-01406: fetched column value was truncated

 

"If the lobis greater than 32kb-1 in size, then PL/SQL will raise a truncation error andthe contents of the buffer are undefined ."

This means thatwe are not able to retrieve blobs columns greater than 32kb-1 in size througha database link.

The restrictionstill holds good for 11g from oracledeskention securefiles and largeobjects developer's Guide

 

3.2.1 test the length of the lob Field

 

-- Remote creation process:

Create or replace procedure get_bloblengthas
Blob_loc blob;
Blob_length number;
Begin
Select <lob_column>
Into blob_loc
From <table_name>
Where name = '<critira> ';
Blob_length: = dbms_lob.getlength (blob_loc );
Dbms_output.put_line ('length of the column: '| to_char (blob_length ));
End;

 

-- Local call process:

Exec get_bloblength @ repb

 

If the returned value is greater than 32 KB, we can use the materialized view.

 

3.2.2 create a Materialized View

 

SQL> Create materializedview lobmv1 as select line, text from lob1 @ lob_link;

 

-- Query the materialized view: clob is displayed normally.

SQL> set wrap off;

SQL> select * From lobmv where rownum <5;

 

Line Text

-------------------------------------------------------------------------------

5

8 type number is number_base;

9 subtype float is number; -- number (126)

11 subtype "doubleprecision" is float;

 

3.3 copy a table containing the lob field to a local Global temporary table

 

Create a global temporary table that is the same as the remote database of dblink. Then, query the temporary table:

-- Create temporary table: Create global temporary table lob2 (line number primary key, text clob) on commit Delete rows; -- insert data: SQL> insert into lob2 select line, text from lob1 @ lob_link; 499 rows created. SQL> select * From lob2 where rownum <5; line text ---------- limit 5 8 type number is number_base; 9 subtype float is number; -- number (126) 11 subtype "do Uble precision "is float; SQL> commit; Commit complete. SQL> select * From lob2 where rownum <5; No rows selected -- the data is deleted after submission. This is the attribute of the temporary table.

 

 

 

 

Bytes -------------------------------------------------------------------------------------------------------

All rights reserved. reprinted articles are allowed, but source addresses must be indicated by links. Otherwise, the documents will be held legally responsible!

Skype: tianlesoftware

QQ: tianlesoftware@gmail.com

Email: tianlesoftware@gmail.com

Blog: http://www.tianlesoftware.com

WEAVER: http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

LinkedIn: http://cn.linkedin.com/in/tianlesoftware

 

 

------- Add a group to describe the relationship between Oracle tablespace and data files in the remarks section. Otherwise, reject the application ----

Dba1 group: 62697716 (full); dba2 group: 62697977 (full) dba3 group: 62697850 (full)

Super DBA group: 63306533 (full); dba4 group: 83829929 dba5 group: 142216823

Dba6 group: 158654907 dba7 group: 172855474 DBA group: 104207940