Oracle8i/JAVA: stored function problem

I've tried to use stored functions within ORACLE8i to generate huge text-files, depending on the query.
The first attempt was a stored function with a VARCHAR2-Result on the declaration side and a java-String as the real result, but this is limited by the 4kB-size of Varchars within ORACLE: only about 4 k are sent.

The next approach has been a data-stream - here are the snippets of the sources:

PLSQL:
create or replace function executeQuery(  queryString Varchar2 )
 return Long
 as LANGUAGE JAVA
 NAME 'QueryClass.executeQuery(java.lang.String) return java.lang.OutputStream';
/

JAVA - stored function:

public class QueryClass {

  public static OutputStream executeQuery(String queryString) {

snip..

     Connection connection = new OracleDriver().defaultConnection();

snip..

    String s1 = new String('a very large String of about 10kb....');     String s = new String("<Datalength>"+s1.length()+"<Datalength>"+s1);

    ByteArrayOutputStream bous = new ByteArrayOutputStream(s.length()+1);

    bous.write(s.getBytes(),0, s.length());     bous.flush();

    return bous;

}

on the Client-Side (it's a Servlet using a thin-Driver)

snip..

      OracleCallableStatement cstmt = (OracleCallableStatement)
      connection.prepareCall ("{? = call ExecuteQuery (?,)}");
      cstmt.registerOutParameter (1, Types.LONGVARBINARY);
      cstmt.setString (2, query);
      cstmt.execute ();

      InputStream bin = null;
      bin = cstmt.getBinaryStream(1);

      byte[] buf = new byte[1024];
      int length = 0;
      int pos = 0;
      while ((bin != null) && ((length = bin.read(buf)) != -1)) {
        pos += length;
        System.err.println(Integer.toString(pos));
        System.err.println(new String(buf));
        result.append(new String(buf));
        buf = new byte[1024];
      }


     bin.close();
     cstmt.close();

as a result: only 4096Bytes are read, I've tried Types.Long, cstmt.getAsciiStream() as well.

There must be an error within this approach, but it should be possible to stream huge amount of data from oracle to a servlet.

Does anybody know where??? Received on Tue Sep 14 1999 - 09:17:19 CDT