How to Read a CLOB Field

A CLOB field contains a locator object for large data objects.

A CLOB (Character Large Object) field is a field in an Oracle database that contains a locator or pointer to a large object. CLOB fields are used to store pointers to large files within the database, eliminating the need to manage the files and their locators on a separate file system, while addressing the problems associated with storing large objects directly in the database. In order to retrieve a CLOB field, you will have to use a programming language, such as Java, to read the data to which it points. When you have retrieved the filestream, you can then read and process its contents.

Things You'll Need

• Oracle database
• Java editor

Instructions

Configuration

1. Launch your Java editor application by clicking on “Start” and “Programs” from your desktop, then selecting the application from the menu.

2. Start a new project by selecting the “File” menu, then clicking on “New” and then “Project” in most editors.

3. Type a name for your project and click on “Save.”

Processing

1. Create a new CLOB object by using the “getClob” method of the “ResultSet” object. The syntax is:
   Clob clobName = resultset.getClob(CLOB_Field)

2. Read the CLOB field as an ASII stream. You will use the “getAsciiStream” method of the “Clob” object to materialize the data in the CLOB field so that you can process it in the Java code. This method returns an “InputStream” of ASCII bytes.

   According to Oracle, the correct definition is:
   public InputStream getAsciiStream() throws SQLException

   For example:
   java.io.InputStream nameofInputStrem = clobName.getAsciiStream();
   byte byteName = nameofInputStream.read();

3. Read the CLOB field as a character stream. You will use the “getCharacterStream” method of the “Clob” object to materialize the CLOB field as a Unicode character stream. This method returns a “Reader” object.

   The definition is:
   public Reader getCharacterStream() throws SQLException

   For example:
   java.io.Reader nameofReader = clobName.getCharacterStream();
   int output = nameofReader.read();

4. Read a portion of the CLOB field using the “getSubString” method of the “Clob” object. This method will return a “string” object containing data from the CLOB field of the specified length, beginning at the specified start position.

   The definition is:
   public String getSubString(long startpos, int length) throws SQLException

   Where startpos is the start position and length is the length of the substring.

   For example:
   String ouputString = clobName.getSubString(1,25);

References

• Oracle:” Interface Clob”; 2003
• PSOUG: “Oracle DBMS_LOB”;2010
• Oracle:” Clob ”;2000

Resources

• Apache:” HOWTO - Work with LOB Data Types”; Torsten Schlabach;2011