Writing CLOB – ORA-01002: Fetch out of sequence
Thomas Konrath
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)
Sudsy
> 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.
Not quite. You have a transaction still open since you set
AutoCommit to false. Move the call to the indicated place.
> 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);
>>>>>>>>>>>>>>>>>>> Remove from here
>
> 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");
<<<<<<<<<<<<<<<<<<< Insert here
Note also that you wouldn't run into this problem if you set the
default column value to be EMPTY_CLOB().