Q

Retrieve a CLOB from the Oracle DBMS in Java

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");
 
 
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 last published in April 2005

Dig Deeper on Oracle Application Server

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

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

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close