Maclochlainn's Weblog

Michael McLaughlin's Old Technical Blog

How to read a CLOB through JDBC

with 5 comments

You’ll find an updated version on the new blog, and the code is copiable there.

There was a post on the Oracle Technical Network about connecting and reading a CLOB. I thought it might be a nice thing to add to my blog. To make my publisher happy, I borrowed it from my Oracle Database 11g PL/SQL Programming book. If you’re wondering why it’s in there, well there’s a chapter on writing Java libraries and PL/SQL wrappers to libraries.

This is based on Java 5 and a connection to an Oracle Database 11g instance. You should set your environment $PATH to the Java home bin directory, and $CLASSPATH to the location of the ojdbc5.jar file before compiling and running this program.

It depends on seeding the LOB first. You can find the code to do that in the presentation that I made at the Utah Oracle User Group Training Days 2008. The program will render a Java Swing JFrame like this:

Here’s the code:

// --------------------------------------------------------------------
// ReadCLOB.java
// Appendix D, Oracle Database 11g PL/SQL Programming
// by Michael McLaughlin
//
// This code demonstrates reading an image file and displaying
// the image in a JLabel in a JFrame.
// --------------------------------------------------------------------
// Java Application class imports.
import javax.swing.JFrame;
import javax.swing.JPanel;
import javax.swing.JScrollPane;
import javax.swing.JTextArea;
import java.awt.Font;
import java.awt.GridLayout;
import java.io.Reader;
// Generic JDBC imports.
import java.sql.Clob;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
// Oracle JDBC import.
import oracle.jdbc.driver.OracleDriver;
// -------------------------------------------------------------------/
public class ReadCLOB extends JPanel {
// Define database connections.
private String host = "mclaughlin11g";
private String port = "1521";
private String dbname = "orcl";
private String userid = "plsql";
private String passwd = "plsql";
// Define display variables.
private String clobText;
private JScrollPane scrollPane;
private JTextArea textArea;
// -----------------------------------------------------------------/
public ReadCLOB () {
// Set layout manager.
super(new GridLayout(1,0));
// Query the database.
clobText = getQuery(host,port,dbname,userid,passwd);
// Construct text area and format it.
textArea = new JTextArea(clobText);
textArea.setEditable(false);
textArea.setFont(new Font("San Serif",Font.PLAIN,14));
textArea.setLineWrap(true);
textArea.setRows(10);
textArea.setSize(400,100);
textArea.setWrapStyleWord(true);
// Put the image in container, and add label to panel.
scrollPane = new JScrollPane(textArea);
add(scrollPane); }
// -----------------------------------------------------------------/
private String getQuery(String host,String port,String dbname
,String user,String pswd) {
// Define method variables.
char[] buffer;
int count = 0;
int length = 0;
String data = null;
String[] type;
StringBuffer sb;
try {
// Load Oracle JDBC driver.
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
// Define and initialize a JDBC connection.
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@" +
host + ":" + port + ":" + dbname, userid, passwd);
// Define metadata object.
DatabaseMetaData dmd = conn.getMetaData();
// Create statement.
Statement stmt = conn.createStatement();
// Execute query.
ResultSet rset =
stmt.executeQuery("SELECT item_desc " +
"FROM   item " +
"WHERE  item_title = 'The Lord of the Rings - Fellowship of the Ring'" +
"AND    item_subtitle = 'Widescreen Edition'");
// Get the query metadata, size array and assign column values.
ResultSetMetaData rsmd = rset.getMetaData();
type = new String[rsmd.getColumnCount()];
for (int col = 0;col < rsmd.getColumnCount();col++)
type[col] = rsmd.getColumnTypeName(col + 1);
// Read rows and only CLOB data type columns.
while (rset.next()) {
for (int col = 0;col < rsmd.getColumnCount();col++) {
if (type[col] == "CLOB") {
// Assign result set to CLOB variable.
Clob clob = rset.getClob(col + 1);
// Check that it is not null and read the character stream.
if (clob != null) {
Reader is = clob.getCharacterStream();
// Initialize local variables.
sb = new StringBuffer();
length = (int) clob.length();
// Check CLOB is not empty.
if (length > 0) {
// Initialize control structures to read stream.
buffer = new char[length];
count = 0;
// Read stream and append to StringBuffer.
try {
while ((count = is.read(buffer)) != -1)
sb.append(buffer);
// Assign StringBuffer to String.
data = new String(sb); }
catch (Exception e) {} }
else
data = (String) null; }
else
data = (String) null; }
else {
data = (String) rset.getObject(col + 1); }}}
// Close resources.
rset.close();
stmt.close();
conn.close();
// Return CLOB as a String data type.
return data; }
catch (SQLException e) {
if (e.getSQLState() == null) {
System.out.println(
new SQLException("Oracle Thin Client Net8 Connection Error.",
"ORA-" + e.getErrorCode() +
": Incorrect Net8 thin client arguments:\n\n" +
"  host name     [" + host + "]\n" +
"  port number   [" + port + "]\n" +
"  database name [" + dbname + "]\n"
, e.getErrorCode()).getSQLState());
// Return an empty String on error.
return data; }
else {
System.out.println(e.getMessage());
return data; }}
finally {
if (data == null) System.exit(1); }}
// -----------------------------------------------------------------/
public static void main(String[] args) {
// Define window.
JFrame frame = new JFrame("Read CLOB Text");
// Define and configure panel.
ReadCLOB panel = new ReadCLOB();
panel.setOpaque(true);
// Configure window and enable default close operation.
frame.setContentPane(panel);
frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
frame.setLocation(100,100);
frame.pack();
frame.setVisible(true);    }}

Written by maclochlainn

September 7, 2008 at 11:55 pm

5 Responses

Subscribe to comments with RSS.

  1. [...] You can find the blog page here … Tagged with: clob, getClob, Java 5, JDBC, ojdbc5.jar « Magic configuration steps for Mac OS X and Oracle Client 10g [...]

  2. Hi
    It’s better to put source link

    chinaman

    September 18, 2008 at 3:49 am

  3. Yes, I’ll probably have to move this over to one of my domains. I’ll do it after Oracle Open World.

    maclochlainn

    September 19, 2008 at 12:46 am

  4. I finally migrated the blog. A big part of making that investment where comment like the one from “chinaman” above.

    maclochlainn

    November 3, 2008 at 3:21 am

  5. Thank you so much for the code snippet. I (hopefully) would have gotten there eventually, but this made it painless.

    Phillip

    February 3, 2011 at 9:58 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: