Announcement Announcement Module
Collapse
No announcement yet.
Upgrading to Oracle 11g (ojdbc6) & KeyHolder Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Upgrading to Oracle 11g (ojdbc6) & KeyHolder

    Hello,
    I'm attempting to upgrade code from Oracle 10g to Oracle 11g (v. 11.1.0.7.0 using ojdbc6.jar). I am currently using Spring 2.5.6 and Java JDK 1.6.0_10.
    I'm having a problem with the use of KeyHolder to retrieve the key after an insert.

    The following call succeeds for Oracle 10 but not Oracle 11.

    Code:
    public void testNamedParamOracle11_likeDao() throws SQLException {
            
            
            NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(
                    getDataSource());
    
            
            final TstCfCombo cfcomboTemp = new TstCfCombo(null, 39, 38,
                    37, 36, 35, 34, 33, 32);
    
            SqlParameterSource namedParamsInsert = new BeanPropertySqlParameterSource(
                    cfcomboTemp.getConfidenceFactor());
            KeyHolder keyHolder = new GeneratedKeyHolder();
            
            jdbcTemplate.update(fSqlInsert,
                    namedParamsInsert, keyHolder,
                    new String[] { "cf_combo_id" });
    }
    The trace when run with Oracle 11 is:

    java.lang.ArrayIndexOutOfBoundsException: 8
    at oracle.jdbc.driver.OracleSql.computeBasicInfo(Orac leSql.java:950)
    at oracle.jdbc.driver.OracleSql.getSqlKind(OracleSql. java:623)
    at oracle.jdbc.driver.OraclePreparedStatement.<init>( OraclePreparedStatement.java:1212)
    at oracle.jdbc.driver.T4CPreparedStatement.<init>(T4C PreparedStatement.java:28)
    at oracle.jdbc.driver.T4CDriverExtension.allocatePrep aredStatement(T4CDriverExtension.java:68)
    at oracle.jdbc.driver.PhysicalConnection.prepareState ment(PhysicalConnection.java:3140)
    at oracle.jdbc.driver.PhysicalConnection.prepareState ment(PhysicalConnection.java:3042)
    at oracle.jdbc.driver.PhysicalConnection.prepareState ment(PhysicalConnection.java:6022)
    at org.springframework.jdbc.core.PreparedStatementCre atorFactory$PreparedStatementCreatorImpl.createPre paredStatement(PreparedStatementCreatorFactory.jav a:233)
    at org.springframework.jdbc.core.JdbcTemplate.execute (JdbcTemplate.java:585)
    at org.springframework.jdbc.core.JdbcTemplate.update( JdbcTemplate.java:824)
    at org.springframework.jdbc.core.namedparam.NamedPara meterJdbcTemplate.update(NamedParameterJdbcTemplat e.java:242)
    at ....cache.test.CfComboListDaoTest.testNamedParamOr acle11_likeDao(CfComboListDaoTest.java:184)
    ...


    If I take out the KeyHolder, as follows, then the code works in both Oracle 10 and Oracle 11. (But I really do need the key!)

    Code:
        public void testNamedParamOracle11_noKeyHolder() throws SQLException {
            
            
            NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(
                    getDataSource());
    
            
            final TstCfCombo cfcomboTemp = new TstCfCombo(null, 39, 38,
                    37, 36, 35, 34, 33, 32);
    
            SqlParameterSource namedParamsInsert = new BeanPropertySqlParameterSource(
                    cfcomboTemp.getConfidenceFactor());
            //KeyHolder keyHolder = new GeneratedKeyHolder();
            
            jdbcTemplate.update(fSqlInsert,
                    namedParamsInsert);
            
        }
    Is this a bug in Spring or the Oracle 11 code? Has the usage of KeyHolder or NamedParameterJdbcTemplate changed? Or am I doing something wrong here?

    I know there are other ways to insert with parameters and retrieve the key, but I really like the simplicity of the above method, and I see no reason why it shouldn't continue to work with Oracle 11.

    Thanks in advance.

  • #2
    Known Oracle issue

    This is a problem that has been discussed elsewhere:
    http://forums.oracle.com/forums/thre...6535&tstart=46
    http://forums.oracle.com/forums/thre...sageID=2698647

    and is a known Oracle issue:
    https://metalink.oracle.com/metalink...G&p_id=7112447
    "PREPARESTATEMENT GET ARRAYINDEXOUTOFBOUNDSEXCEPTION WHEN IN PARAM. AMOUNT IS > 7 "


    I tested the same code with only 7 parameters and it works fine.

    Comment

    Working...
    X