Big CLOBs

From: Huck <l.iannone_at_libero.it>
Date: Thu, 13 Jun 2002 10:06:03 GMT
Message-ID: <f8_N8.8125$Kt3.186815@twister2.libero.it>

Hi alll,
I am using Oracle 9i release
I have to store a CLOB into a table of which I report the DDL below:

the problem is that when I try to store CLOBs that are bigger that 16K (16384 bytes) the storing process ends without problems but I cannot retrieve the CLOB anymore and I get the following exception when i execute the method read of the CLOB InputStream

Written bytes: 16384
java.io.IOException: ORA-21560: l'argomento 2 è nullo, non valido, o fuori intervallo
ORA-06512: a "SYS.DBMS_LOB", line 648
ORA-06512: a line 1

 java.lang.Throwable(java.lang.String)
 java.lang.Exception(java.lang.String)
 java.io.IOException(java.lang.String)

 void oracle.jdbc.dbaccess.DBError.SQLToIOException(java.sql.SQLException)  boolean oracle.jdbc.driver.OracleClobInputStream.needBytes()  int oracle.jdbc.driver.OracleBufferedStream.read(byte [], int, int)  int oracle.jdbc.driver.OracleBufferedStream.read(byte [])  java.lang.String
com.sword.xmlcm.persistenceLayer.oracle.TestCLOB.retrieveCLOB(java.lang.Stri ng, java.lang.String)
 void com.sword.xmlcm.persistenceLayer.oracle.TestCLOB.main(java.lang.String [])

CREATE TABLE "XMLCM"."CLOB_TEST" ("CLOB_FIELD" CLOB NOT NULL, "KEY"     VARCHAR2(128 byte) NOT NULL)
    TABLESPACE "USERS" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS     255
    STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS     2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)     LOGGING LOB("CLOB_FIELD") STORE AS ( TABLESPACE "USERS"     STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS     2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1)     ENABLE
    STORAGE IN ROW
    NOCACHE CHUNK 4096 PCTVERSION 10) to store a CLOB I use the following Java Code

void addCLOB(String repository, String key, String filePath) {  try{
  String insertEMPTYCLOB= " BEGIN INSERT INTO "+repository+" (KEY,CLOB_FIELD) VALUES(?,EMPTY_CLOB()) RETURN CLOB_FIELD INTO ?; END;";   myConnection.setAutoCommit(false);
  CallableStatement aCallableStatement=
myConnection.prepareCall(insertEMPTYCLOB);   aCallableStatement.registerOutParameter(2,Types.CLOB);

  aCallableStatement.setString(1,key);
  aCallableStatement.execute();
  CLOB myClob= (CLOB)aCallableStatement.getClob(2);

  File myFile= new File(filePath);

  FileInputStream in= new FileInputStream(myFile);
  OutputStream out= myClob.getAsciiOutputStream();
  byte[] buffer= new byte[myClob.getBufferSize()];

  int i=-1;
  int count=0;
  while ( (i=in.read(buffer))!=-1){
   out.write(buffer,0,i);
   count=count+i;
  }

  System.out.println("Written bytes: "+count);

  in.close();
  out.close();
  aCallableStatement.close();
  myConnection.commit();

}

 catch(Exception e){
  e.printStackTrace();
}

}

wheras to retrieve it I use this code

public String retrieveCLOB(String repository, String key) {

 try{
  String myQuery = "SELECT CLOB_FIELD FROM "+repository+" WHERE KEY=?";   PreparedStatement aPreparedStatement=
myConnection.prepareStatement(myQuery);

  aPreparedStatement.setString(1,key);
  ResultSet myRes= aPreparedStatement.executeQuery();   myRes.next();
  CLOB myClob= (CLOB)myRes.getClob("CLOB_FIELD");   String result="";
  InputStream in=myClob.getAsciiStream();   byte[] buffer= new byte[myClob.getBufferSize()];   int i=-1;
  int count=0;

  while ((i=in.read(buffer))!=-1){
   result=result+ new String(buffer);
  }

  aPreparedStatement.close();

  return result.trim();

}

 catch(Exception e){
  e.printStackTrace();
  return null;
}

}

Where is the mistake???
Gratefully,

Luigi Iannone Received on Thu Jun 13 2002 - 05:06:03 CDT