Notice:

This page has been converted from a dynamic Wordpress article to a static HTML document. As a result, some content may missing or not rendered correctly.

Returning A CLOB From A Java Stored Procedure ~ Tue, 17 Apr 2012 20:07:31 +0000

This took me way too long to figure out. All of my search results were coming back with questions about passing CLOBs or VARARRAYs to a Java stored procedure, and nothing about returning such datatypes. In the end, it turns out to be somewhat simple, but completely nonsensical.

First, know that you will need to use the OJDBC libraries. These should already be available in your Oracle installation if your database is configured to allow Java stored procedures. If not, you'll have to talk to your DBA, because I don't know anything about that. All I know is that the libraries are available in my instance.

So, let's look at some example code:

package com.jrfom;

import java.io.*;
import java.sql.Connection;
import oracle.jdbc.driver.*;
import oracle.sql.CLOB;

public class ExampleProcedureClass {
  public static CLOB encodeFile(String file)
    throws java.io.FileNotFoundException,
      java.io.IOException,
      java.sql.SQLException
  {
    OracleDriver driver = new OracleDriver();
    Connection conn     = driver.defaultConnection();
    CLOB clob           = CLOB.createTemporary(conn, false, CLOB.DURATION_CALL);

    String sillyString = "This is a string that will be in the CLOB.";
    clob.setString(1, sillyString);

    return clob;
  }
}

What's so "nonsensical" about this? The fact that you have to get a reference to the database connection. Without this connection reference, you cannot create a usable CLOB. There is the static CLOB.getEmptyClob(), but it "can not be read or written" (ref). The only other option is to construct and instance of the object, and every public constructor requires a Connection object. But, this is a stored procedure! Why should we have to do this? I have no idea. But the defaultConnection method of the OracleDriver object will get you a reference to the connection calling the procedure.

So there you go. Enjoy your ability to return CLOBs from your Java stored procedures.

Code,  Java,  Oracle,  Technology