This section discusses the bind values for queries and DML statements.

For the Queries and DMLs (INSERT, UPDATE, DELETE) with bind values, the following four cases are possible. The first case involves local tables and locators and is the standard LOB functionality, while the other three cases are part of the distributed LOBs functionality and have restrictions listed at the end of this section.

• Local table with local locator as bind value.

• Local table with remote locator as bind value

• Remote table with local locator as bind value

• Remote table with remote locator as bind value

Queries of the following form which use remote lob locator as bind value are supported:

SELECT name FROM lob_tab@db2 WHERE length(c1)=length(:lob_v1);

In the above query, c1 is an LOB column and lob_v1 is a remote locator.

DMLs of the following forms using a remote LOB locator will be supported. Here, the bind values can be local or remote persistent LOB locators.

UPDATE lob_tab@db2 SET c1=:lob_v1;

INSERT into lob_tab@db2 VALUES (:1, :2);

Var lob1 CLOB;
BEGIN
    SELECT c2 INTO lob1 FROM lob_tab@db2 WHERE c1=1;
END;
/
SELECT LENGTH(:lob1) FROM DUAL;

This section demonstrates the usage of remote locator in PL/SQL with an example.

DECLARE 
    substr_data VARCHAR2(4000); 
    remote_loc CLOB; 
BEGIN 
    SELECT c2 into remote_loc 
    FROM lob_tab@db2 WHERE c1=1; 
    substr_data := substr(remote_loc, position, length) 
END;

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;
/

Most OCILOB APIs support operations on remote LOB locators. The following list of OCILOB functions returns an error when a remote LOB locator is passed to them:

The following example shows how to pass a remote locator to OCILOB API.

void select_read_remote_lob()
{
  text *select_sql = (text *)"SELECT c2 lob_tab@dbs1 where c1=1";
  ub4 amtp = 10;
  ub4 nbytes = 0;
  ub4 loblen=0;
  OCILobLocator * one_lob;
  text strbuf[40];

 /* initialize single locator */
 OCIDescriptorAlloc(envhp, (dvoid **) &one_lob,
                 (ub4) OCI_DTYPE_LOB,
                 (size_t) 0, (dvoid **) 0)

 OCIStmtPrepare(stmthp, errhp, select_sql, (ub4)strlen((char*)select_sql),
                 (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);

 OCIDefineByPos(stmthp, &defp, errhp, (ub4) 1,
                     (dvoid *) &one_lob,
                     (sb4) -1,
                     (ub2) SQLT_CLOB,
                     (dvoid *) 0, (ub2 *) 0,
                     (ub2 *) 0, (ub4) OCI_DEFAULT));

 /* fetch the remote locator into the local variable one_lob */
 OCIStmtExecute(svchp, stmthp, errhp, 1, 0, (OCISnapshot *)0, 
                 (OCISnapshot *)0, OCI_DEFAULT);

 /* Get the length of the remote LOB */
 OCILobGetLength(svchp, errhp,
                (OCILobLocator *) one_lob, (ub4 *)&loblen)

 printf("LOB length = %d\n", loblen);

 memset((void*)strbuf, (int)'\0', (size_t)40);

 / * Read the data from the remote LOB */
 OCILobRead(svchp, errhp, one_lob, &amtp,
                (ub4) 1, (dvoid *) strbuf, (ub4)& nbytes, (dvoid *)0,
                (OCICallbackLobRead) 0,
                (ub2) 0, (ub1) SQLCS_IMPLICIT));
 printf("LOB content = %s\n", strbuf);

}

Remote LOB locators have the following restrictions:

• You cannot select a remote temporary LOB locator into a local variable using the SELECT statement. For example,

  select substr(c2, 3, 1) from lob_tab@db2 where c1=1

  The preceding query returns an error.

• Remote LOB functionality is not supported for Index Organized tables (IOT). An attempt to get a locator from a remote IOT table will result in an error.

• Both the local database and the remote database have to be of Database release 12.2 or higher version.

• With distributed LOBs functionality, the tables that you use in the from clause or where clause should be collocated on the same database. If you use emote locators as bind variables in the where clauses, then they should belong to the same remote database. You cannot have one locator from one database (say, DB1) and another locator from another database (say, DB2) to be used as bind variables.

• Collocated tables or locators use the same database link. It is possible to have two different DB Links pointing to the same database. In the following example, both dblink1 and dblink2 point to the same remote database, but with different authentication methods. Oracle Database does not support such operations.

  INSERT into tab1@dblink1 SELECT * from tab2@dblink2;

• Any DBMS_LOB or OCILob APIs that accept two locators must obtain both the LOB locators through the same database link. Operations, as specified in the following example, are not supported:

  SELECT ad_sourcetext INTO clob1 FROM print_media@db1 WHERE product_id = 10011;
  SELECT ad_sourcetext INTO clob2 FROM print_media@db2 WHERE product_id = 10011;
  DBMS_LOB.COPY(clob1, clob2, length(clob2));

• Bind values should be of the same LOB type as the column LOB type. For example, you must bind NCLOB locators to NCLOB columns and CLOB locators to CLOB columns. Implicit conversion between NCLOB and CLOB types is not supported in case of remote LOBs.

• DML statements with Array Binds are not supported when the bind operation involves a remote locator, or if the table involved is a remote table.

• You cannot select a BFILE column from a remote table into a local variable.