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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: