Search notes:

Oracle columns: DATA_LENGTH, DATA_PRECISION and DATA_SCALE

create table tq84_data_scale_length_prec      (          -- DATA_LENGTH  DATA_PRECISION  DATA_SCALE
    ------------------------------------------           -- -----------  --------------  ---------
    num             number         ,                     --          22            null        null
    num__5          number  ( 5   ),                     --          22               5           0
    num__5__2       number  ( 5, 2),                     --          22               5           2
    num_38_12       number  (38,12),                     --          22              38          12
    num_star        number  ( *   ),                     --          22            null        null
    vc_10           varchar2(10   ),                     --          10            null        null
    dt              date           ,                     --           7            null        null
    ts              timestamp      ,                     --          11            null           6
    ts_tz           timestamp with time zone,            --          13            null           6
    ts_tz_loc       timestamp with local time zone       --          11            null
);

Determine DATA_LENGTH, DATA_PRECISION and DATA_SCALE

select
   column_name,
   data_type,
   data_length,
   data_precision,
   data_scale
from
   user_tab_columns
where
   table_name = 'TQ84_DATA_SCALE_LENGTH_PREC'
order by
   column_id;

Inserting values that are too large for the column definition

insert into tq84_data_scale_length_precision(num__5__2) values( 123.45  );
insert into tq84_data_scale_length_precision(num__5__2) values( 123.456 ); -- No error thrown, but value inserted is 123.46
insert into tq84_data_scale_length_precision(num__5__2) values(1234.56  ); -- ORA-01438: value larger than specified precision allowed for this column

insert into tq84_data_scale_length_precision(vc_10) values('0123456789' );
insert into tq84_data_scale_length_precision(vc_10) values('0123456789*'); -- ORA-12899: value too large for column "..."."TQ84_DATA_SCALE_LENGTH_PRECISION"."VC_10" (actual: 11, maximum: 10)

Clean up demonstration table

drop table tq84_data_scale_length_precision;

Index