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 first published in April 2005

Dig deeper on Oracle Application Server

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close