Java.sql.SQLException: ORA-01002: Sequence Selection on XATransaction

Question

The java.sql.SQLException is sometimes thrown on the same data: ORA-01002: fetch from the sequence, but in most attempts everything works fine.

Java application running on Glassfish 3.1.2.2. Can someone explain to me where the problem is?

@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> 

Answer 1

This is very strange, but the problem was solved by removing the @ConcurrencyManagement (ConcurrencyManagementType.BEAN) annotation

Can anyone explain this?

Answer 2

The code is incomplete, so I can only guess:

• the cursor was closed and you tried to select again
• You made the choice to update and committed, and then tried to extract the next line.

Answer 3

Is your connection established for auto-commit? Failure from commit or rollback may cause this exception.

I also noticed that your SQL is not surrounded by begin / end, as in Oracle docs or {}, as in this example and Oracle Javadoc .

Answer 4

To ensure that when an exception is deleted, all rows are processed

Modify the code below to turn on counter i to get processed rows and find the actual number 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); }