01002: fetch out of sequence on XATransaction
On the same data sometimes throws the exception java.sql.SQLException: ORA-01002: fetch out of sequence, but in most attempts all working fine.
Java app running on Glassfish 3.1.2.2. Can anybody explain me, where is the problem?
@Singleton
@LocalBean
@Startup
@ConcurrencyManagement(ConcurrencyManagementType.BEAN)
public class MarketCodesSingleton {
@Resource(mappedName="jdbc/sss")
private DataSource source;
private volatile static Map<Interval, String> marketCodes;
@PostConstruct
@Schedule(minute="*/10", hour="*")
public void fillMarketCodes() {
try(Connection conn = source.getConnection()) {
Map<Interval, String> marketCodesInt = new TreeMap<>();
DaoFactory.getMarketCodesDao().fillMarketCodes(marketCodesInt, conn);
marketCodes = Collections.unmodifiableMap(marketCodesInt);
Logger.getLogger(getClass().getName()).log(Level.FINE, "MarketCodes updated");
} catch (SQLException e) {
Logger.getLogger(getClass().getName()).log(Level.SEVERE, "fillMarketCodes exception",e);
throw new EJBException("fillMarketCodes exception",e);
}
}
public String getMarketCode(Long msisdn) {
Interval interval = new Interval(msisdn);
return marketCodes.get(interval);
}
}
DaoFactory.getMarketCodesDao().fillMarketCodes:
private static final String getMarketCodes_SQL = "CALL SERVICE_PKG.GET_MARKET_CODES(?)";
@Override
public void fillMarketCodes(Map<Interval, String> intervals, Connection conn) throws SQLException {
try (CallableStatement cs = conn.prepareCall(getMarketCodes_SQL)) {
//-10 is a OracleTypes.CURSOR
cs.registerOutParameter(1, -10);
cs.execute();
try (ResultSet rs = (ResultSet) cs.getObject(1)) {
//*******Exception throws on the rs.next() in this method*******
while (rs.next()) {
Interval interval = new Interval(rs.getLong("from_no"), rs.getLong("to_no"));
intervals.put(interval, rs.getString("market_code"));
}
}
}
}
Procedure:
procedure GET_MARKET_CODES(
c_cursor OUT SYS_REFCURSOR
) AS
BEGIN
OPEN c_cursor FOR
SELECT from_no, to_no, market_code
FROM market_codes;
END GET_MARKET_CODES;
Connection properties:
<jdbc-connection-pool
connection-creation-retry-interval-in-seconds="5"
datasource-classname="oracle.jdbc.xa.client.OracleXADataSource"
max-pool-size="200"
max-connection-usage-count="1000"
res-type="javax.sql.XADataSource"
steady-pool-size="0"
name="sss_pool"
connection-creation-retry-attempts="5">
<property name="URL" value="jdbc:oracle:thin:@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = xx)))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = xx)))"></property>
<property name="Password" value="***"></property>
<property name="User" value="***"></property>
</jdbc-connection-pool>
The code is incomplete, so I can only guess:
Is your connection set to auto commit? A fetch across a commit or rollback is likely to cause this exception.
I also noticed that your SQL isn't surrounded by begin/end as in the Oracle docs or {} as in this example and the Oracle Javadoc.
To make certain whether when hitting exception all the rows are processed
Modify the below code to include a counter i to get processed rows and find actual count of rows
int i=0;
try{
while (rs.next()) {
Interval interval = new Interval(rs.getLong("from_no"), rs.getLong("to_no"));
intervals.put(interval, rs.getString("market_code"));
i=i+1;
}
}
catch (Exception e)
{
Logger.getLogger(getClass().getName()).log(Level.FINE, "the total rows processed"+ i);
Statement stmt = null;
String query = "select count(1) count_rows
from market_codes";
stmt = con.createStatement();
ResultSet rs1 = stmt.executeQuery(query);
rs1.next();
String countRows = rs1.getString("count_rows");
Logger.getLogger(getClass().getName()).log(Level.FINE,"Actual count of rows"+ countRows);
}