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

Objectives

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

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

Creating CLOB object by streaming the data

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


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