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"