Working with Remote LOBs in SQL and PL/SQL

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:

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, and RPAD.
- Functions returning the original LOB locator are: NVL, DECODE, and CASE.
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 that take in more than one LOB argument:

These are: INSTR, LIKE, REPLACE, CONCAT, ||, SUBSTR, TRIM, LTRIM, RTRIM, LPAD, and RPAD. All these functions are relevant only for CLOBs and NCLOBs.

These functions are supported only if all the LOB arguments are in the same dblink, and the value returned is not a LOB. 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;
```
PLSQL 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 supported

SELECT 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