Reading CLOB data in Oracle 8.1.7 using JDBC OCI drivers.

From: Balaji Shyamkumar <balaji_at_purpleyogi.com>
Date: 5 Oct 2001 14:05:50 -0700
Message-ID: <e8ff4674.0110051305.506b0974_at_posting.google.com>

I am having trouble reading CLOB data using the OCI drivers. The server is 8.1.7 on Solaris and the client is a windows 2000 client version 8.1.7.

For example, the following table has a column of type CLOB.

---

SQL> describe dummy1;

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------
 ID                                        NOT NULL NUMBER
 WD1                                       NOT NULL NUMBER(5)
 WD2                                       NOT NULL NUMBER(5)
 OSIZE                                              NUMBER
 NUMWORDS                                           NUMBER
 RESULT                                             CLOB
=============================================================

And the following PL/SQL code

---

DECLARE
   clob_locator CLOB;
   charbuf VARCHAR2(32000);
   read_offset INTEGER;
   read_amount INTEGER;
BEGIN
   SELECT result INTO clob_locator FROM dummy1 WHERE id = 5;

   DBMS_OUTPUT.PUT_LINE('CLOB Size: ' ||

                       DBMS_LOB.GETLENGTH(clob_locator));

   read_offset := 65000;
   read_amount := 20;

   dbms_lob.read(clob_locator, read_amount, read_offset, charbuf);    dbms_output.put_line(substr(charbuf,1,20)); END;

---

produces the following results in sqlplus (on the windows client.) CLOB Size: 121655

veterinari 4450 1

PL/SQL procedure successfully completed.

Next I tried something similar with the following java code.

---

// Simple test for reading CLOB data.

import java.io.*;
import java.sql.Connection;
import java.sql.DriverManager;
import java.util.Hashtable;
import java.util.Properties;
import java.sql.*;

import oracle.jdbc.driver.*;
import oracle.sql.*;

class clobTest
{

     public static void main(String args[])     {

        Properties properties = new Properties();
        properties.put("user", "scott");
        properties.put("password", "tiger");
        properties.put("server", "testdb");

        String driverClass = "oracle.jdbc.driver.OracleDriver";
        String url = "jdbc:oracle:oci8:testdb";

        try


        {

DriverManager.registerDriver (new oracle.jdbc.driver.OracleDriver());
            Connection conn = DriverManager.getConnection(url,
properties);
	    Clob myClob = null;
	    Statement stmt = conn.createStatement();
	    stmt.execute("SELECT wd1 from dummy1");
	    ResultSet rs1 = stmt.getResultSet();
	    while(rs1.next()) {
		System.out.println("wd1 = " + rs1.getInt(1));
	    }
	    stmt.execute("SELECT RESULT from DUMMY1");
	    ResultSet rs = stmt.getResultSet();
	    while (rs.next()) {
		myClob = rs.getClob(1);
		InputStream readClobis =  myClob.getAsciiStream();
		System.out.println("Data is " + readClobis.read());
	    }
	    rs.close();
            conn.close();
        }
        catch(Exception exception)


        {

            System.out.println("\nError encountered:\n");
            exception.printStackTrace();
            System.exit(1);
        }
	
        System.exit(0);

    }
}

---

This is the output on the console window on the windows client.

E:\tests>set CLASSPATH
CLASSPATH=.;c:\jdk1.2.2\lib;e:\oracle\ora81\jdbc\lib\classes12.zip

E:\tests>javac -g clobTest.java

E:\tests>java clobTest
wd1 = 6
wd1 = 5

Error encountered:

java.io.IOException: ORA-03120: two-task conversion routine: integer overflow

        at oracle.jdbc.dbaccess.DBError.SQLToIOException(DBError.java:531)
        at oracle.jdbc.driver.OracleClobInputStream.needBytes(OracleClobInputStr
eam.java, Compiled Code)
        at oracle.jdbc.driver.OracleBufferedStream.read(OracleBufferedStream.jav
a:82)
        at clobTest.main(clobTest.java, Compiled Code)

E:\tests>

---

What am I doing wrong? Does anyone have a working example of reading and writing CLOB data using any JDBC drivers for version 8.1.7 of Oracle server and 8.1.7 client for windows?

Thanks!
Balaji Received on Fri Oct 05 2001 - 23:05:50 CEST