I am trying to retrieve a CLOB from the Oracle DBMS in Java so that I can use the output stream to write to it. However, I'm having a problem after I insert the row and try to retrieve the blank CLOB. I'm getting the following, "SQLException: ORA-01002: Fetch out of sequence."

Here's the code: 

 
 
myPreparedStatement =
myConnection.prepareStatement ("INSERT INTO AMC_NEWB_XML VALUES (?, ?,
?, ?)");
 
                   myPreparedStatement.setString(1, mySessionId);
 
     
 
                   myPreparedStatement.setInt(2, 1);
 
                             
 
                   Calendar myCal = Calendar.getInstance();
 
                   java.util.Date myUtilDate = myCal.getTime();
 
                   java.sql.Date mySqlDate = new
java.sql.Date(myUtilDate.getTime());
 
                   myPreparedStatement.setDate(3, mySqlDate);
 
                   OracleConnection myOracleConnection =
(OracleConnection)myConnection;
 
                   myPreparedStatement.setClob(4,
oracle.sql.CLOB.empty_lob());
 
                   
 
                   int myRowCount = myPreparedStatement.executeUpdate();
 
                   myConnection.commit();
 
                   System.out.println("Successful update of
"+myRowCount+" row");
 
                   
 
                   //myReader.close();
 
                   myPreparedStatement.close();
 
                   
 
                   //Now select the clob from the row just created.
 
                   String mySql = "select XML_DATA from AMC_NEWB_XML
where SESSION_ID = ? FOR UPDATE";
 
                   CallableStatement myCallableStatement =
myConnection.prepareCall(mySql);
 
                   myCallableStatement.setString(1, mySessionId);
 
                   boolean myRes = myCallableStatement.execute();
 
                   System.out.println("Retrieved the CLOB from the DB");

    Requires Free Membership to View

    Login

You are using a callableStatment? CallableStatements access PL/SQL. You are just executing a SELECT. Use the PreparedStatment so that you have bind variables.

Use preparedStatment as you did when you registered the empty CLOB. 

 ResultSet rs = myPreparedStatement.execute();
rs.next();
CLOB myClob = ((OracleResultSet) rs).getCLOB("XML_DATA");

// go to work on you CLOB

This was first published in April 2005

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top