Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.tools -> Re: SQLException:ORA-01002: fetch out of sequence when fetching Clobs

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

From: Bhooshan S. Prabhu <bhooshan.prabhu_at_citicorp.com>
Date: 29 Jun 2001 23:35:20 -0700
Message-ID: <2cf4efe2.0106292235.4e6f4236@posting.google.com>

"Victor Grazi" <vgrazi_at_bnyesi.com> wrote in message news:<XtT_6.1912$1m4.322889_at_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;
> }



Hello,

I came across the following on metalink.oracle.com under "Oracle JDBC Frequently Asked Questions". Hope it's of help to you.

Regards
Bhooshan


Error Message: "ORA-01002: fetch out of sequence" A JDBC Connection by default has the AutoCommit turned ON. However, to use a SQL that has 'for update' you need to have autoCommit to be turned OFF.

Hence, the solution is to set autocommit to false.


Received on Sat Jun 30 2001 - 01:35:20 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US