24 March 2011

Buggy behavior on some 11.1 versions with INTEGER variables (2123)

The 23 March quiz explored nuanced differences between a PL/SQL INTEGER type and a SQL INTEGER type.

In particular, inserts into a table of an integer with more than 38 digits does not cause an error to be raised, as one might have thought.

Several players wrote, however, that when they tried to run the verification code, they did, in fact, get the"ORA-01438: value larger than specified precision allowed for this column" error.

After further testing, it looks like this script works (and thus demonstrates the point of the quiz properly) on database instances of versions 10.2 and 11.2. On some sub-releases of 11.1, however, such as 11.1.0.6, the script fails with the error noted above. On 11.1.0.7, this bug is apparently fixed and the script runs without error.

The author of this quiz, "_Nikotin", also found that "it's possible to reproduce other bug-like behaviour with INTEGER (on 10.2, 11.1, 11.2):


SQL> drop table t;

Table dropped.

SQL> create table t (a integer);

Table created.

SQL> insert into t values (1E125);

1 row created.

SQL> alter table t modify a number(38);

Table altered.

SQL> insert into t select a from t;

1 row created.

SQL> insert into t values (1E125);
insert into t values (1E125)
                      *
ERROR at line 1:
ORA-01438: value larger than specified precision allowed for this column


SQL> select a from t;

         A
----------
1.000E+125
1.000E+125

SQL> select dbms_metadata.get_ddl('TABLE', 'T') from dual;

DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------

  CREATE TABLE "TEST_USER"."T"
   (  "A" NUMBER(38,0)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"

1 comment:

  1. Hello All,

    I think that the problem still relies on a documentation ambiguity, versus the internal implementation.

    As per the documentation, INTEGER is supposed to be the same as NUMBER(38).

    However, as we saw in the quiz answer, integer is implemented INTERNALLY as NUMBER(*,0)
    and this IS NOT the same as NUMBER(38).

    Defining the employee_id in the quiz as
    NUMBER(38), the INSERT fails with ORA-01438 even on Oracle11.1.0.7.

    Defining the employee_id as NUMBER exhibits the same behavior as INTEGER,
    that is, the 3 values in the quiz were inserted without error, but rounded to 40 digits of precision (rounded all 3 to the same value), which is in fact the maximum allowed by the implementation.

    The following explains this is more detail:

    http://www.ixora.com.au/notes/numeric_datatypes.htm

    Thanks & Best Regards,
    Iudith

    ReplyDelete