Go Premium for a chance to win a PS4. Enter to Win


writing Oracle 9 BLOB (JDBC)

Posted on 2004-03-29
Medium Priority
Last Modified: 2007-12-19
Hello all!

I'm having trouble with Oracle Blobs through java. I don't know in which forum I should ask this question, but here goes:

I have an Oracle 9i database and JDBC 9.2 driver version.
I am using java v1.4.

I'm connecting to my database using the following:

DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
        _connection = DriverManager.getConnection("jdbc:oracle:thin:@host:port:ut", user, passwd);

In my code, i'm trying to write a blob to my table and the code looks like this:

import java.sql.*;
import oracle.sql.BLOB;


stmt = Database.getConnection().createStatement();
ResultSet rs = stmt.executeQuery(sql);
if (rs.next()) {
        Blob myblob = rs.getBlob(1);
        OutputStream os = ((BLOB)myblob).setBinaryStream(0);

BUT i get this error:
java.sql.SQLException: Unsupported function

After debugging, I found that it is "((BLOB)myblob).setBinaryStream(0);" which is causing this problem.

I've been reading the Oracle JDBC doc and can't find what the problem could be. I tried using OCI driver for connection but then I get ORA-06401: NETCMN error...
Question by:mariec

Author Comment

ID: 10703279
BTW i've just changed that line to:

OutputStream os = ((BLOB)myblob).getBinaryOutputStream();

and it still doesnt work

Expert Comment

ID: 10703432

you should use DBMS_LOB package to write blob data to database.


Accepted Solution

grim_toaster earned 920 total points
ID: 10703522
Couple of pointers for you:

1) You will need to set your connection to not automatically commit
2) You need to query for update
3) You will need to commit at the end (or rollback if there were errors)
4) Closing of database resources and io streams should be done in a finally block to ensure that they are always closed, even if errors occur

Here's a slightly modified version of your code to make it work in an Oracle specific way (but without the forcing closure of the resources as mentioned above).

            con = Database.getConnection();

            stmt = con.createStatement();

            if (rset.next()) {
                BLOB myblob = (BLOB) rset.getBlob(1);
                OutputStream os = myblob.getBinaryOutputStream();
                os.write("hello I'm a goldfish...".getBytes());

LVL 23

Assisted Solution

seazodiac earned 80 total points
ID: 10707585
Hi, mariec,
in case you are still looking , here is a doc from Oracle metalink and it's for oracle8i but I find it's also applicable to oracle9i

  The purpose of this article is to explain and demonstrate the use of  
  the Oracle JDBC extensions for CLOB and BLOB support.  It contains a  
  commented example of a JDBC program that manipulates CLOBs and BLOBs  
  in an Oracle database using the Oracle extensions to the JDBC drivers.  
  This article was taken from the 'LobExample.java' sample that comes with  
  the 8i release.  It is intended for use by programmers who are interested  
  in using these extensions to manipulate LOB data in an Oracle database.  
  The example is cursory in nature and is meant to be used as a template  
  to help create your own JDBC applications that need to manipulate LOBs.  
  It is not the responsibility of Oracle to recode the example to meet  
  your specific needs.  Since the code uses Oracle specific extensions to  
  JDBC, it is not portable to other vendors databases and JDBC drivers.
  This code was tested using classes111.zip, JDK 1.1.8, and Oracle 8.1.5.
  This article was written prior to the JDBC 2.0 specifiction and prior to  
  the existence of classes12.zip.  Refer to the LobExample.java on your  
  file system for a more current example that uses JDK 1.2 extentions.  
Related Documents
  A more detailed description of the Oracle JDBC extensions and  
  additional JDBC information is contained in the "Oracle 8i JDBC  
  Developer's Guide and Reference", (A64685-01).
  For more information on Java Streams, refer to the "Core Java,  
  Volume II", (ISB-N-0-13-766965-8), Sun Microsystems Press (A prentice  
  Hall Title), by Hostmann and Cornell.
  Only two steps that are necessary to read and write CLOB and BLOB  
  data in an Oracle database:
  1. Access the LOB locator.  The datatypes oracle.sql.BLOB and
     oracle.sql.CLOB are classes that encapsulate Oracle LOB
     locators and provide a streaming interface to the LOB data
     through the locator.
     A CLOB or BLOB locator can be retrieved from an OracleResultSet
     or an OracleCallableStatement.
  2. Use the appropriate getXXXStream() method provided by the LOB
     locator class.  This materializes the BLOB or CLOB as a  
     Java Stream.  The read() and write() methods provided by the  
     stream interface can then be used to access and manipulate the  
     LOB data.
  Oracle.sql.BLOB and oracle.sql.CLOB are datatypes that are supported  
  by the JDBC 2.0 standard.  As such, these datatypes will implement  
  java.sql.BLOB and java.sql.CLOB when the JDBC 2.0 standard is available.  
  This standard for JDBC is part of the JDK 1.2 release.
  Since the 8i release of the JDBC drivers is JDBC 1.22 and JDK 1.1.6  
  compliant, the oracle.sql.BLOB and oracle.sql.CLOB classes do not  
  implement java.sql.BLOB and java.sql.CLOB interfaces because they
  are not present in the classes.zip file that comes with the JDK 1.1.x.  
  To get around this and to offer support of the JDBC 2.0 features that
  pertain to LOB datatypes, Oracle has placed JDBC 2.0 compliant
  interfaces to LOB datatypes in the oracle.jdbc2 package.  This package
  can be found in classes111.zip.  
  The oracle.sql.BLOB and oracle.sql.CLOB datatypes implement the interfaces  
  oracle.jdbc2.Blob and oracle.jdbc2.Clob, respectively.  In a future  
  release of Oracle, LOB types will implement java.sql.* interfaces.
Example Code
import java.sql.*;
import java.io.*;
import java.util.*;
// Importing the Oracle Jdbc driver package makes the code more readable
import oracle.jdbc.driver.*;
//needed for new CLOB and BLOB classes
import oracle.sql.*;
import oracle.jdbc2.*;
public class LobStreams  
  public static void main (String args [])
  throws Exception
    // Load the Oracle JDBC driver
    DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    // Connect to the database
    // You can put a database name after the @ sign in the connection URL.
    // <database> is either an entry in tnsnames.ora or a SQL*net name-value pair.
    Connection conn =
      DriverManager.getConnection ("jdbc:oracle:oci8:@<database>", "scott", "tiger");
    // Its faster when auto commit is off
    conn.setAutoCommit (false);
    // Create a Statement
    Statement stmt = conn.createStatement ();
    // Drop the basic_lob_table
      stmt.execute ("drop table basic_lob_table");
    catch (SQLException e)
      // An exception could be raised here if the table did not exist already
      // but we gleefully ignore it
    // Create a table containing a BLOB and a CLOB
    stmt.execute ("create table basic_lob_table (x varchar2 (30), b blob," +
                  "c clob)");
    // Populate the table
    stmt.execute ("insert into basic_lob_table values ('one'," +
    "'010101010101010101010101010101', 'onetwothreefour')");
    stmt.execute ("insert into basic_lob_table values ('two'," +
    "'020202020202020202020202020202', 'twothreefourfivesix')");
    System.out.println ("Dumping lobs");
    // Select the lobs
    OracleResultSet rset = (OracleResultSet)stmt.executeQuery(
                                "select * from basic_lob_table");
    while (rset.next ())
      // Get the lobs
      BLOB blob = ((OracleResultSet)rset).getBLOB (2);
      CLOB clob = ((OracleResultSet)rset).getCLOB (3);
      // Print the lob contents
      dumpBlob (conn, blob);
      dumpClob (conn, clob);
      // Change the lob contents
      fillClob (conn, clob, 2000);
      fillBlob (conn, blob, 4000);
    System.out.println ("Dumping lobs again");
    rset = (OracleResultSet)stmt.executeQuery(
                      "select * from basic_lob_table");
    while (rset.next ())
      // Get the lobs
      BLOB blob = ((OracleResultSet)rset).getBLOB (2);
      CLOB clob = ((OracleResultSet)rset).getCLOB (3);
      // Print the lobs contents
      dumpBlob (conn, blob);
      dumpClob (conn, clob);
  // Utility function to dump Clob contents
//  static void dumpClob (Connection conn, CLOB clob)
  static void dumpClob (Connection conn, Clob clob)
  throws Exception
    // get character stream to retrieve clob data
    Reader instream = clob.getCharacterStream();
    // create temporary buffer for read
    char[] buffer = new char[10];
    // length of characters read
    int length = 0;
    // fetch data
    while ((length = instream.read(buffer)) != -1)
      System.out.print("Read " + length + " chars: ");
      for (int i=0; i<length; i++)
    // Close input stream
  // Utility function to dump Blob contents
  static void dumpBlob (Connection conn, BLOB blob)
  throws Exception
    // Get binary output stream to retrieve blob data
    InputStream instream = blob.getBinaryStream();
    // Create temporary buffer for read
    byte[] buffer = new byte[10];
    // length of bytes read
    int length = 0;
    // Fetch data
    while ((length = instream.read(buffer)) != -1)
      System.out.print("Read " + length + " bytes: ");
      for (int i=0; i<length; i++)
        System.out.print(buffer[i]+" ");
    // Close input stream
  // Utility function to put data in a Clob
  static void fillClob (Connection conn, CLOB clob, long length)
   throws Exception
    Writer outstream = clob.getCharacterOutputStream();
    int i = 0;
    int chunk = 10;
    while (i < length)
      outstream.write(i + "hello world", 0, chunk);
      i += chunk;
      if (length - i < chunk)
        chunk = (int) length - i;
  // Utility function to put data in a Blob
  static void fillBlob (Connection conn, BLOB blob, long length)
    throws Exception
    OutputStream outstream = blob.getBinaryOutputStream();
    int i = 0;
    int chunk = 10;
    byte [] data = { 1, 2, 3, 4, 5, 6, 7, 8, 9, 10 };
    while (i < length)
      data [0] = (byte)i;
      outstream.write(data, 0, chunk);
      i += chunk;
      if (length - i < chunk)
        chunk = (int) length - i;

Author Comment

ID: 10711327
grim_toaster's hints helped out alot. Query for update helps as well as disabling autocommit.

Seazodiac, its an interesting article but since the JDBC 2.0 standard is available, the code must be changed a little. Thanks for the doc though.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question