max buffer size in dbms_lob.read

[st...@sds.de]

I am trying to read BLOBS using JDBC. Since the stream interface
to BLOBs is not supported for 8.0.5 I have to call dbms_lob.read
to get the data.

The problem is I get an Exception when I want to read more
than 128 or so bytes at a time. Documentation of rdbms_lob.read
says I should be able to read 32767 bytes.

I get the following error message:

java.sql.SQLException: ORA-21560: Argument 2 ist null, ungültig oder
außerhalb des Wertebereichs
ORA-06512: in "SYS.DBMS_LOB", Zeile 458
ORA-06512: in Zeile 1

Any ideas?

Sent via Deja.com http://www.deja.com/
Before you buy.

[C. Ferguson]

Hi,
please post your code snippet.

cindy

[st...@sds.de]

In article <38BEA1B6...@rationalconcepts.com>,

This is straight out of the example provided by Oracle,
the only modification is the value for the variable "chunk"
which is 10 in the original example. All values up to 255
work fine, everything larger gets me an exception on
cstmt2.execute.

Documentation on the Oracle web site says chunk size could
be as large as 32K.

OracleCallableStatement cstmt1 =
(OracleCallableStatement)
conn.prepareCall ("begin ? := dbms_lob.getLength (?); end;");
OracleCallableStatement cstmt2 =
(OracleCallableStatement)
conn.prepareCall ("begin dbms_lob.read (?, ?, ?, ?); end;");

cstmt1.registerOutParameter (1, Types.NUMERIC);
cstmt1.setBlob (2, blob);
cstmt1.execute ();

long length = cstmt1.getLong (1);
long i = 0;
int chunk = 256;

while (i < length)
{
cstmt2.setBlob (1, blob);
cstmt2.setLong (2, chunk);
cstmt2.registerOutParameter (2, Types.NUMERIC);
cstmt2.setLong (3, i + 1);
cstmt2.registerOutParameter (4, Types.VARBINARY);
cstmt2.execute ();

long read_this_time = cstmt2.getLong (2);
byte [] bytes_this_time = cstmt2.getBytes (4);

System.out.print ("Read " + read_this_time + " bytes: ");

int j;
for (j = 0; j < read_this_time; j++)
System.out.print (bytes_this_time [j] + " ");
System.out.println ();

i += read_this_time;
}

cstmt1.close ();
cstmt2.close ();

[C. Ferguson]

Ok,
  here's how I've gone about reading a blob, but this won't be an example using stored procedures.

  ...
  // The blob is a gif file.

    FileOutputStream fos;
   try {
     fos = new FileOutputStream(test.gif);

    String query = "select myblob from mytable where myclause = 1";
    Statement ask = myconnection.createStatement();
   ResultSet answer = ask.execute(query);

   while (answer.next()) {
        Blob blob = answer.getBlob(1);

        InputStream movdata = blob.getBinaryStream();
        byte inbuf = new byte [4*1024];

        int bytesRead;
        int totalRead = 0;

        while  (( bytesRead = movData.read(inbuf)) != -1) {
            totalRead = totalRead + bytesRead;
            fos.write(inbuf, 0, bytesRead);
        }
        System.out.println("The total bytes read is " + totalRead);

     }
     ask.close();
     fos.close();
}
catch ...
 
hope this helps,
cindy

[tst...@my-deja.com]

In article <38C3FD97...@rationalconcepts.com>,
"C. Ferguson" <c_fer...@rationalconcepts.com> wrote:
>
> --------------878526CCBC71F5471301F16E
> Content-Type: text/plain; charset=iso-8859-1
> Content-Transfer-Encoding: 8bit

>
> Ok,
> here's how I've gone about reading a blob, but this won't be an
example
> using stored procedures.
>

Well, yes, I'd also rather use streams to read/write BLOBS,
but we are using Oracle 8.0.5. According to Oracle streams
only work reliably with 8.1.x, so we have to use the stored
procedures.

Thanks anyway.

Thomas Stets

[C. Ferguson]

That's odd.
I run that against an 8.0.4 database... reliably...

cindy

tst...@my-deja.com wrote:

> In article <38C3FD97...@rationalconcepts.com>,

> "C. Ferguson" <c_fer...@rationalconcepts.com> wrote:
> >
> > --------------878526CCBC71F5471301F16E
> > Content-Type: text/plain; charset=iso-8859-1
> > Content-Transfer-Encoding: 8bit
> >

> > Ok,
> > here's how I've gone about reading a blob, but this won't be an
> example
> > using stored procedures.
> >
>

> Well, yes, I'd also rather use streams to read/write BLOBS,
> but we are using Oracle 8.0.5. According to Oracle streams
> only work reliably with 8.1.x, so we have to use the stored
> procedures.
>
> Thanks anyway.
>
> Thomas Stets
>