12 Replies Latest reply: Sep 11, 2013 3:22 AM by AlbertoFaenza RSS

    Implicit conversion 9i vs 11g

    AlbertoFaenza

      Hi all,

       

      I have faced a problem in production today related to an implicit conversion that I was not able to explain. I have realized later on that it is related to the different behavior between Oracle 9i and Oracle 11g.

       

      Here is the test:

       

      Connected to:

      Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

      With the Partitioning option

      JServer Release 9.2.0.8.0 - Production

       

      SQL>

      SQL> SELECT * FROM v$version;

       

      BANNER

      ----------------------------------------------------------------

      Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production

      PL/SQL Release 9.2.0.8.0 - Production

      CORE    9.2.0.8.0       Production

      TNS for HPUX: Version 9.2.0.8.0 - Production

      NLSRTL Version 9.2.0.8.0 - Production

       

      SQL>

      SQL>

      SQL> CREATE OR REPLACE PROCEDURE test_integer (p_num IN INTEGER)

        2  IS

        3  BEGIN

        4     DBMS_OUTPUT.put_line ('Input is ' || p_num);

        5  END;

        6  /

       

      Procedure created.

       

      SQL>

      SQL> EXEC test_integer('1.12345');

      Input is 1.12345

       

      PL/SQL procedure successfully completed.

       

      SQL>

       

       

       

       

      As you can string implicitly converted to INTEGER seem not to lose the decimal part and printed as 1.12345.

       

      If I run the same in Oracle 11g:

      Connected to:

      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

      With the Partitioning, OLAP, Data Mining and Real Application Testing options

       

      SQL>

      SQL> SELECT * FROM v$version;

       

      BANNER

      --------------------------------------------------------------------------------

      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

      PL/SQL Release 11.2.0.1.0 - Production

      CORE    11.2.0.1.0      Production

      TNS for 32-bit Windows: Version 11.2.0.1.0 - Production

      NLSRTL Version 11.2.0.1.0 - Production

       

      SQL>

      SQL>

      SQL> CREATE OR REPLACE PROCEDURE test_integer (p_num IN INTEGER)

        2  IS

        3  BEGIN

        4     DBMS_OUTPUT.put_line ('Input is ' || p_num);

        5  END;

        6  /

       

      Procedure created.

       

      SQL>

      SQL> EXEC test_integer('1.12345');

      Input is 1

       

      PL/SQL procedure successfully completed.

       

      SQL>

       

      p_num is truncated as I would have expected.

       

      Is it a different behavior in Oracle 9i that has changed in newer versions or shall I consider this as a bug in that version?

       

      Regards.

      Alberto

        • 1. Re: Implicit conversion 9i vs 11g
          Hoek

          Don't know if you should consider this as a bug, since we're told many many times by a certain Tom Kyte ( you probably know him ) to never ever rely on implicit datatype conversion and you've given a nice demonstration of reason 243571308974 not to rely on them.

           

          For the record: I cannot resist a working testcase and can reproduce on 11.2.0.3:

          --------------------------------------------------------------------------------
          Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
          PL/SQL Release 11.2.0.3.0 - Production
          CORE    11.2.0.3.0      Production
          TNS for Linux: Version 11.2.0.3.0 - Production
          NLSRTL Version 11.2.0.3.0 - Production

          5 rows selected.

          SQL> CREATE OR REPLACE PROCEDURE test_integer (p_num IN INTEGER)
            2  IS
            3  BEGIN
            4     DBMS_OUTPUT.put_line ('Input is ' || p_num);
            5  END;
            6  /

          Procedure created.

          SQL> set serverout on
          SQL> EXEC test_integer('1.12345');
          Input is 1

          PL/SQL procedure successfully completed.

          • 2. Re: Implicit conversion 9i vs 11g
            Dom Brooks

            1.12345 is not an integer.

            Surely the bug is in 9i.

            • 3. Re: Implicit conversion 9i vs 11g
              SomeoneElse

              If I call it with a numeric data type instead of a character string I get the unexpected results:

               

              Connected to:

              Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production

               

              SQL> CREATE OR REPLACE PROCEDURE test_integer (p_num IN INTEGER)
                2  IS
                3  BEGIN
                4     DBMS_OUTPUT.put_line ('Input is ' || p_num);
                5  END;
                6  /

              Procedure created.

              SQL> exec test_integer(1.2345);
              Input is 1.2345

              SQL> exec test_integer('1.2345');
              Input is 1


              • 4. Re: Implicit conversion 9i vs 11g
                SomeoneElse

                Keep in mind Oracle doesn't really have an Integer type (which is an ANSI supported type).

                 

                Instead, it uses number(38):

                 

                SQL> create table t (n integer);

                Table created.

                SQL> desc t
                Name          Null?    Type
                ------------- -------- ------------
                N                      NUMBER(38)

                 

                • 5. Re: Implicit conversion 9i vs 11g
                  Dom Brooks

                  Check the subtype definition of INTEGER in SYS.STANDARD in 9i.
                  I don't have such a version around.

                   

                  In 11.2 it is:

                  subtype INTEGER is NUMBER(38,0);

                   

                  I've got a distant nagging memory about subtype constraints but I just can't remember...

                  • 6. Re: Implicit conversion 9i vs 11g
                    chris227

                    String and function call in sql (11g)

                     

                    CREATE OR REPLACE function test_integer2 (p_num INTEGER)
                    return integer
                    IS
                    BEGIN
                    return p_num;
                    END;

                     

                    select
                    test_integer2('1.12345') r
                    from dual

                     

                    R
                    1.12345

                     

                    select * from v$version

                    BANNER
                    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
                    PL/SQL Release 11.2.0.3.0 - Production

                    • 7. Re: Implicit conversion 9i vs 11g
                      SomeoneElse

                      If you use PLS_INTEGER, it should work fine:

                       

                      SQL> CREATE OR REPLACE PROCEDURE test_integer (p_num IN pls_integer)
                        2  IS
                        3  BEGIN
                        4     DBMS_OUTPUT.put_line ('Input is ' || p_num);
                        5  END;
                        6  /

                      Procedure created.


                      SQL> exec test_integer('1.2345');
                      Input is 1


                      SQL> exec test_integer(1.2345);

                      Input is 1

                      • 8. Re: Implicit conversion 9i vs 11g
                        AlbertoFaenza

                        DomBrooks wrote:

                         

                        Check the subtype definition of INTEGER in SYS.STANDARD in 9i.
                        I don't have such a version around.

                         

                        In 11.2 it is:

                        subtype INTEGER is NUMBER(38,0);

                         

                        I've got a distant nagging memory about subtype constraints but I just can't remember...

                        Hi Dom,

                         

                        I confirm that in both version definition is the same:

                          subtype INTEGER is NUMBER(38,0);

                        So with scale 0 still the number (in some cases) is keeping digits to the right of decimal point. Interesting...

                         

                        Regards.

                        Alberto

                        • 9. Re: Implicit conversion 9i vs 11g
                          AlbertoFaenza

                          Hoek wrote:

                           

                          Don't know if you should consider this as a bug, since we're told many many times by a certain Tom Kyte ( you probably know him ) to never ever rely on implicit datatype conversion and you've given a nice demonstration of reason 243571308974 not to rely on them.

                           

                          Hi Hoek,

                           

                          thank you for your comment. You are right about implicit conversion but it was a surprise to me.

                          There is a procedure which is called by an external application and I hope you can understand my surprise when I have seen an integer accepting (without truncating) numbers with digits after the decimal point.

                           

                          Definitely the solution is to avoid that this could happen (the application is relying on user input so they can make a check before submitting the input to the procedure) but still it is something which is not clear to me.

                          Also I have notice that even in 11g we have different behavior. Passing the input as a float number instead of a string (as SomeoneElse example) produces the wrong behavior also on Oracle 11g.

                          Also Chris has shown another unexpected behavior using a function.

                           

                          Regards.

                          Alberto

                          • 10. Re: Implicit conversion 9i vs 11g
                            AlbertoFaenza

                            SomeoneElse wrote:

                             

                            Keep in mind Oracle doesn't really have an Integer type (which is an ANSI supported type).

                             

                            Instead, it uses number(38):

                             

                            SQL> create table t (n integer);

                            Table created.

                            SQL> desc t
                            Name          Null?    Type
                            ------------- -------- ------------
                            N                      NUMBER(38)

                             

                            Hi SomeoneElse,

                             

                            in this case INTEGER is defined in SYS.STANDARD as NUMBER(38,0). Being the scale=0 I would expect that no digits after the decimal points are kept.

                            Even NUMBER(38) is equal to NUMBER(38,0) and according to Oracle documentation "A scale of 0 rounds to the nearest whole number".

                             

                            It seems we have some different behavior in some cases though.

                             

                            Regards.

                            Alberto.

                            • 11. Re: Implicit conversion 9i vs 11g
                              Hoek

                              Hi Alberto,

                               

                              I had to leave my beloved desktop right after my reply yesterday, and after reading up a bit just now I see this thread has taken an interesting turn indeed.

                               

                              Regards,

                              Hoek

                              • 12. Re: Implicit conversion 9i vs 11g
                                Lalit Kumar B

                                SomeoneElse wrote:

                                 

                                Keep in mind Oracle doesn't really have an Integer type (which is an ANSI supported type).

                                 

                                Instead, it uses number(38):

                                 

                                SQL> create table t (n integer);

                                Table created.

                                SQL> desc t
                                Name          Null?    Type
                                ------------- -------- ------------
                                N                      NUMBER(38)

                                 

                                 

                                I completely agree on this. However, this will only be shown in SQL*Plus. If we check the same in any tool like PL/SQL Developer, it will show TYPE as INTEGER itself.I know we must stick to SQL*Plus, but I just wanted to share this.

                                 

                                 

                                SQL> create table t(col integer);

                                 

                                 

                                 

                                Table created

                                 

                                 

                                 

                                SQL> desc t;

                                 

                                Name Type        Nullable Default Comments

                                 

                                ----      -------        --------    -------    --------

                                 

                                COL   INTEGER Y

                                 

                                 

                                 

                                SQL>