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

I've used Oracle's thin driver on a servlet and have written a 2mb file - don't have exact time, but I think it was less than 20 seconds. You should also try using long raw for the column type instead of long. I also used setBytes, as I was reading from a binary file and this worked well. You can also steam with an InputStream.

Hope this helps.

Gary

jagdip wrote in message <3674DCF1.1EE4C1E_at_mailhost.krdl.org.sg>...
>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 Wed Dec 16 1998 - 00:00:00 CST