What is wrong in my CLOB java program for data > 4000
Date: 12 Jul 2001 07:10:57 -0700
Message-ID: <e9af93aa.0107120610.1934b6_at_posting.google.com>
I am trying to insert and read char data via Java program using
CLOB/Clob and Oracle 8.1.5. In addition
to use the CLOB class (and its putString method) of oracle JDBC API I
am using classes12.zip (alongwith
classes111.zip). The following program works just fine if the data is
of <= 4000 chars. But if the data
is more than 4000 chars than it gives "Data size bigger than max size
for this type: 4001" exception.
I have no idea why it is happening this way though I am using clob
locator (rather than direct input)
for the char data writing. One more point to mention here is that when
I do "desc" on my t1 table it shows the clob table column as
CLOB(4000). i was confused about this also as why does it show that
way. documents says
that it supports 4gb. I know that if you do the direct insert in the
clob field like "insert into t1
values (1, 'my long clob data')" then one can use max upto 4000 chars.
But if you use clob locator,
as I am, then 4gb is the limit.
Please help.
Below is the entire program with good comments.
Regards
/*************************************************
Note : I am using...
- Oracle 8.1.5
- thin Driver
- jdk1.3 from Sun
- Windows 2000
- classes12.zip for CLOB (got it from JDeveloper3.0 from its
jdbc\lib\oracle8.1.6sdk dir)
(classes111.zip contains oracle.jdbc2.Clob required for
oracle.sql.CLOB class)
connect scott/tiger
create table t1 (id number, data clob);
D:\CLOBTest>javac -classpath
D:\oracle8.1.6sdk\classes12.zip;D:\oracle8.1.6sdk\classes111.zip;.
CLOBTest.java
D:\CLOBTest>java -classpath
D:\oracle8.1.6sdk\classes12.zip;D:\oracle8.1.6sdk\classes111.zip;.
CLOBTest
4001
JDBC driver version is 8.1.5.0.0
2001-07-12 10:51:28.617:---------- Going to write ----------- 2001-07-12 10:51:28.687:Generating data... 2001-07-12 10:51:28.697:Generated 4001 chars of data 2001-07-12 10:51:28.697:Inserting data... 2001-07-12 10:51:28.777:Inserted data. 2001-07-12 10:51:28.777:Executing Select to get the CLOB locator... 2001-07-12 10:51:28.837:Fetching CLOB Locator... 2001-07-12 10:51:28.867:Writing to Clob Locator...Exception in thread "main" java.sql.SQLException: Data size bigger than max size for this type: 4001
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:620)
at oracle.jdbc.ttc7.TTCItem.setArrayData(TTCItem.java:81)
at oracle.jdbc.driver.OraclePreparedStatement.setItem(OraclePreparedStatement.java:530)
at oracle.jdbc.driver.OraclePreparedStatement.setString(OraclePreparedStatement.java:802)
at oracle.sql.CLOB.plsql_write(CLOB.java:384)
at oracle.jdbc.ttc7.TTC7Protocol.lobWrite(TTC7Protocol.java:1475)
at oracle.sql.CLOB.putChars(CLOB.java:247)
at oracle.sql.CLOB.putString(CLOB.java:265)
at CLOBTest.test_write(CLOBTest.java:77)
at CLOBTest.main(CLOBTest.java:131)
**************************************************/
import java.sql.*;
import oracle.jdbc.driver.*;
import oracle.sql.*;
class CLOBTest {
public static void showTimeStamp(String msg) {
System.out.println( (new
Timestamp(System.currentTimeMillis())).toString() +":" + msg );
}
public static void test_write(Connection conn, int nchars) throws Exception {
PreparedStatement pstat1 = conn.prepareStatement("insert into T1 values (?, empty_clob())");
PreparedStatement pstat2 = conn.prepareStatement("select DATA from T1 where id=? for update");
showTimeStamp("Generating data...");
StringBuffer sb = new StringBuffer();
for( int i = 0; i < nchars; i++ ) {
sb.append('0');
}
String outString = sb.toString();
showTimeStamp("Generated " + outString.length() +" chars of
data");
showTimeStamp("Inserting data...");
pstat1.setInt(1, 1);
pstat1.executeUpdate();
showTimeStamp("Inserted data.");
showTimeStamp("Executing Select to get the CLOB locator...");
pstat2.setInt(1, 1);
ResultSet res = pstat2.executeQuery();
if (res.next()) {
showTimeStamp("Fetching CLOB Locator...");
oracle.sql.CLOB clob = (oracle.sql.CLOB) res.getClob(1);
showTimeStamp("Writing to Clob Locator...");
clob.putString(1, outString);
showTimeStamp("putString over");
}
res.close();
conn.commit();
}
public static void test_read(Connection conn ) throws Exception {
PreparedStatement pstat = conn.prepareStatement("select Data from T1 where ID=?");
showTimeStamp("Executing Query...");
pstat.setInt(1, 1);
ResultSet res = pstat.executeQuery();
while (res.next()) {
showTimeStamp("Fetching CLOB Locator...");
Clob dataClob = res.getClob(1);
showTimeStamp("Reading data at the clob locator...");
String data = dataClob.getSubString(1, (int)
dataClob.length());
showTimeStamp("got string " + ": length: " + data.length());
showTimeStamp("data: " +data);
}
res.close();
}
public static void main(String args[]) throws Exception {
int nchars = 0;
try {
nchars = Integer.parseInt(args[0]);
}
catch (Exception e) {
System.out.println("\n\tUSAGE: java CLOBTest nchars\n");
System.exit(0);
}
DriverManager.registerDriver(new
oracle.jdbc.driver.OracleDriver());
Connection conn =
DriverManager.getConnection("jdbc:oracle:thin:_at_homepc:1521:MyDB","scott","tiger");
//("jdbc:oracle:oci8:_at_ora816dev.us.oracle.com"
DatabaseMetaData meta=conn.getMetaData(); System.out.println("JDBC driver version is "+meta.getDriverVersion());
conn.setAutoCommit(false);
Statement stmt = conn.createStatement();
stmt.execute("alter session set sql_trace=true"); stmt.execute("delete from t1" );
showTimeStamp("---------- Going to write -----------");
test_write(conn, nchars);
showTimeStamp("");
showTimeStamp("---------- Going to read ------------");
test_read(conn);
showTimeStamp("");
showTimeStamp("done");
}
}
Received on Thu Jul 12 2001 - 16:10:57 CEST