Using the Data Interface on Remote LOBs

9.2 Using the Data Interface on Remote LOBs

The data interface enables you to bind and define a CHARACTER buffer for a CLOB column and a RAW buffer for a BLOB column. This interface is supported for remote LOB columns too.

The advantage of using the data interface over using LOB locators is that it makes only one round-trip to the remote server to fetch the LOB data. If used in as part of an array bind or define, it will use only one round-trip for the entire array operation.

The examples discussed in the book use the print_media table created in the following two schemas: dbs1 and dbs2. The CLOB column of the print_media table used in the examples shown is ad_finaltext. The examples provided for PL/SQL, OCI, and Java in the following sections use binds and defines for this one column, but multiple columns can also be accessed. Following is the functionality supported:

You can bind and define a CLOB as VARCHAR2 or LONG, and a BLOB as a RAW or a LONG or a RAW.
Array binds and defines are supported.

This section describes how to use the remote data interface with LOBs in PL/SQL.

The data interface only supports data of size less than 32KB in PL/SQL. The following snippet shows a PL/SQL example:

CONNECT pm/pm
declare
  my_ad varchar(6000) := lpad('b', 6000, 'b');
BEGIN
  INSERT INTO print_media@dbs2(product_id, ad_id, ad_finaltext) 
       VALUES (10000, 10, my_ad);
  -- Reset the buffer value
  my_ad := 'a';
  SELECT ad_finaltext INTO my_ad FROM print_media@dbs2 
       WHERE product_id = 10000;
END;
/

If ad_finaltext were a BLOB column instead of a CLOB, my_ad has to be of type RAW. If the LOB is greater than 32KB - 1 in size, then PL/SQL raises a truncation error and the contents of the buffer are undefined.

This section demonstrates how to use the remote data interface with LOBs in JDBC.

The following code snippets work with all JDBC drivers:

Bind:

This is for the non-streaming mode:

...
String sql = "insert into print_media@dbs2 (product_id, ad_id, ad_final_text)" +
             " values (:1, :2, :3)";
    PreparedStatement pstmt = conn.prepareStatement(sql);
    pstmt.setInt( 1, 2 );
    pstmt.setInt( 2, 20);
    pstmt.setString( 3, "Java string" );
    int rows = pstmt.executeUpdate();
...

Note: Oracle supports the non-streaming mode for strings of size up to 2 GB. However, the memory size of your computer may be a limiting factor.

For the streaming mode, the same code as the preceding works, except that the setString() statement is replaced by one of the following:

pstmt.setCharacterStream( 3, new LabeledReader(), 1000000 );
pstmt.setAsciiStream( 3, new LabeledAsciiInputStream(), 1000000 );

Note: You can use the streaming interface to insert Gigabyte sized character and binary data into a LOB column.

Here, LabeledReader() and LabeledAsciiInputStream() produce character and ASCII streams respectively. If ad_finaltext were a BLOB column instead of a CLOB, then the preceding example works if the bind is of type RAW:

pstmt.setBytes( 3, <some byte[] array> );

pstmt.setBinaryStream( 3, new LabeledInputStream(), 1000000 );

Here, LabeledInputStream() produces a binary stream.

Define:

For non-streaming mode:

OracleStatement stmt = (OracleStatement)(conn.createStatement());
  stmt.defineColumnType( 1, Types.VARCHAR );
  ResultSet rst = stmt.executeQuery("select ad_finaltext from print_media@dbs2" );
  while( rst.next() )
     {
       String s = rst.getString( 1 );
       System.out.println( s );
     }

Note: If the LOB size is greater than 32767 bytes, the data is truncated and no error is thrown.

For streaming mode:

OracleStatement stmt = (OracleStatement)(conn.createStatement());
  stmt.defineColumnType( 1, Types.LONGVARCHAR );
  ResultSet rs = stmt.executeQuery("select ad_finaltext from print_media@dbs2" );
  while(rs.next()) {  
    Reader reader = rs.getCharacterStream( 1 );  
    int data = 0;  
    data = reader.read();   
    while( -1 != data ){    
      System.out.print( (char)(data) );    
      data = reader.read();  
    }  
    reader.close();
  }

Note: Specifying the datatype as LONGVARCHAR lets you select the entire LOB. If the define type is set as VARCHAR instead of LONGVARCHAR, the data will be truncated at 32k.

If ad_finaltext were a BLOB column instead of a CLOB, then the preceding examples work if the define is of type LONGVARBINARY:

...
   OracleStatement stmt = (OracleStatement)conn.createStatement();
 
   stmt.defineColumnType( 1, Types.INTEGER );
   stmt.defineColumnType( 2, Types.LONGVARBINARY );
 
   ResultSet rset = stmt.executeQuery("SELECT ID, LOBCOL FROM LOBTAB@MYSELF");
 
   while(rset.next())
    {
     /* using getBytes() */
     /*
     byte[] b = rset.getBytes("LOBCOL");
     System.out.println("ID: " + rset.getInt("ID") + "  length: " + b.length);
     */
 
        /* using getBinaryStream() */
        InputStream byte_stream = rset.getBinaryStream("LOBCOL");
        byte [] b = new byte [100000];
        int b_len = byte_stream.read(b);
        System.out.println("ID: " + rset.getInt("ID") + "  length: " + b_len);
 
        byte_stream.close();
    }
...

This section demonstrates how to use the remote data interface with LOBs in OCI.

The data interface only supports data of size less than 2 gigabytes (the maximum value possible of a variable declared as sb4) for OCI. The following pseudocode can be enhanced to be a part of an OCI program:

...
text *sql = (text *)"insert into print_media@dbs2
                    (product_id, ad_id, ad_finaltext) 
                    values (:1, :2, :3)";
OCIStmtPrepare(...);
OCIBindByPos(...); /* Bind data for positions 1 and 2
                     * which are independent of LOB */
OCIBindByPos(stmthp, &bndhp[2], errhp, (ub4) 3, 
             (dvoid *) charbuf1, (sb4) len_charbuf1, SQLT_CHR, 
             (dvoid *) 0, (ub2 *)0, (ub2 *)0, 0, 0, OCI_DEFAULT);
OCIStmtExecute(...);

...

text *sql = (text *)"select ad_finaltext from print_media@dbs2
                    where product_id = 10000";
OCIStmtPrepare(...);
OCIDefineByPos(stmthp, &dfnhp[2], errhp, (ub4) 1, 
             (dvoid *) charbuf2, (sb4) len_charbuf2, SQLT_CHR, 
             (dvoid *) 0, (ub2 *)0, (ub2 *)0, OCI_DEFAULT);
OCIStmtExecute(...);
...

For a BLOB column, you must use the SQLT_BIN type. For example, if you define the ad_finaltext column as a BLOB column instead of a CLOB column, then you must bind and define the column data using the SQLT_BIN type. If the LOB is greater than 2GB - 1 bytes in size, then OCI raises a truncation error and the contents of the buffer are undefined.

This section discusses the restrictions on the usage of Data Interface on Remote LOBs.

Certain syntax is not supported for remote LOBs.

Queries involving more than one database are not supported:

SELECT t1.lobcol, a2.lobcol FROM t1, t2.lobcol@dbs2 a2 WHERE 
LENGTH(t1.lobcol) = LENGTH(a2.lobcol);

Neither is this query (in a PL/SQL block):

SELECT t1.lobcol INTO varchar_buf1 FROM t1@dbs1
UNION ALL
SELECT t2.lobcol INTO varchar_buf2 FROM t2@dbs2;

Only binds and defines for data going into remote persistent LOB columns are supported, so that parameter passing in PL/SQL where CHAR data is bound or defined for remote LOBs is not allowed because this could produce a remote temporary LOB, which are not supported. These statements all produce errors:
```
SELECT foo() INTO varchar_buf FROM table1@dbs2; -- foo returns a LOB

SELECT foo()@dbs INTO char_val FROM DUAL; -- foo returns a LOB

SELECT XMLType().getclobval INTO varchar_buf FROM table1@dbs2;
```

If the remote object is a view such as

CREATE VIEW v AS SELECT foo() a FROM ... ; -- foo returns a LOB
/* The local database then tries to get the CLOB data and returns an error */
SELECT a INTO varchar_buf FROM v@dbs2;

This returns an error because it produces a remote temporary LOB, which is not supported.

RETURNING INTO does not support implicit conversions between CHAR and CLOB.
PL/SQL parameter passing is not allowed where the actual argument is a LOB type and the remote argument is a VARCHAR2, NVARCHAR2, CHAR, NCHAR, or RAW.