| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Writing CLOB - ORA-01002: Fetch out of sequence
Thomas, I am not a Java user but I have come across many such issues in OCI.
Most of them are related to Oracle bugs that have been fixed in newer
releases.
You might want to check the version of client/server you are using and
metalink.oracle.com to see if there is any such known issue.
HTH
Want ease of development or performance? Why not get both! http://www.roguewave.com/products/sourcepro/db/
[Disclaimer: All views expressed here are my personal views and they can be incorrect at times]
"Thomas Konrath" <thomas.konrath_at_fh-joanneum.at> wrote in message
news:cfaa8a52.0309030423.4f512895_at_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(OraclePreparedState
ment.java:589)
> at
oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatem
ent.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(ActTyp
eNode.java:131)
> at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingN
> ode.java:85)
> at
org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.invok
e(PreparableMatchNode.java:156)
> at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingN
> ode.java:109)
> at
org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(Pipel
ineNode.java:140)
> at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingN
> ode.java:109)
> at
org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(Pipe
linesNode.java:144)
> at
org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreeProcess
or.java:328)
> at
org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreeProcess
or.java:293)
> at
org.apache.cocoon.components.treeprocessor.sitemap.MountNode.invoke(MountNod
e.java:131)
> at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingN
> ode.java:85)
> at
org.apache.cocoon.components.treeprocessor.sitemap.PreparableMatchNode.invok
e(PreparableMatchNode.java:156)
> at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingN
> ode.java:109)
> at
org.apache.cocoon.components.treeprocessor.sitemap.PipelineNode.invoke(Pipel
ineNode.java:140)
> at
org.apache.cocoon.components.treeprocessor.AbstractParentProcessingNode.invo
keNodes(AbstractParentProcessingN
> ode.java:109)
> at
org.apache.cocoon.components.treeprocessor.sitemap.PipelinesNode.invoke(Pipe
linesNode.java:144)
> at
org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreeProcess
or.java:328)
> at
org.apache.cocoon.components.treeprocessor.TreeProcessor.process(TreeProcess
or.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:29
4)
> at jrunx.scheduler.WorkerThread.run(WorkerThread.java:66)
Received on Thu Sep 04 2003 - 14:10:39 CDT
 |
 |