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

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

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

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

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.