Synonyms for number

[petahathub]

Hi,

I have oracle 12.2.

I have a little trouble with integer datatype. It is not correctly processed in add_new_cols procedure. The corresponding column in audit table is then added as number(), without presicion and scale.

Then, if i call generate_audit_support procedure for the second time, in add_new_cols procedure there is a mismatch in section existing columns (try to issue a MODIFY, eg increase size etc), number datatype.

Condition in audit_util package, line 469 (from begin of package body, last version of your library)

if nvl(cols.data_precision,-1) = nvl(cols.aud_data_precision,-1) and
              nvl(cols.data_scale,-1) = nvl(cols.aud_data_scale,-1)

is not met and exception is thrown.

Because integer has precision null and scale 0, but number() has precision null and scale null, as it is seen in select

select c.column_name,
                       c.data_type,
                       c.data_precision,
                       c.data_scale,
                       c.data_type_owner,
                       c.data_length,
                       c.column_id,
                       a.data_type  aud_data_type,
                       a.data_precision aud_data_precision,
                       a.data_scale aud_data_scale,
                       a.data_type_owner aud_data_type_owner,
                       a.data_length aud_data_length
                from   ( select *
                         from   all_tab_columns
                         where  owner = p_owner
                         and    table_name = p_table_name ) c,
                       ( select *
                         from all_tab_columns
                         where owner = g_aud_schema
                         and   table_name = l_audit_table_name ) a
                where  c.column_name = a.column_name(+)
                order by c.column_id

There are further synonyms to number, like smallint :)

Cheers

[connormcd]

Thanks for bringing this to my intention. I've updated the package body to handle this.