SQLException:ORA-01002: fetch out of sequence when fetching Clobs

From: Victor Grazi <vgrazi_at_bnyesi.com>
Date: Thu, 28 Jun 2001 17:06:14 GMT
Message-ID: <auJ_6.240$1m4.25667@typhoon1.gnilink.net>

We are trying to use clobs in our Java application (JDK1.22 using JDBC 2 OracleThin drivers)

Whenever we do a query of a Clob column, the executeQuery throws a SQLException:

    ORA-01002: fetch out of sequence

Can you suggest how to fix this?

Thanks/Victor Grazi

Snippet:

    public int insertClob(int primaryKey, String fileName) throws java.io.FileNotFoundException

    {
  int rc = 0;
  System.out.println("insertClob(" + primaryKey+", '" + fileName + "')");

  File inFile = new File(fileName);
  System.out.println(inFile.getAbsolutePath());   if(!inFile.isFile())
{

   throw new FileNotFoundException(fileName);   }

  PreparedStatement pstmt = null;
  ResultSet rs = null;

  try
{

   FileReader inReader = new FileReader(inFile);    //advance to a new row in a result set    pstmt = _conn.prepareStatement("INSERT INTO blob_test ("

                                             + "blobid, "
                                             + "charlob) "
                                             + "VALUES ( ?, empty_clob())"
            );

   pstmt.setInt( 1, primaryKey );

   pstmt.executeUpdate();
   pstmt.close();

   pstmt =
    _conn.prepareStatement("SELECT charlob "

           + " from blob_test "
           + "  where blobid = ?"
           + " for update"
           );

   pstmt.setInt( 1, primaryKey );

//////////////// This is the call that blows up ///////////////////

   rs = pstmt.executeQuery();

   if(rs.next())
   {
    CLOB clob = ((OracleResultSet)rs).getCLOB("charlob");     Writer bWriter = clob.getCharacterOutputStream(); //or use AsciiOutputStream ?

    while(true)
    {

     int i = inReader.read();
     if( i == -1)
     {
      break;
     }
     bWriter.write(i);

    }
 // bWriter.flush();

    bWriter.close();
    rs.close();
    inReader.close();
    rc = 1;
   }
   else
   {
    //re-throw as GtmsSystemException
    rc = -3;
   }
  }
  catch(SQLException e)
{

   writeToSvrLog("insertClob threw SQLException:" + e.getMessage());    //re-throw as GtmsSystemException
   rc = -1;
  }
  catch(IOException e)
{

   //re-throw as GtmsSystemException
   writeToSvrLog("insertClob threw IOException:" + e.getMessage());    rc = -2;
  }

  return rc;
    } Received on Thu Jun 28 2001 - 12:06:14 CDT