This forum is currently locked. You can't register or post questions at this time. (read more)

Sql running on ORACLE 10G but not in Oracle 11g

All posts relating to Oracle PL/SQL development.

Moderator: Tim...

kanthedgaurav
Senior Member
Posts: 51
Joined: Thu Apr 05, 2007 7:15 am
Location: PUNE ,INDIA

Sql running on ORACLE 10G but not in Oracle 11g

Postby kanthedgaurav » Sat Jun 08, 2013 9:20 pm

Hi All,

In code attached SQL is not working on "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production" but it is working fine on "Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi”.
I am not able to figure out what's going wrong with Oracle 11 g ?

Thanks in advance to look this issue .

Code: Select all


CREATE OR REPLACE TYPE split_tbl AS TABLE OF VARCHAR2(32767);
                   
Create table test_10g_11g_3
( SEQ_NUM NUMBER)         
         
Insert into test_10g_11g_3 VALUES(1);
   
COMMIT;

SQL:-
 SELECT *
          FROM test_10g_11g_3
          WHERE (       (SELECT 'ALL'
                             FROM DUAL) = 'ALL'
                        AND
                        seq_num IN ('1')
                     OR (   (SELECT 'ALL'
                                FROM DUAL) != 'ALL'
                         AND (SEQ_NUM) IN (
                                SELECT COLUMN_VALUE
FROM TABLE(split_tbl('ALL')))
                        )
                    )
                   
Thanks,
Kanthed Gaurav

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Sql running on ORACLE 10G but not in Oracle 11g

Postby Tim... » Sat Jun 08, 2013 11:23 pm

Hi.

Well, the result looks pretty sensible to me. The error you get is,

Code: Select all

SELECT COLUMN_VALUE
               *
ERROR at line 10:
ORA-01722: invalid number


If you look at the value returned in the COLUMN_VALUE table, you will see it is "ALL" and you are comparing that with SEQ_NUM, which is a NUMBER type. Since these are two different types, Oracle is doing an implicit conversion. When it tries to do convert "ALL" to a NUMBER, it fails with the error.

Why the difference between versions? The optimizer will often rewrite SQL behind the scenes to make it perform better. It's possible the rewrites that happen in the two versions are slightly different, causing the SEQ_NUM value to be implicitly converted to a VARCHAR2 in 10g, allowing the comparison to take place.

Regardless of what the reason for the difference, your statement is wrong. That fact Oracle 10g may have let you get away with it doesn't detract from the fact your statement is wrong. Whenever you do something that compares different data types you are trusting to luck that Oracle will do the right thing. That is a bug waiting to happen!

So, either the values in your table type should not be strings, or you need to explicitly convert the SEQ_NUM value to a string so the comparison works and you are not relying on Oracle to guess the correct column to convert.

Code: Select all

SELECT *
          FROM test_10g_11g_3
          WHERE (       (SELECT 'ALL'
                             FROM DUAL) = 'ALL'
                        AND
                        seq_num IN ('1')
                     OR (   (SELECT 'ALL'
                                FROM DUAL) != 'ALL'
                         AND TO_CHAR(SEQ_NUM) IN (
                                SELECT COLUMN_VALUE
FROM TABLE(split_tbl('ALL')))
                        )
 13                      );

   SEQ_NUM
----------
         1

1 row selected.

test@db11g>


I am assuming this is not a real statement running in your system because there are some really silly things going on in it. For example, you have two occurrences of this:

Code: Select all

(SELECT 'ALL' FROM DUAL) = 'ALL'


This will always be true so it can be removed from the query in both cases, simplifying it to this.

Code: Select all

SELECT *
FROM test_10g_11g_3
WHERE (seq_num IN ('1')
       OR (TO_CHAR(SEQ_NUM) IN (SELECT COLUMN_VALUE
                                FROM TABLE(split_tbl('ALL')))
           )
      );


This is functionally equivalent to the statement you sent, with the TO_CHAR correction added of course.

Now, if you've just posted a bad example, while trying to simplify your problem for the sake of this forum, that's fine. If this really is the statement running in your system you need to speak to someone to ask what it is written like this for, because it doesn't make sense.

So the moral of this story is, "Never rely on implicit data type conversions!" :)

Cheers

Tim...
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog

kanthedgaurav
Senior Member
Posts: 51
Joined: Thu Apr 05, 2007 7:15 am
Location: PUNE ,INDIA

Re: Sql running on ORACLE 10G but not in Oracle 11g

Postby kanthedgaurav » Sun Jun 09, 2013 1:57 am

Thank you so much for giving detailed explanation !
Thanks,

Kanthed Gaurav

Tim...
Site Admin
Posts: 18437
Joined: Mon Nov 01, 2004 5:56 pm
Location: England, UK
Contact:

Re: Sql running on ORACLE 10G but not in Oracle 11g

Postby Tim... » Sun Jun 09, 2013 6:48 am

:)
Tim...
Oracle ACE Director
Oracle ACE of the Year 2006 - Oracle Magazine Editors Choice Awards
OakTable Member
OCP DBA 7.3, 8, 8i, 9i, 10g, 11g
OCP Advanced PL/SQL Developer
Oracle Database: SQL Certified Expert
My website: http://www.oracle-base.com
My blog: http://www.oracle-base.com/blog


Return to “Oracle SQL and PL/SQL Development”

Who is online

Users browsing this forum: No registered users and 2 guests