1. SecureFiles and Large Objects Developer's Guide
  2. Distributed LOBs
  3. Working with Remote Locators

10.3 Working with Remote Locators

You can select a persistent LOB locator from a remote table into a local variable and this can be done in any programmatic interface like PL/SQL, JDBC or 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;

See Also:

Parent topic: Distributed LOBs

10.3.1 Using Local and Remote Locators as Bind with Queries and DML on Remote Tables

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 a remote LOB locator as a 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 are supported. Here, the bind values can be local, remote persistent, or temporary LOB locators.

UPDATE lob_tab@db2 SET c1=:lob_v1;

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

You can pass a remote locator to most built-in SQL functions such as LENGTH, INSTR, SUBSTR, and UPPER. For example:
Var lob1 CLOB;
BEGIN
    SELECT c2 INTO lob1 FROM lob_tab@db2 WHERE c1=1;
END;
/
SELECT LENGTH(:lob1) FROM DUAL;

Note:

DMLs with returning clause are not supported on remote tables for both scalar and LOB columns.

Parent topic: Working with Remote Locators

10.3.2 Using Remote Locator

This section demonstrates the usage of remote locator in PL/SQL and with OCILOB API with examples.

A remote locator can be passed as a parameter to built in PL/SQL functions like LENGTH, INSTR, SUBSTR, UPPER and so on which accepts LOB as input. For 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:

  • OCILobLocatorAssign
  • OCILobArrayRead()
  • OCILobArrayWrite()
  • OCILobLoadFromFile2()

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

}

See Also:

OCI Programmer’s Guide, for the complete list of OCILOB APIs

Parent topic: Working with Remote Locators

10.3.3 Using Remote Locators with OCILOB API

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:

  • OCILobLocatorAssign
  • OCILobArrayRead()
  • OCILobArrayWrite()
  • OCILobLoadFromFile2()

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

}

See Also:

OCI Programmer’s Guide, for the complete list of OCILOB APIs

Parent topic: Working with Remote Locators

10.3.4 Restrictions when using remote LOB locators

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 remote 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.

Parent topic: Working with Remote Locators