Home » Developer & Programmer » JDeveloper, Java & XML » how to insert large value in CLOB field
how to insert large value in CLOB field [message #91238] Tue, 11 June 2002 04:32 Go to next message
Priyank RASTOGI
Messages: 1
Registered: June 2002
Junior Member
Hi:

I am developing a J2EE application. I want to insert a string more than 4000 bytes in a CLOB field. I cant do this using Oracle thin driver. I want to achieve this by using pure JDBC code. No database-specific code. How can I achive this? I will really appreciate if someone can help me out.

-
regards
Priyank
Re: how to insert large value in CLOB field [message #91250 is a reply to message #91238] Sat, 15 June 2002 08:33 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10694
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
From the docs
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.

sample
------------
 
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
    try
    {
      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++)
        System.out.print(buffer[i]);
      System.out.println();
    }
    
    // Close input stream
    instream.close();
  }

  // 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]+" ");
      System.out.println();
    }
    // Close input stream
    instream.close();
  }

  // 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;
    }
    outstream.close();
  }

  // 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;
    }
    outstream.close();
  }
}
Re: how to insert large value in CLOB field [message #91597 is a reply to message #91250] Wed, 04 December 2002 09:43 Go to previous message
Doug Campbell
Messages: 2
Registered: December 2002
Junior Member
Is it also possible to use the getBytes(int i) and setBytes(int i) methods of PreparedStatment?

I am somewhat reluctant to use driver specific classes such as OracleResultSet etc....
Previous Topic: Re: Procedure that has OUT parameter as vArray giving error :ORA-06531: Reference to uninitialized
Next Topic: Java Stored Procedure sending UDP message
Goto Forum:
  


Current Time: Tue Jul 27 23:42:26 CDT 2021