How to pass LOBs as parameters from Java to a Database Stored Procedure.

Date: 12-May-2003

Objective

After reading this document, you should be able to:

• Create LOB(Large OBject) objects in a Java program and pass it as a parameter to a database stored procedure.
  This document illustrates the same by giving the code snippet and description.
• Run the sample using the instructions provided.

Software Requirements

• JDK1.3.x or above This can be downloaded from here.
• Oracle9i Database Release 2 or later. This can be downloaded from here.
  
• Oracle9i JDBC Driver. This can be downloaded from here.

Note: Oracle JDBC Driver is available with Oracle Database/client installation. If you have a database/client installation,
you need not download JDBC driver separately.

Description

LOBs or Large Objects, are designed to support the unstructured data that cannot be decomposed into standard components i.e they can't be broken down into any finer structure in terms of database storage. LOBs can store unstructured data and is optimized for large amounts of data such as text, graphic images, still video clips, full motion video, and sound waveforms. They provide a uniform way of accessing large unstructured data within the database or outside.

With Oracle 8 and above, there can be multiple LOB columns in a database table, which can also be of different types. The storage capacity of LOBs is upto 4GB.

Oracle supports the following two types of LOBs.

• BLOB, CLOB, and NCLOB: These are stored in the database either in-line in the table or in a separate segment or tablespace.
  • BLOBs can store large amounts of binary data typically graphic images and pictures.
  • CLOBs can store large amounts of character data and are useful for storing unstructured XML documents.
  • NCLOB: consists of character data that corresponds to the national character set defined for the Oracle database.
• BFILEs: These are stored as operating system files.
  • BFILE is a LOB whose value is composed of binary ("raw") data, and is stored outside the database tablespaces in a server-side operating system file.
    

Sample Scenario:
Lets assume a story table in the database having two columns, an id and a CLOB. The story text is stored in the CLOB column. A stored procedure updates the story table in the database. It accepts two input parameters: id and CLOB. Below is an example SQL code snippet:

Database preparation:

CREATE TABLE stories (id NUMBER(4), story CLOB);
INSERT INTO stories VALUES(1, EMPTY_CLOB());
COMMIT;

-- The following procedure updates the CLOB in the table.
CREATE OR REPLACE PROCEDURE updateStory(id NUMBER, colValue  IN CLOB) IS
 BEGIN
    UPDATE stories s SET  s.story = colValue

    WHERE s.id = id ;
    COMMIT;
 -- Exception handling.
  -- EXCEPTION
   --   WHEN OTHERS THEN
    -- <process exception here>
 END updateStory;
/
 	

We shall now look into different ways of passing CLOB to a database stored procedure. We can create and manipulate CLOB object in two ways:
With and without streaming the data. Data streaming is always recommended for large amount of data.

Below is the code snippet for both the ways.

Without Streaming the data and using oracle.sql.CLOB class

    .............
    .............
    
    // Using Oracle implementation of CLOB 
    import oracle.sql.CLOB;
    // IO Imports

    import java.io.Writer;
	
    /*
     * This method uses temporary clob to create the CLOB object.
     */
    private static CLOB getCLOB( String clobData )
                    throws Exception {
      CLOB tempClob = null;

      try {
        //  create a new temporary CLOB
        tempClob = CLOB.createTemporary( conn, true, CLOB.DURATION_SESSION );

        // Open the temporary CLOB in readwrite mode to enable writing
        tempClob.open( CLOB.MODE_READWRITE );

        // Get the output stream to write

        Writer tempClobWriter = tempClob.getCharacterOutputStream(  );

        // Write the data into the temporary CLOB
        tempClobWriter.write( clobData );

        // Flush and close the stream
        tempClobWriter.flush(  );
        tempClobWriter.close(  );

        // Close the temporary CLOB
        tempClob.close(  );

      } catch ( Exception exp ) {
	         // Free CLOB object
          tempClob.freeTemporary( );
          // do something
      }
      return tempClob;      

    }					
					

Calling the database procedure by using the above getCLOB method:

    ..............................
    ..............................
      CallableStatement cs = null;
      oracle.sql.CLOB clob = null;
      String clobData = null;
      .......................
      .......................
  
      // read the file whose content has to be passed as CLOB.
      String lineSep = System.getProperty("line.separator");
      BufferedReader br = new BufferedReader(new FileReader(filename));
      String nextLine = "";
      StringBuffer sb = new StringBuffer();
      while ((nextLine = br.readLine()) != null) {

        sb.append(nextLine);
        sb.append(lineSep);
      }
      // convert to string
      clobData = sb.toString();

      // call Stored DB procedure for updating clob column
      cs = (CallableStatement)
            conn.prepareCall( "begin updateStory(?,?); end;" ); 

      // Create the CLOB object from the getCLOB method listed above.
      clob = getCLOB(clobData);

      // Bind the columns values
      cs.setObject(1, "1" );

      cs.setObject(2, clob );

      ............................
      ............................

    ..............................
    ..............................
  public void callUpdateUsingStream(String id, String filename )
       throws SQLException, FileNotFoundException {

    CallableStatement cs = null;
    .....................
    .....................
      conn.setAutoCommit(false);
      String fileName =  filename;


      // Open the sample file as a stream
      File file = new File(fileName);
      FileInputStream fin = new FileInputStream(file);

      // Call Stored DB procedure for updating clob column
      cs = (CallableStatement)
                  conn.prepareCall( "begin updateStory(?,?); end;" ); 
      // set id.
      cs.setObject(1, id );

      // use setAsciiStream to set the clob parameter.
      cs.setAsciiStream(2,fin,(int)file.length());

      cs.execute();
      conn.setAutoCommit(true);


    ............................
    ............................

Running the Java class

• Copy the full source code to a directory and save it as TestCLob.java
  
• Run the SQL script listed above to create the table and the stored procedure as SCOTT user in the database.
• Edit TestClob.java and change the getConnection() method to use your own database parameters.
  // Connect to the database.
  conn = DriverManager.getConnection
  ("jdbc:oracle:thin:@<hostname>:<port>:<sid>",
  "scott", "tiger");
  
• From a command prompt in the copied directory, set the classpath to include the
  Oracle JDBC driver class: classes12.zip or classes12.jar
• Compile the TestClob class.
  Example:
  javac -d . TestClob.java
• Run the class by giving values to the parameters.
  java TestClob <id> <filename> <flag>
  
• <id> String value that denotes the id of the record to be updated.
• <filename> String value that denotes the file to be uploaded as content in the clob column.
• <flag> String value, can be either "true" or "False".
  "false" will use non-streaming way to update the CLOB using callUpdate() method.
  "true" will use streaming way to update thr CLOB using callUpdateUsingStreaming()method.
  Example:
  java TestClob "1" "d:\text.txt" "false"
• Check the file size and the CLOB size in the database to verify the updated data by running the following query:
  SQL> SELECT DBMS_LOB.GETLENGTH(story) FROM stories

Resources

• Oracle9i Application Developer's Guide - Large Objects (LOBs) Release 2 (9.2)
  

Sample Application References on OTN:

• Advance JDBC samples: LOB Sample
• XML Dynamic news
• Survey Sample application
• Content Management System

Summary

This document has tried to explain and illustrate how to create CLOB objects from a Java program and pass it to a database stored procedure.

Please enter your comments about this sample in the OTN Sample Code Discussion Forum.