Skip to main content

Search

Welcome to Tibero GTS!

Querying CLOB and BLOB through DBLINK

Document Type | Technical Information

Category | Utility

Document Number | TUTTI012

 

Overview

This guide explains the issue encountered when querying CLOB and BLOB data through DBLINK in a Tibero environment, and how to work around it.
 
After bringing LOB data to the local DB using the INSERT ~ SELECT * FROM table@DBLINK syntax, the query is performed. The example tests this using two servers (TIBERO1, TIBERO2) and DBLINK.

Note

The test was conducted in the following environment.
  • [tibero@T1:/]$ tbboot -v
  • Tibero 6 (DB 6.0 FS06_CS_1703) Linux stopbugs 2.6.9-89.ELsmp #1 SMP Mon Jun 22 12:31:33 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux version (little-endian)
  • Patch files (none)

 

 

Method

1. (TIBERO1) Create Test Table

conn sys/tibero@tibero_v

drop table tibero.bb1;
create table tibero.bb1(c1 number, c2 clob, c3 blob);
desc tibero.bb1;

COLUMN_NAME   TYPE    CONSTRAINT
------------  ------- --------------------------
C1            NUMBER
C2            CLOB
C3            BLOB

 

2. (TIBERO1) Insert Data into Test Table

insert into tibero.bb1 values(1,null,null);
insert into tibero.bb1 values(2,empty_clob(),empty_blob());
insert into tibero.bb1 values(3,'test_record_3','48656C6C6F'||UTL_RAW.CAST_TO_RAW(' World!'));
commit;


3. (TIBERO1) Query Data from Test Table

Confirm that LOB data is not displayed.
SQL> col c3 for a20
SQL> col c2 for a20
SQL> select c1,c2,utl_raw.cast_to_varchar2(dbms_lob.substr(c3,20,1)) c3 from tibero.bb1;

C1         C2                   C3
---------- -------------------- --------------------
1
2
3          test_record_3        Hello World!


4. (TIBERO2) Create DBLINK

Add the TIBERO 1 server connection information to the tbdsn.tbr file on the TIBERO 2 server under the name 'tibero_v', then execute as follows.
conn sys/tibero;
Connected to Tibero.

drop public database link p_remote;
create public database link p_remote connect to tibero identified by tmax using 'tibero_v';

SQL> select owner, db_link, host from dba_db_links;

OWNER      DB_LINK         HOST
---------- --------------- ----------
PUBLIC     P_REMOTE        tibero_v


5. (TIBERO 2) Query LOB Data Using DBLINK

You can see that error TBR-26020 occurs.
select c1,c2,utl_raw.cast_to_varchar2(dbms_lob.substr(c3,20,1)) c3 from bb1@p_remote;

TBR-26020: Cannot read a LOB object in a remote server.


6. (TIBERO 2) Create Table and Insert Data

Bring the LOB data from the remote DB to the local DB.
drop table tibero.bb1;
create table tibero.bb1 as select * from bb1@p_remote where 1=2;
insert into tibero.bb1 select * from bb1@p_remote;
commit;

 

7. (TIBERO 2) Query LOB Data

Perform LOB data query on the local DB.
SQL> col c2 for a20
SQL> col c3 for a20
SQL> select c1,c2,utl_raw.cast_to_varchar2(dbms_lob.substr(c3,20,1)) c3 from tibero.bb1;

C1         C2                   C3
---------- -------------------- --------------------
1
2
3          test_record_3        Hello World!

 

 

 

Related to