10.1 Working with Remote LOBs in SQL and PL/SQL
This section describes the SQL and PL/SQL functions that are supported on remote LOBs.
SQL Functions
All the SQL built-in functions and user-defined functions that are supported on local LOBs and BFILEs, are also supported on remote LOBs and BFILEs, as long as the final value returned by the nested functions is not a LOB type. This includes functions for remote persistent and temporary LOBs and for BFILEs.
Most of the examples in the following sections use
print_media
table. Following is the structure of the
table:
![print_media table print_media table](img/print_media_lob_v2.png)
Built-in SQL functions, which are executed on a remote site, can
be part of any SQL statement, like SELECT
, INSERT
,
UPDATE
, and DELETE
. For
example:
SELECT LENGTH(ad_sourcetext) FROM print_media@remote_site -- CLOB
SELECT LENGTH(ad_fltextn) FROM print_media@remote_site; -- NCLOB
SELECT LENGTH(ad_composite) FROM print_media@remote_site; -- BLOB
SELECT product_id from print_media@remote_site WHERE LENGTH(ad_sourcetext) > 3;
UPDATE print_media@remote_site SET product_id = 2 WHERE LENGTH(ad_sourcetext) > 3;
SELECT TO_CHAR(foo@dbs2(...)) FROM dual@dbs2;
-- where foo@dbs2 returns a temporary LOB
PL/SQL functions
Built-in and user-defined PL/SQL functions that are executed on the remote site and operate on remote LOBs and BFILEs are allowed, as long as the final value returned by nested functions is not a LOB.
SELECT product_id FROM print_media@dbs2 WHERE foo@dbs2(ad_sourcetext, 'aa') > 0;
-- foo is a user-define function returning a NUMBER
DELETE FROM print_media@dbs2 WHERE DBMS_LOB.GETLENGTH@dbs2(ad_graphic) = 0;
Restrictions on Remote User Defined Functions
The SQL and PL/SQL functions fall under the following non-comprehensive list of categories:
- SQL functions that are not supported on LOBs
The SQL functions like the
DECODE
function, which are not supported for LOBs, are not supported on remote LOBs as well. - Functions that accept exactly one LOB argument (where all the other arguments are of non-LOB data types) and does not return a LOB
The functions, like the LENGTH function, are supported. For example:
SELECT LENGTH(ad_composite) FROM print_media@remote_site; SELECT LENGTH(ad_header.logo) FROM print_media@remote_site; -- LOB in object SELECT product_id from print_media@remote_site WHERE LENGTH(ad_sourcetext) > 3;
-
Functions that return a LOB
These functions may return the original LOB or produce a temporary LOB. These functions can be performed on the remote site, as long as the result returned to the local site is not a LOB.
- Functions returning a temporary LOB are:
REPLACE
,SUBSTR
,CONCAT
, ||,TRIM
,LTRIM
,RTRIM
,LOWER
,UPPER
,NLS_LOWER
,NLS_UPPER
,LPAD
, andRPAD
. - Functions returning the original LOB locator are:
NVL
,DECODE
, andCASE
.
For example, the following statements are supported:
SELECT TO_CHAR(CONCAT(ad_sourcetext, ad_sourcetext)) FROM print_media@remote_site; SELECT TO_CHAR(SUBSTR(ad_fltextnfs, 1, 3)) FROM print_media@remote_site;
But the following statements are not supported:
SELECT CONCAT(ad_sourcetext, ad_sourcetext) FROM print_media@remote_site; SELECT SUBSTR(ad_sourcetext, 1, 3) FROM print_media@remote_site;
- Functions returning a temporary LOB are:
-
Functions that take in more than one LOB argument:
These are:
INSTR
,LIKE
,REPLACE
,CONCAT
, ||,SUBSTR
,TRIM
,LTRIM
,RTRIM
,LPAD
, andRPAD
. All these functions are relevant only forCLOB
s andNCLOB
s.These functions are supported only if all the LOB arguments are on the same
dblink
. For example, the following is supported:SELECT TO_CHAR(CONCAT(ad_sourcetext, ad_sourcetext)) FROM print_media@remote_site; -- CLOB SELECT TO_CHAR(CONCAT(ad_fltextn, ad_fltextn)) FROM print_media@remote_site; -- NCLOB
But the following is not supported
SELECT TO_CHAR(CONCAT(a.ad_sourcetext, b.ad_sourcetext)) FROM print_media@db1 a, print_media@db2 b WHERE a.product_id = b.product_id;
- PL/SQL functions operating on LOBs:
A function in one
dblink
cannot operate on LOB data in another dblink. For example, the following statement is not supported:SELECT a.product_id FROM print_media@dbs1 a, print_media@dbs2 b WHERE CONTAINS@dbs1(b.ad_sourcetext, 'aa') >0;
- Multiple LOBs in a query block:
One query block cannot contain tables and functions at different
dblinks
. For example, the following statement is not supportedSELECT a.product_id FROM print_media@dbs2 a, print_media@dbs3 b WHERE CONTAINS@dbs2(a.ad_sourcetext, 'aa') > 0 AND foo@dbs3(b.ad_sourcetext) > 0; -- foo is a user-defined function in dbs3
- LOB operators and columns are supported if they are in a
SELECT
list andwhere
clause in a join query. - Oracle-provided PL/SQL functions and procedures can return LOB locators.
- Only remote LOBs support SQL operators returning temporary LOBs.
- Only the views supplied by Oracle, support returning LOBs.
Parent topic: Distributed LOBs