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