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

Home -> Community -> Usenet -> c.d.o.misc -> Writing CLOB – ORA-01002: Fetch out of sequence

Writing CLOB – ORA-01002: Fetch out of sequence

From: Thomas Konrath <thomas.konrath_at_fh-joanneum.at>
Date: 3 Sep 2003 05:23:27 -0700
Message-ID: <cfaa8a52.0309030423.4f512895@posting.google.com> 





Hi !!!



I have the following problem:



I am working in a project which uses the Cocoon framework. 
I have a table which has basically the following columns:



Table name: userContent


id	NUMBER


original XMLTYPE


text	CLOB



The user can save some XML content in this table (column ‘original').
Than he can choose some transform routines, which converts the XML
content in plain text (which is then saved in the column ‘text')



Normally, all this works fine (writing XML into the table, convert it
to plain text and write it into the CLOB column).



But when the user inserts a new row in the table or removes a row from
it, I get the following error message, when I try to write to the CLOB
column.



Error Message: ORA-01002: FETCH out of sequence



I think this problem occurs because of the connection pooling (but I
am not sure). When I create a database connection directly to the
database, then all works fine. This error only occurs when I use the
connection from the connection pooling.



Has anyone else this problem or had already solved it?
I would by thankful for any help … thanks



Thomas




Here is my java code, where I change the content of the CLOB column:
…


Connection con = null;


PreparedStatement pStm = null;


PreparedStatement pStm2 = null;


ResultSet rs = null;


java.io.Writer writer = null;


try


{


    /*Class.forName("oracle.jdbc.driver.OracleDriver");
    con = DriverManager.getConnection("jdbc:oracle:thin:@dbserver:1521:dbinstance",
"user", "user");


    ==> When I use this, everything works 
    */



    Context ctx = new InitialContext();


    DataSource ds = (DataSource) ctx.lookup("jdbc/myDB");
    con = ds.getConnection();




    con.setAutoCommit(false);   



    String updateQuery = "UPDATE userContent "


                    + " SET "


                    + " text = EMPTY_CLOB() "

                    + " WHERE id = ? ";



    


    pStm = con.prepareStatement(updateQuery);
    pStm.setInt(1, Integer.parseInt(ri) );
    log.debug("Executing:" + updateQuery);
    if ( pStm.executeUpdate() > 0 )


        log.debug(">>>>> UPDATE with EMPTY_CLOB() was successful
!!!");
    




    log.debug("Executing was successful");



    updateQuery = "SELECT text "


                    + " FROM userContent "

                    + " WHERE id = ?"

                    + " FOR UPDATE";



    pStm2 = con.prepareStatement(updateQuery);			// in this line a get
the exeception
    


    pStm2.setInt(1, Integer.parseInt(ri));
    log.debug("Executing:" + updateQuery);
    rs = pStm2.executeQuery();



    rs.next();



    oracle.sql.CLOB pcna = ( (oracle.jdbc.OracleResultSet)
rs).getCLOB(1);




    log.debug("Getting writer from CLOB ...");
    writer = pcna.getCharacterOutputStream();



    log.debug("Writing result 2 into CLOB ...");
    writer.write(result2.toString());



    writer.flush();


    writer.close();


    writer = null;



    pcna = null;



    log.debug("Commit changes ...");



    con.commit();



    writer = null;



}


catch(SQLException se)


{


    con.rollback();


    se.printStackTrace();


}


finally


{


    log.debug("Closing all objects ...");



    if ( writer != null )


    {

        log.debug("######## Closing writer ...");
        writer.close();
        writer = null;




    }




    if ( rs != null)


    {

        log.debug("######## Closing resultset ...");
        rs.close();
        rs = null;




    }



    if ( pStm2 != null)


    {

        log.debug("######## Closing statement 2 ...");
        pStm2.close();
        pStm2 = null;




    }



    if ( pStm != null )


    {

        log.debug("######## Closing statement ...");
        pStm.close();
        pStm = null;




    }



    if ( con != null )


    {

        log.debug("Closing connection ...");
        con.setAutoCommit(true);
        con.close();
        con = null;




    }




}


…




Here is the exception stack trace from the error:



..


DEBUG [web-5] (PrepareEdit.java:227) - Executing:UPDATE userContent
SET  text = EMPTY_CLOB()  WHERE id = ?


DEBUG [web-5] (PrepareEdit.java:230) - Executing was successful
DEBUG [web-5] (PrepareEdit.java:239) - Executing:SELECT text  FROM
userContent  WHERE id = ? FOR UPDATE


ERROR [web-5] (PrepareEdit.java:264) - Error while performing update.
ERROR [web-5] (PrepareEdit.java:265) - null ORA-01002: FETCH out of
sequence.


java.sql.SQLException: ORA-01002: FETCH out of sequence.

 	at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
  	at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:573)
        at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1891)
        at oracle.jdbc.ttc7.TTC7Protocol.executeFetch(TTC7Protocol.java:955)
        at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
        at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2520)
        at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2672)
        at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:589)
        at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:527)
        at jrun.sql.JRunPreparedStatement.executeQuery(JRunPreparedStatement.java:73)
>>      at comp.myapp.PrepareEdit.act(PrepareEdit.java:240) << my java
class
        at org.apache.cocoon.components.treeprocessor.sitemap.ActTypeNode.invoke(ActTypeNode.java:131)
        at org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invokeNodes(AbstractParentProcessingN
ode.java:85)
        at org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.invoke(PreparableMatchNode.java:156)
        at org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invokeNodes(AbstractParentProcessingN
ode.java:109)
        at org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(PipelineNode.java:140)
        at org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invokeNodes(AbstractParentProcessingN
ode.java:109)
        at org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(PipelinesNode.java:144)
        at org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreeProcessor.java:328)
        at org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreeProcessor.java:293)
        at org.apache.cocoon.components.treeprocessor.sitemap.MountNode.invoke(MountNode.java:131)
        at org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invokeNodes(AbstractParentProcessingN
ode.java:85)
        at org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.invoke(PreparableMatchNode.java:156)
        at org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invokeNodes(AbstractParentProcessingN
ode.java:109)
        at org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(PipelineNode.java:140)
        at org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invokeNodes(AbstractParentProcessingN
ode.java:109)
        at org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(PipelinesNode.java:144)
        at org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreeProcessor.java:328)
        at org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreeProcessor.java:293)
        at org.apache.cocoon.Cocoon.process(Cocoon.java:575)
        at org.apache.cocoon.servlet.CocoonServlet.service(CocoonServlet.java:999)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
        at jrun.servlet.ServletInvoker.invoke(ServletInvoker.java:106)
        at jrun.servlet.JRunInvokerChain.invokeNext(JRunInvokerChain.java:42)
        at jrun.servlet.JRunRequestDispatcher.invoke(JRunRequestDispatcher.jaDEBUG


[web-5] (PrepareEdit.java:270) - Clos


ing all objects ...


va:241)

        at jrun.servlet.ServletEngineService.dispatch(ServletEngineService.java:527)
        at jrun.servlet.http.WebService.invokeRunnable(WebService.java:172)
        at jrunx.scheduler.ThreadPool$DownstreamMetrics.invokeRunnable(ThreadPool.java:348)
        at jrunx.scheduler.ThreadPool$ThreadThrottle.invokeRunnable(ThreadPool.java:451)
        at jrunx.scheduler.ThreadPool$UpstreamMetrics.invokeRunnable(ThreadPool.java:294)
        at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)


Received on Wed Sep 03 2003 - 07:23:27 CDT












Original text of this message













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