Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
HomeAnnouncementsWhite Papers
Discussion GroupsFirst AidDatabasesJavaBeansGUIJava 3DVirtual MachineCORBASecurityToolsGeneral
Java DirectoryOpen Source ProjectsSample Book ChaptersUser GroupsWeb Resources
Related Topics
Databases.NETMore Topics ...

Java Forum / Databases / June 2004

Tip: Looking for answers? Try searching our database.

WebLogic 8.1, Oracle 8.1.7, Blob - strange problem

Thread view: 
Kevin W - 28 May 2004 19:45 GMT
Error: ORA-01002: fetch our of sequence

I have searched the groups and BEA's website for a solution.  While I
can find many references to this error, the problems reported and the
situations seem to be different from what I'm experiencing.

We have a Change Management process that consists of several different
environments: Developer Sandbox, DevTest, SysTest, MigrationTest,
Production.

The code has been running for a few months under WebLogic 6.1 sp4
without problem.  We recently upgraded the WebLogic servers to 8.1 and
began getting this error in the Production environment. We have not
been able to duplicate the problem in any other environment; it only
occurs in Production.  In all the other environments, the error has
never occured.  There are several applications other than the one
exhibiting the error that are running without problem, but these
applications do not use Blobs.

When we first bring up the WebLogic server, all will run just fine.
We can upload files/images to the Oracle server and store them in a
Blob without error.  After the server has been running for 3 to 4
hours, we will begin to receive the "ORA-01002: fetch our of sequence"
error when we attempt to upload a file. At that time, we can still
successfully read from the Blob table. The file size doesn't matter
nor does issue seem to be affected by the number of uploads.  I have
continously uploaded documents for about an hour (much more than the
normal load) without error.

If I undeploy the JDBC Data Source and the Connection Pool and then
redeploy them, the uploads will again function correctly for 3 to 4
hours at which time we begin receiving the errors again.

I have searched the WebLogic logs and can find nothing that seems to
correlate to when these errors begin.  The Java application traps the
error and it is not logged in the WLS log at all.

Version Info:
WebLogic Server 8.1
Oracle Server 8.1.7.4.12
Oracle Client on WLS server 8.1.7
We are using Oracle's thin JDBC driver (ocijdbc8), not the Weblogic
driver.

Code:
=================================================
int result = 0;
int id = 0;
Connection conn = null;
ResultSet rs = null;
try {
 // Get our connection
 conn = ServiceLocator.getInstance().getDBConnection(Services.DATASOURCE);
 if (conn != null) {
   // Create the new record with an empty blob.
   PreparedStatement prepStmt = conn.prepareStatement
     (CompanyImageFilter.getSqlCreate());
   // SQL returned by CompanyImageFilter.getSqlCreate()
   // " INSERT INTO SP2_COMP_IMAGE " +
   // " (CREATED_DATE, CREATED_BY, UPDATED_DATE, UPDATED_BY, COMP_ID,
        CONTENT_TYPE, CONTENT_SIZE, CONTENT_PATH, CONTENT) " +
   // " VALUES(?, ?, ?, ?, ?, ?, ?, ?, EMPTY_BLOB()) ";
   Timestamp now = XASystem.getTimestamp();
   prepStmt.setTimestamp(1, now);
   prepStmt.setString(2, caller.toString());
   prepStmt.setTimestamp(3, now);
   prepStmt.setString(4, caller.toString());
   prepStmt.setInt(5, companyImage.getCompanyID());
   prepStmt.setString(6, companyImage.getContentType());
   prepStmt.setInt(7, companyImage.getContentSize());
   prepStmt.setString(8, companyImage.getContentPath());
   result = prepStmt.executeUpdate();
 
   // Fetch the primary key of the new record.
   prepStmt = conn.prepareStatement(CompanyImageFilter.getSqlSelectMaxID());
   rs = prepStmt.executeQuery();
   if (rs.next())
     id = rs.getInt(1);

   // Select the new empty blob for update.
   prepStmt = conn.prepareStatement
     (CompanyImageFilter.getSqlSelectForBlobUpdate());
   // SQL returned by CompanyImageFilter.getSqlSelectForBlobUpdate()
   // " SELECT CONTENT FROM SP2_COMP_IMAGE WHERE ID = ? FOR UPDATE "
   prepStmt.setInt(1, id);
   rs = prepStmt.executeQuery();
   java.sql.Blob dbBlob = null;
   if (rs.next()) {
     dbBlob = rs.getBlob(1);
   }

   // Copy the byte stream to the blob.               
   java.io.InputStream is = new java.io.ByteArrayInputStream
     (companyImage.getContent());
   java.io.OutputStream os =
     ((weblogic.jdbc.vendor.oracle.OracleThinBlob)dbBlob)
     .getBinaryOutputStream();
   byte[] inBytes = new byte[65534]; // Per BEA, default size for
connection.
   int numBytes = is.read(inBytes);
   while (numBytes > 0) {
     os.write(inBytes, 0, numBytes);
     numBytes = is.read(inBytes);
   }
   os.flush();

   // Update the blob in the database.
   prepStmt = conn.prepareStatement(CompanyImageFilter.getSqlUpdateBlobByID());
   // SQL returned by CompanyImageFilter.getSqlUpdateBlobByID()
   // " UPDATE SP2_COMP_IMAGE SET CONTENT = ? WHERE ID = ? "
   prepStmt.setBlob(1, dbBlob);
   prepStmt.setInt(2, id);
   prepStmt.executeUpdate();
   prepStmt.close();
   conn.close();
 }
} catch (Exception ex) {
 iExceptionHandler(ex, conn);
}
=================================================

Any help or suggestions will be greatly appreciated.

Thanks,
Kevin
Joe Weinstein - 28 May 2004 20:28 GMT
> Error: ORA-01002: fetch our of sequence
>
[quoted text clipped - 121 lines]
> Thanks,
> Kevin

Hi. Several suggestions:
1 - Be sure the driver you're using is listed ahead of all weblogic stuff
in the server classpath, as it is printed out at startup.
2 - Have you opened an official BEA support case for this?
3 - I assume you are using our jdbc connection pools. Can you try turning
off the pool's statement cache? The issue may be something to do with
are-use of prepared statements.

Joe Weinstein at BEA
Kevin W - 04 Jun 2004 07:24 GMT
> > Error: ORA-01002: fetch our of sequence
> >
[quoted text clipped - 131 lines]
>
> Joe Weinstein at BEA

Joe:

Thank you for the suggestions.

1 - the driver is loading from the Classes14.zip file that installed
with WLS 8.1.  It seems to be loading correctly.
2 - Yes, I do have a ticket open.  But nothing has yet resulted.
3 - As per your suggestion, I tried this.  It makes no difference.

I also have a team of Oracle experts at HP (they are hosting the
servers) looking into it, but it seems the problem is with WLS 8.1 or
it's interaction with the Oracle driver.

Interesting note:  I just learned that the Production environment is
Win2K sp3 while all the other environments have been upgraded to Win2K
sp4.  Production is scheduled for update on 12 June.

-- Kevin
Joe Weinstein - 04 Jun 2004 16:37 GMT
>>>Error: ORA-01002: fetch our of sequence
>>>
[quoted text clipped - 137 lines]
>
> Thank you for the suggestions.

Hi. We'll solve this...

> 1 - the driver is loading from the Classes14.zip file that installed
> with WLS 8.1.  It seems to be loading correctly.

I assume you mean classes12.zip. There is no classes14.zip. I highly suggest
that you download Oracle's latest appropriate version of classes12.zip,
and have it explicitly listed in the server's -classpath argument, before
weblogic.jar. The driver we ship often becomes obsolete in weeks.

> 2 - Yes, I do have a ticket open.  But nothing has yet resulted.

Send me the case number.

> 3 - As per your suggestion, I tried this.  It makes no difference.

Ok. That's good information. If you can please update the official ticket/case
with this feedback and info, it will help.
thanks,
Joe

> I also have a team of Oracle experts at HP (they are hosting the
> servers) looking into it, but it seems the problem is with WLS 8.1 or
[quoted text clipped - 5 lines]
>
> -- Kevin
D Rolfe - 04 Jun 2004 09:28 GMT
> Error: ORA-01002: fetch our of sequence

You mean 'fetch *out* of sequence, don't you? :)

The 'official' explanation of this message is:

Reason: In a host language program, a FETCH call was issued out of
sequence. A successful parse-and-execute call must be issued before a
fetch. This can occur if an attempt was made to FETCH from an active set
after all records have been fetched. This may be caused by fetching from
a SELECT FOR UPDATE cursor after a commit. A PL/SQL cursor loop
implicitly does fetches and may also cause this error.

In order to use BLOBS you must use "SELECT FOR UPDATE", which is
oracle-speak for row level locking. Row level locking breaks if your
Connection object has autoCommit set to true. Or someone else issues a
commit on your connection. Could it be that after 3-4 hours you start
getting connection objects with autoCommit set to false?

More importantly: You are calling commit at the end of this, aren't you?
If you don't you be seeing the consequences of running out of resources
to read and write BLOBs because nothing's been committed....

You don't need to do the update statement. a java.sql.blob is a pointer
to a location inside the database. Once you have a blob you can read or
write to it without having to use SQL statements other than 'commit'.

I don't have a defintiive answer to your problem but I've added a few
lines to your code. If that doesn't work stop using the connection pool,
create your own connection and use it no more than about 20 times before
re-creating it. While I wouldn't normally recommend doing something like
this this is a production system we're talking about and it's Friday....

> Version Info:
> WebLogic Server 8.1
[quoted text clipped - 13 lines]
>   conn = ServiceLocator.getInstance().getDBConnection(Services.DATASOURCE);
>   if (conn != null) {

      // Make sure we aren't commiting every statement
      conn.setAutoCommit(false);

>     // Create the new record with an empty blob.
>     PreparedStatement prepStmt = conn.prepareStatement
[quoted text clipped - 15 lines]
>     result = prepStmt.executeUpdate();
>  
      // Without this line Oracle will never know to release the
      // resources it used parsing your INSERT.
      prepStmt.close();

>     // Fetch the primary key of the new record.
>     prepStmt = conn.prepareStatement(CompanyImageFilter.getSqlSelectMaxID());
>     rs = prepStmt.executeQuery();
>     if (rs.next())
>       id = rs.getInt(1);

      // Without these lines Oracle will never know to release the
      // resources it used parsing your SELECT. Why don't you get the ID
      // first and then just do an INSERT?
      rs.close();
      prepStmt.close();

>     // Select the new empty blob for update.
>     prepStmt = conn.prepareStatement
[quoted text clipped - 22 lines]
>     }
>     os.flush();

      rs.close();
      prepStmt.close();

      // You don't need this statement - all your doing is assigning
      // your blob pointer to itself....
      /**

>     // Update the blob in the database.
>     prepStmt = conn.prepareStatement(CompanyImageFilter.getSqlUpdateBlobByID());
[quoted text clipped - 4 lines]
>     prepStmt.executeUpdate();
>     prepStmt.close();
      **/
      conn.commit();

>     conn.close();
>   }
[quoted text clipped - 7 lines]
> Thanks,
> Kevin

David Rolfe
Orinda Software
Dublin, Ireland
---------------------------------------------------------------------------
Orinda Software make OrindaBuild, a utility for writing JDBC calls for
stored procedures and tables.
www.orindasoft.com


Free Magazines

Get these publications absolutely FREE for up to 12 months. There are no hidden fees and no obligation. Simply choose a title, complete the application form and submit it. Read more ...

Oracle MagazineNetwork ComputingComputer WorldBio-IT WorldeWeekInformation WeekInfosecurity
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.