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 Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/42800.jpg) |
Mahesh Rajendran
Messages: 10707 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();
}
}
|
|
|
|
Goto Forum:
Current Time: Sun May 19 19:24:27 CDT 2024
|