Subquery works in 9i but not in 11g

Statement below is working on Oracle 9i but not on Oracle 11g

SELECT *
FROM
(
    SELECT 0 scrnfail_rate, '9' zz, 7 hh FROM DUAL
    UNION ALL
    SELECT 0 scrnfail_rate, '9' zz, 7 hh FROM DUAL
)
WHERE zz IS NOT NULL
AND TO_CHAR (hh) NOT IN
(
    SELECT
        DECODE
        (
            scrnfail_rate, 0, -1,
                ROUND (LEVEL * 1 / (scrnfail_rate / 100)) 
                - 
                ROUND (1 / (2 * (scrnfail_rate / 100)))
        ) AS nno
    FROM   DUAL
    WHERE   NVL (scrnfail_rate, 0) > 0
    CONNECT BY   LEVEL <= ROUND(9 * scrnfail_rate / 100)
)

It looks like Oracle 11g is ignoring where decode or even where clause in the subquery. This query should return two rows as it does on Oracle开发者_如何学运维 9i, but results ORA-01476: divisor is equal to zero on Oracle 11g EE 11.2.0.1.0 - 64bit.

---

Can anyone help? Thanks!

---

I found that there is a bug in Oracle 11.2.0.1.0 which was causing this problem.

alter session set optimizer_features_enable='11.1.0.7'

changing optimizer features solvesthis problem.

---

If you run into this issue and removing subqueries from of your SQL scripts is not an option for you I suggest you go with Zsuetams solution and use:

alter session set optimizer_features_enable='11.1.0.7';

You might even want to alter the system settings of your Oracle instance so that this optimizer setting automatically stays in effect for all sessions as long as the database is mounted by executing:

alter system set optimizer_features_enable='11.1.0.7' scope=both;

as an user with ALTER SYSTEM system privilege (e. g. SYS user).

If you fear any side effects of such a system wide deployment, e. g. you are running a cluster and you fear that tempering with Oracle instance system settings might have an effect on other parties as well (and retesting everything isn’t reasonable)… Then your application container might come to rescue by providing some way to extend its JNDI DataSource resource configuration with some sql initialization statements. The connection factory would then execute those statements once during the creation of the connections to this data source.

If you are using Tomcat 7 for example use the initConnectionSqls parameter:

    <Resource name="application.datasource" auth="Container"
              type="javax.sql.DataSource" driverClassName="oracle.jdbc.OracleDriver"
[...]
              initConnectionSqls="alter session set optimizer_features_enable=&apos;11.1.0.7&apos;" />

NOTE: If you are using DBCP >= 1.3.1/1.4.1 (not released yet) you probably already have to use the parameter "connectionInitSqls" instead of "initConnectionSqls" as versions 1.3 and 1.4 of DBCP incorrectly use "initConnectionSqls" as the name of this property for JNDI object factory configuration.

Have a look at Apache Tomcat 7 - JNDI Resources HOW-TO and Apache Commons - Database Connection Pooling Configuration for more information on this.

Oh, and finally: An even better solution might be to upgrade to Oracle 11g version 11.2.0.2.0 after all ;-)

---

Unfortunately, I can't reproduce it.

As a workaround, try this:

SELECT  *
FROM    (
        SELECT  0 scrnfail_rate, '9' zz, 7 hh
        FROM    DUAL
        UNION ALL
        SELECT  0 scrnfail_rate, '9' zz, 7 hh
        FROM    DUAL
        )
WHERE   zz IS NOT NULL
        AND TO_CHAR (hh) NOT IN
        (
        SELECT  DECODE
                (
                scrnfail_rate, 0, -1,
                ROUND (LEVEL * 1 / (DECODE(scrnfail_rate, 0, 1, scrnfail_rate) / 100)) - ROUND (1 / (2 * (DECODE(scrnfail_rate, 0, 1, scrnfail_rate) / 100)))
                ) AS nno
        FROM    DUAL
        WHERE   NVL (scrnfail_rate, 0) > 0
        CONNECT BY
                LEVEL <= ROUND(9 * scrnfail_rate / 100)
        )