Limitations of JDBC-OCI 8.0.3 driver for Oracle8 BLOBs ??

From: jagdip <jagdip_at_mailhost.krdl.org.sg>
Date: 1998/12/14
Message-ID: <3674DCF1.1EE4C1E@mailhost.krdl.org.sg>#1/1

hi JDBC/OCI Oracle experts,

I am evaluating whether to use Java/JDBC-OCI drivers or C++/OCI drivers to write and retrieve Word/PDF documents ( as Oracle BLOBs ( not FLOBs) )
from/into Oracle database for our Document Mgt System..

We are writing prototypes using both and try to benchmark..

i hv Oracle 8.0.3 server installation on my Windows NT system. i hv installed jdbc-oci drivers on Windows NT using Oracle installer which
installed as follows:

c:\orant\jdbc\lib\classs\111.zip
c:\orant\jdbc\lib\classs\102.zip
c:\orant\bin\oci803jdbc.dll
c:\orant\bin\oci733jdbc.dll

The README file mentions:
Oracle JDBC Drivers 7.3.3.1.3 beta-release README

i wrote a Java program to read a document from PC filesystem and then put it into the Oracle blob using these JDBC-OCI drivers.. and viceversa.

i am facing two problems:

1st problem

---

The chunk size used in writing and reading a blob is restricted to 255

is it so ? if i increase this size, it gives me error..it says cannot increase
the length of the chunk size., as shown in the statement cstmt1.setLong(2, ix ) in the following piece of code.. I cannot increase the vlaue of the variable "ix" not more than 255..

// the portion of the method adddocBlob() code is here..

	long ix=0;
	long index=0;

  OracleCallableStatement cstmt1 =
      (OracleCallableStatement)
      conn.prepareCall ("begin dbms_lob.write (?, ?, ?, ?); end;");	

while ( ( ix=(long)((docrecdata=(Docread)docrec.dread()).length)) != -1 )
// the above statement returns a portion of the document in the object
"docrecdata"

	{
	byte [] bytes = new byte[(int)ix];
	bytes=docrecdata.bytes;

      cstmt1.setBlob (1, blob);
      cstmt1.setLong (2, ix);
      cstmt1.setLong (3, index + 1);
      cstmt1.setBytes (4, bytes);

	index += ix;
      cstmt1.execute ();
	} // doc read end loop

// end code..

Due to this restriction of 255 size, the writing of a document or reading of a
document into the blob is very slow..

When using C++/OCI program, i do not have this restriction..

Has anyone faced this similar problem or confirm this restriction of 255 chunk size ??
if no restriction, can u pls let me know how to write or read more than 255 chunk size ?? or any other alternative ??

2nd problem:

---

USing max 255 chunk size, i hv benchmarked For writing 64K document into ORacle BLOB , it took 11 secs For 13K, it took 2 secs..

For reading for both cases, it took 2 to 3 secs..

Then, i tested on 0.5 MB file.. After about 200K writing, i found that the performance is very slow, it is taking 2 secs to write 255 bytes
chunk and so slow.. i think it took more than 3-4 hours to write this 0.5 MB document into ORacle database..
At this moment, there are no applications running and no load on the machine,
at that time CPU utilisation was about 15%, enough virtual memory available
on the machine...This problem is very strange ... why it is so slow ?? but dont know why so slow.. i think it could be jdbc-oci driver problem..???

can anybodody pls let me know whether is it a jdbc-oci driver problem??

With this perfromance, i cannot go for java/jdbc-oci driver ..

i hv tested C++/OCI program on AIX with huge files(2 MB into a blob), no problem..
i hv not yet tested C++/OCI program on NT with huge files but it may work..

So, could anyone pls shed some light on this problem no. 2 ??

Note: The Windows NT is a Pentium Pro 200 MHz system. 64MB RAM. Virtual memory: 150-300 MB setting..

Please let me know if anyway i can solve these two problems ...

i highly appreciate for ur responses..
Any help on how to get this will be greatly appreciated.

cheers
jagdip Received on Mon Dec 14 1998 - 00:00:00 CST