Updating CLOB column in a UTF8 database

From: Phil Haigh <phil.haigh_at_fwpharma.com>
Date: Wed, 23 May 2001 21:00:50 +0100
Message-ID: <UFUO6.3877$8Z5.184841@monolith.news.easynet.net>

In Java (JDBC thin) I'm connecting to an Oracle 8.1.6 database created with the UTF8 character set. I create a record in a table with an empty CLOB and then re-read it FOR UPDATE with the intention of adding a text document to the CLOB. However, only the first character of the document gets there. If I run the code on an 8.1.6 database that does NOT have a UTF8 character set, this works. Same server, same Oracle home. Here's the test case I produced for Oracle Metalink. You'll need a table to try it out:

    create table clob_table ( file_name varchar2( 64 ), document_text clob );

This code was developed in JDeveloper 3.2.2:

package tar_1562384;

import java.sql.*;
import java.io.*;
import java.util.*;

import oracle.jdbc.driver.*;

/**
 * A Class class.
 * <P>
 * @author Phil Haigh
 */

public class Document extends Object {

  private static Connection theConnection;

  public static void main (String args []) throws Exception {     long length;
    connectToDatabase();
    length = load();
    System.out.println( "Execution completed successfully - CLOB is " + length + " characters long." );
  }

  public static long jsp() throws Exception {     inheritConnection();
    return load();
  }

  public static long load()

         throws Exception {

    final String theFilenameCol = "File_Name";     final String theClobCol = "Document_Text";     final String theDocumentTable = "Clob_Table";     final String theDocumentName = "Test Document";     long length = 0;

      // Start by creating the new document record with a NULL value for the
      // document.
      Statement theStatement = theConnection.createStatement();
      theStatement.execute( "DELETE FROM " + theDocumentTable );
      theStatement.execute(


        "INSERT INTO " + theDocumentTable + " ( " +

        theFilenameCol + ", " +
        theClobCol + " ) " +


        "VALUES ( '" +

        theDocumentName + "', "
        + " empty_clob())" );

      // Now select the document record for update and apply the document to
it.
      ResultSet theClobDetails = theStatement.executeQuery(


        "SELECT " + theClobCol + " " +

        "FROM " + theDocumentTable + " " +

        "WHERE " + theFilenameCol + " = '" + theDocumentName +

        "' FOR UPDATE" );


      if ( theClobDetails.next()) {

        // Get the CLOB locator and open output stream for the CLOB
        oracle.sql.CLOB theClob =
(OracleResultSet)theClobDetails).getCLOB( theClobCol );
        Writer theClobStream = theClob.getCharacterOutputStream();

        int counter = 5000;
        while ( counter-- > 0 ) {
          String theString = new String( "123456789012345678901234567890" );
          theClobStream.write( theString );
        }

        theClobStream.flush();
        theClobStream.close();
        theClobDetails.close();

      }
      else {
        // Couldn't retrieve the record!
        throw new Exception( "Can't retrieve inserted record (" +
theDocumentName + ")" );
      }

      theStatement.execute( "commit" );

      theClobDetails = theStatement.executeQuery(


        "SELECT " + theClobCol + " " +

        "FROM " + theDocumentTable + " " +

        "WHERE " + theFilenameCol + " = '" + theDocumentName +

        "' FOR UPDATE" );

      if ( theClobDetails.next()) {

        // Get the CLOB locator and open output stream for the CLOB
        oracle.sql.CLOB theClob =
(OracleResultSet)theClobDetails).getCLOB( theClobCol );
        length = theClob.length();
        System.out.println( "Length of the CLOB is " + length );
      }
      else {
        System.out.println( "Can't re-read CLOB" );
      }

      theStatement.execute( "commit" );
      theStatement.close();

      return length;

  }

  private static void inheritConnection() throws Exception {     try
    {

      DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver());
      theConnection = DriverManager.getConnection( "jdbc:oracle:kprb:" );
      theConnection.setAutoCommit( false );

    }
    catch (Exception e) {
      System.out.println( "Error occurred connecting to the Database: " + e );

      throw e;
    }
  }

  private static void connectToDatabase() throws Exception {

    final String s_hostName     = "(your host)";
    final String s_portNumber   = "1521";  // probably!
    final String s_databaseSID  = "(your sid)";
    final String s_userName     = "username";
    final String s_password     = "password";

    try
    {

      DriverManager.registerDriver( new oracle.jdbc.driver.OracleDriver());
         String connectString =
            "(DESCRIPTION=(ADDRESS=(HOST="+s_hostName+")"+
            "(PROTOCOL=tcp)(PORT="+s_portNumber+"))"+
            "(CONNECT_DATA=(SID="+s_databaseSID+")))";

         theConnection = DriverManager.getConnection(
            "jdbc:oracle:thin:@" + connectString,
            s_userName, s_password );

         theConnection.setAutoCommit( false );

    }
    catch (Exception e) {
      System.out.println( "Error occurred connecting to the Database: " + e );

      throw e;
    }
  }  

  }

BTW it only fails if run as a Java Stored Procedure. It succeeds if run as client-side java. To run server-side, deploy from JDeveloper with entry point 'jsp' and then execute the following in SQL*Plus:

    declare

        mysize number;
    begin

        dbms_output.enable(10000);
        mysize:=tar_1562384.jsp;
        dbms_output.put_line( 'CLOB size after loading is ' || mysize );

    end;
    /

Any suggestions? Any known problems with JSP and CLOBs and UTF8?

Many thanks. Received on Wed May 23 2001 - 15:00:50 CDT