You can select a persistent LOB locator from a remote table into a local variable and this can be done in PL/SQL or in OCI. The remote columns can be of type BLOB, CLOB or NCLOB. The following SQL statement is the basis for all the examples with remote LOB locator in this chapter.

CREATE TABLE lob_tab (c1 NUMBER, c2 CLOB);

In the following example, the table lob_tab (with columns c2 of type CLOB and c1 of type number) defined in the remote database is accessible using database link db2 and a local CLOB variable lob_var1.

SELECT c2 INTO lob_var1 FROM lob_tab@db2 WHERE c1=1;
SELECT c2 INTO lob_var1 FROM lob_tab@db2 WHERE c1=1 for update;

In PL/SQL, the function dbms_lob.isremote can be used to check if a particular LOB belongs to a remote table. Similarly, in OCI, you can use the OCI_ATTR_LOB_REMOTE attribute of OCILobLocator to check if a particular LOB belongs to a remote table. For example,

IF(dbms_lob.isremote(lob_var1)) THEN
dbms_output.put_line(‘LOB locator is remote)
ENDIF; 

Topics:

• Using Local and Remote locators as bind with queries and DML on remote tables

• Restrictions when using remote LOB locators

Any 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 nested functions is not a LOB type. This includes functions for remote persistent and temporary LOBs and for BFILEs.

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

The SQL functions fall under the following (not necessarily exclusive) categories:

• SQL functions that are not supported on LOBs:

  These functions are relevant only for CLOBs: an example is DECODE.

  These functions cannot be supported on remote LOBs because they are not supported on local LOBs.

• Functions taking exactly one LOB argument (all other arguments are of other data types) and not returning a LOB:

  These functions are relevant only for CLOBs, NCLOBs, and BLOBs: an example is LENGTH and it is 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:

  All these functions are relevant only for CLOBs and NCLOBs. 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. Note that even though DECODE and CASE are not supported currently to operate on LOBs, they could operate on other data types and return a LOB.

  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;
  

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.

The following example uses the print_media table described in "Table for LOB Examples: The PM Schema print_media Table"

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;

All DBMS_LOB APIs other than the APIs targeted for BFILEs support operations on remote LOB locators.

The following example shows how to pass remote locator as input to dbms_lob operations.

DECLARE
  lob CLOB;
  buf VARCHAR2(120) := 'TST';
  amt NUMBER(2);
  len NUMBER(2);
BEGIN
  amt :=30;
  select c2 into lob from lob_tab@db2 where c1=3 for update;
  dbms_lob.write(lob, amt, 1, buf);
  amt :=30;
  dbms_lob.read(lob, amt, 1, buf);
  len := dbms_lob.getlength(lob);
  dbms_output.put_line(buf);
  dbms_output.put_line(amt);
  dbms_output.put_line('get length output = ' || len);
END;
/

Topics: