Skip to Main Content
  • Questions
  • How to validate the precision and scale of a number in pure PL/SQL

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Przemek.

Asked: October 08, 2020 - 12:01 am UTC

Answered by: Chris Saxon - Last updated: October 13, 2020 - 4:35 am UTC

Category: PL/SQL - Version: 19.8 EE

Viewed 1000+ times

You Asked

Hello,
I came across a problem to validate if a user-provided number will fit the column in a table. The check to be done in PL/SQL is to validate if precision and scale will be big enough for the provided number.
What would be your suggestion to achieve that?
My efforts below.


I cannot find it in the documentation but I found that PL/SQL does not allow datatype limits in neither VALIDATE_CONVERSION nor CAST functions, so the following fails:
DECLARE 
  tn NUMBER;
BEGIN
  tn := CAST( '123.456' AS NUMBER( 6, 2 ));
  DBMS_OUTPUT.PUT_Line( tn);
END;

SQL does allow datatype limits in there, so the solution could be something like:
execute IMMEDIATE 'SELECT CAST(:p_test_num AS NUMBER(:p_precision,:p_scale)) from dual' INTO ...

However, the check is to be done in bulk (loading a text file), so context switches will become a problem.
I could use PL/SQL like:
set serveroutput ON SIZE UNLIMITED

CREATE OR REPLACE PROCEDURE P(p_test_num IN VARCHAR2, p_precision IN PLS_INTEGER, p_scale IN PLS_INTEGER, p_format IN VARCHAR2 DEFAULT NULL)
IS
   l_result PLS_INTEGER;  
   -- -1 - the number will fit but with rounding
   --  0 - the number will not fit
   --  1 - the number will fit perfectly 
BEGIN 
execute IMMEDIATE 'declare
  l_test_num NUMBER('||p_precision||', '||p_scale||');
begin
  if :test_format is null then
     l_test_num := to_number(:test_num);
  else
     l_test_num := to_number(:test_num, :test_format);
  end if;
  if l_test_num = :test_num 
  then :result := 1;
  else :result := -1;
  end if;
exception
   when value_error then :result := 0;
   when others then raise; 
end;' USING IN p_format, IN p_test_num, OUT l_result;
   
   DBMS_OUTPUT.PUT_Line( l_result);
END;


exec p('21474.83647',38,1)
exec p('2147483647' ,38,1)
exec p('2147483647' ,38,127)
exec p('21474.83647',38,1,'99999D99')
exec p('21474.83647' ,38,1,'99999D99999')
exec p('21474.83647' ,38,1,'99999999D99999999')
exec p('21474.83647' ,38,12,'99999999D99999999')
exec p('2147483647' ,38,127,'99999999999')

... but I have a feeling this could be done better...

and we said...

You can declare subtypes of the required scale and precision and cast to these.

This will raise an exception if you exceed the precision, but not the scale because Oracle Database rounds these. So you have to check the input equals the result.

declare 
  tn varchar2(10) := '9.91';
  subtype n is number(2,1);
begin
  case
    when tn = cast ( tn as n ) then 
      null;
    else
      raise_application_error ( -20001, 'rounding' );
  end case;
end;
/

ORA-20001: rounding


You can also decalre subtypes as column%types, binding them to the underlying column's data type:

create table t (
  c1 number ( 2, 1 )
);

declare 
  tn varchar2(10) := '9.91';
  subtype n is t.c1%type;
begin
  case
    when tn = cast ( tn as n ) then 
      null;
    else
      raise_application_error ( -20001, 'rounding' );
  end case;
end;
/

ORA-20001: rounding


You can't dynamically pass the subtype though. So to do this in one, you'd need to pass the column you're checking and cast to the appropriate subtype:

drop table t 
  cascade constraints purge;
create table t (
  c1 number ( 2, 1 ), c2 number ( 3, 2 )
);

create or replace procedure check_num_limits ( 
  val varchar2, col varchar2
) as
  subtype t1 is t.c1%type;
  subtype t2 is t.c2%type;
begin
  case
    when col = 'C1' and val = cast ( val as t1 ) then 
      null;
    when col = 'C2' and val = cast ( val as t2 ) then 
      null;
    else
      raise_application_error ( -20001, 'rounding' );
  end case;
end check_num_limits;
/

exec check_num_limits ( '9.91', 'C1' );

ORA-20001: rounding

exec check_num_limits ( '9.91', 'C2' );
exec check_num_limits ( '9.991', 'C2' );

ORA-20001: rounding

and you rated our response

  (7 ratings)

Reviews

October 08, 2020 - 10:59 am UTC

Reviewer: Przemek

Thanks Chris,
Indeed I could cast to the destination table column. I wish I had one table in one schema...
Anyway, it seems that I will not get rid of EXECUTE IMMEDIATE there.

Or I can write some complex set of IFs checking the number of digits to the right and to the left of the decimal point of the number being validated. I wonder if this is at all possible.
Chris Saxon

Followup  

October 08, 2020 - 12:38 pm UTC

Is there a particular reason you need to check the values before you insert them?

You could just run the insert, returning the inserted values. You'll get an exception if this exceeds the precision; to check for rounding all you need to do is verify the input and returned values are equal:

declare
  ln number := 9.91;
  rn number;
begin
  insert into t ( c1 )
  values ( ln )
  returning c1 into rn;
  
  if ln <> rn then
    raise_application_error ( -20001, 'rounding' );
  end if;
end;
/

ORA-20001: rounding

Compare desired precision/scale and TM9

October 09, 2020 - 9:51 am UTC

Reviewer: Kim Berg Hansen from Middelfart, Denmark

You could turn your input into a number (with maximum precision/scale), and then TO_CHAR that number twice - once with desired precision/scale as 9999D999, once with TM9 - and compare the two. If precision is broken, the first TO_CHAR returns ####; if scale is broken, the first TO_CHAR rounds - in either case the output will be different than the TM9 output.

So an example table:

create table emulate_external_table (
   col     varchar2(100)
);

insert into emulate_external_table (col) values ('123.456');
insert into emulate_external_table (col) values ('123.45');
insert into emulate_external_table (col) values ('9123.456');
insert into emulate_external_table (col) values ('123.4');
insert into emulate_external_table (col) values ('9123');
insert into emulate_external_table (col) values ('123');
commit;


In PL/SQL it'd be like this:

set serveroutput on size unlimited;

declare
   l_will_fit  boolean;
   function will_fit (
      p_string    varchar2
    , p_precision pls_integer
    , p_scale     pls_integer
   )
      return boolean
   is
   begin
      return validate_conversion(p_string as number) = 1
         and rtrim(to_char(to_number(p_string), 'FM' || rpad('9', p_precision, '9') || 'D' || rpad('9', p_scale, '9')), '.')
              = to_char(to_number(p_string), 'TM9');
   end;
begin
   for eet in (
      select col
      from emulate_external_table
   ) loop
      dbms_output.put(eet.col);
      l_will_fit := will_fit(eet.col, 3, 2);
      dbms_output.put('  ' || case when l_will_fit then 'fits' else 'do not fit' end || ' in number(3,2)');
      l_will_fit := will_fit(eet.col, 4, 1);
      dbms_output.put('  ' || case when l_will_fit then 'fits' else 'do not fit' end || ' in number(4,1)');
      dbms_output.new_line;
   end loop;
end;
/

123.456  do not fit in number(3,2)  do not fit in number(4,1)
123.45  fits in number(3,2)  do not fit in number(4,1)
9123.456  do not fit in number(3,2)  do not fit in number(4,1)
123.4  fits in number(3,2)  fits in number(4,1)
9123  do not fit in number(3,2)  fits in number(4,1)
123  fits in number(3,2)  fits in number(4,1)


RTRIM of '.' gets rid of trailing decimal point from 999D99 when the value is integer.

(This example assumes using decimal point, not decimal comma.)

Instead of pure PL/SQL, you could then also do pure SQL:

select
   eet.col
 , rtrim(to_char(to_number(eet.col), 'FM999D99'), '.') test_expr1
 , to_char(to_number(eet.col), 'TM9') test_expr2
 , case
      when validate_conversion(eet.col as number) = 1
       and rtrim(to_char(to_number(eet.col), 'FM999D99'), '.') = to_char(to_number(eet.col), 'TM9')
      then 'Y'
      else 'N'
   end as will_fit_p3_s2
from emulate_external_table eet;

COL        TEST_EXPR1 TEST_EXPR2 WILL_FIT_P3_S2
---------- ---------- ---------- --------------
123.456    123.46     123.456    N             
123.45     123.45     123.45     Y             
9123.456   #######    9123.456   N             
123.4      123.4      123.4      Y             
9123       #######    9123       N             
123        123        123        Y             


That way you can insert only those who fits:

insert into real_table
select
   to_number(eet.col)
from emulate_external_table eet
where
   case
      when validate_conversion(eet.col as number) = 1
       and rtrim(to_char(to_number(eet.col), 'FM999D99'), '.') = to_char(to_number(eet.col), 'TM9')
      then 'Y'
      else 'N'
   end = 'Y'
;


But then again, if you load it like that, you could also use DML Error Logging instead, simply insert all rows from the external table using LOG ERRORS, and then all those that didn't fit the precision/scale would go to the error log table. That way you wouldn't have to code a check at all ;-)

Chris Saxon

Followup  

October 09, 2020 - 12:49 pm UTC

Nice stuff Kim :)

simply insert all rows from the external table using LOG ERRORS, and then all those that didn't fit the precision/scale would go to the error log table

The database silently rounds values exceeding the scale, so there's no error right? Don't you need to add a constraint to ensure this isn't happening?

Yes, LOG ERROR wouldn't catch scale errors

October 12, 2020 - 8:55 am UTC

Reviewer: Kim Berg Hansen from Middelfart, Denmark

You're right, Chris.

LOG ERROR wouldn't catch scale errors, it'd just round. I forgot that. In that case last query of my comment above would be better ;-)

Chris Saxon

Followup  

October 12, 2020 - 10:24 am UTC

Well you could declare the column with a larger scale than you want, then have a check constraint to ensure the inserted value matches the rounding you want.

But that's getting a bit of a faff ;)

ORA-01481: invalid number format model

October 12, 2020 - 11:27 am UTC

Reviewer: Przemek from Poland

This trick with the conversion is awesome, however, covers only a fraction of the problem. Tried some edge cases and if p_precision + p_scale > 62 then it fails with ORA-01481...
Is there anything about it in the documentation? I have not found anything about it in the TO_CHAR doc...

I must stay with the EXECUTE IMMEDIATE :-(
Chris Saxon

Followup  

October 12, 2020 - 12:31 pm UTC

Can you give an example of these edge cases?

What's the reason you can't just insert the values and use the returning clause to check for rounding?

ORA-01481 continued

October 12, 2020 - 12:50 pm UTC

Reviewer: Przemek from Poland

Just gave you the rule. But the example could be:
l_will_fit := will_fit('1', 38, 30);

In my case, the format of the number is only one from dozens of rules that need to be checked before the data moves from the staging area to the "production" table. Other rules are: regular expressions on the column value, dependencies between columns, dependencies on the rows (e.g. sum in groups of rows must be equal), etc. etc.
Chris Saxon

Followup  

October 12, 2020 - 2:28 pm UTC

It looks like the conversion logic needs tweaking.

Passing 38, 30, it produces a format model which has 38 digits before the decimal point and 30 after, e.g.:

FM99999999999999999999999999999999999999D999999999999999999999999999999


This exceeds the maximum precision by a long way.

A number(38,30) has 8 digits before the decimal point and thirty after. You can't store a value with 60+ significant decimal digits in Oracle Database!

In this case, the format mask should be:

FM99999999D999999999999999999999999999999


The padding needs to subtract the scale from the precision. And adjust the position of the decimal ponit to handle scales between -84 and 127.

dozens of rules that need to be checked before the data moves

But why do you need to check them before moving the data? What's the exact problem with just inserting the data and catching the exceptions there?

EXECUTE IMMEDIATE sucks...

October 12, 2020 - 5:47 pm UTC

Reviewer: Przemek from Poland

That was my point.
Anyway, I spent some more time on it (as I should at the first place) and created:
CREATE OR REPLACE FUNCTION will_fit ( p_string    VARCHAR2, p_precision PLS_INTEGER, p_scale     PLS_INTEGER)
RETURN PLS_INTEGER
IS
   l_format    VARCHAR2(130);
   l_converted VARCHAR2(130);
BEGIN
   IF validate_conversion(p_string AS NUMBER) = 0 THEN
      RETURN 0; -- a bad number
   END IF;

   IF p_PRECISION NOT BETWEEN   1 AND  38
   OR p_SCALE     NOT BETWEEN -84 AND 127 
   THEN
      RAISE VALUE_ERROR;
   END IF;
   IF p_precision > p_scale 
   THEN l_format := 'FM' || RPAD('9', p_precision-p_scale, '9') || 'D' || RPAD('9', p_scale, '9');
   ELSE l_format := 'FM0D'||RPAD('0', p_scale-p_precision, '0') || RPAD('9', p_precision, '9');
   END IF;
   
   l_converted := RTRIM(TO_CHAR(TO_NUMBER(p_string), l_format), '.0'); 
   IF l_converted LIKE '#%' 
   THEN RETURN 0; -- an overflow
   END IF;
     
   IF l_converted = TO_CHAR(TO_NUMBER(p_string), 'TM9')
   THEN RETURN 1; -- all perfectly fine
   ELSE RETURN -1; -- converted with rounding
   END IF;
END will_fit;
/


What do you think, makes sense?
I'd still need to play with NLS_ for the decimal point. I wonder if it will work with scientific notation or currency...


Then I run a performance comparison:
CREATE OR REPLACE FUNCTION will_fit2(p_test_num IN VARCHAR2, p_precision IN PLS_INTEGER, p_scale IN PLS_INTEGER, p_format IN VARCHAR2 DEFAULT NULL) 
      RETURN PLS_INTEGER
   IS
   -- PL/SQL does not allow dataypes limits in the CAST function, but SQL does, so the alternative would be (but what about context switches?):
   --    execute IMMEDIATE 'SELECT CAST( '''|| p_test_num ||''' AS NUMBER ( '|| p_precision ||' , '|| p_scale ||' ) ) from dual' INTO l_test_num 
     l_result PLS_INTEGER;
      -- -1 - the number will fit but with rounding
      --  0 - the number will nto fit
      --  1 - the number will fit perfectly 
   BEGIN 
      execute IMMEDIATE 'declare
        l_test_num NUMBER('||p_precision||', '||p_scale||');
      begin
        if :test_format is null then
           l_test_num := to_number(:test_num);
        else
           l_test_num := to_number(:test_num, :test_format);
        end if;
        if l_test_num = :test_num 
        then :result := 1;
        else :result := -1;
        end if;
      exception
         when value_error then :result := 0;
         when others then raise; 
      end;' USING IN p_format, IN p_test_num, OUT l_result;
      RETURN l_result;
   END will_fit2;  
/


set timing ON
set serveroutput ON SIZE UNLIMITED
DECLARE 
     l_result PLS_INTEGER := 0;
BEGIN
  FOR i IN 1..100000 LOOP
     l_result := l_result + will_fit ( TO_CHAR(DBMS_RANDOM.VALUE(-999999,999999), 'TM9'), 5, 2);
  END LOOP;
  dbms_output.put_line(l_result);
END;
DECLARE 
     l_result PLS_INTEGER := 0;
BEGIN
  FOR i IN 1..100000 LOOP
     l_result := l_result + will_fit2( TO_CHAR(DBMS_RANDOM.VALUE(-999999,999999), 'TM9'), 5, 2);
  END LOOP;
  dbms_output.put_line(l_result);
END;


And turned out that EXECUTE IMMEDIATE is 10 times slower.... I'm surprised.



Still about validations in PL/SQL - I have many checks, some cross column, some cross rows that are related to the source data rather than the destination table. If I only had one validation (the problem with precision/scale) then "try & error" by inserting to the destination table could work. Let go...

Thanks...

October 12, 2020 - 8:56 pm UTC

Reviewer: Przemek from Poland

Thank you Kim and Chris.
I think I managed to reach the final stage.
Just to bore you but for the sake of the record I'am pasting the procedure invented by Kim with a set of IFs I referenced in the beginning of this thread:

CREATE OR REPLACE FUNCTION will_fit ( p_string VARCHAR2, p_precision PLS_INTEGER, p_scale PLS_INTEGER, p_format IN VARCHAR2 DEFAULT NULL)
RETURN PLS_INTEGER
      -- -1 - the number will fit but with rounding
      --  0 - the number will nto fit
      --  1 - the number will fit perfectly 
IS
   l_format    VARCHAR2(130);
   l_converted VARCHAR2(130);
BEGIN

   IF p_format IS NOT NULL THEN 
      IF validate_conversion(p_string AS NUMBER, p_format ) = 0 THEN
         RETURN 0;
      END IF;   
   ELSE
      IF validate_conversion(p_string AS NUMBER ) = 0 THEN
         RETURN 0;
      END IF;   
   END IF;

   IF p_PRECISION NOT BETWEEN   1 AND  38
   OR p_SCALE     NOT BETWEEN -84 AND 127 
   THEN
      RAISE VALUE_ERROR;
   END IF;
   
   CASE WHEN p_scale <= 0 
        THEN IF   p_precision - p_scale < 62 
             THEN l_format := 'FM' || RPAD('9', p_precision, '9') || RPAD('0', -p_scale, '0');
             ELSE l_format := 'FM' || RPAD('9', p_precision, '9') || 'EEEE';
             END IF;
        WHEN p_scale BETWEEN 1 AND p_precision
        THEN l_format := 'FM' || RPAD('9', p_precision-p_scale, '9') || 'D' || RPAD('9', p_scale, '9');
        ELSE -- WHEN p_scale > p_precision
             IF   p_precision + p_scale < 62
             THEN l_format := 'FM0D' || RPAD('0', p_scale-p_precision, '0') || RPAD('9', p_precision, '9');
             ELSE l_format := 'FM0D' || RPAD('9', 56, '9') || 'EEEE';
             END IF;
   END CASE;
   
   IF p_format IS NOT NULL THEN 
      l_converted := TO_CHAR(TO_NUMBER(p_string, p_format), l_format); 
   ELSE
      l_converted := TO_CHAR(TO_NUMBER(p_string), l_format); 
   END IF;

   IF l_converted LIKE '#%' THEN          
      RETURN 0;
   END IF;

   IF p_format IS NOT NULL THEN 
      IF l_converted = TO_CHAR(TO_NUMBER(p_string, p_format), 'TM9')
      THEN RETURN 1;
      ELSE RETURN -1;
      END IF;
   ELSE           
      IF l_converted = TO_CHAR(TO_NUMBER(p_string), 'TM9')
      THEN RETURN 1;
      ELSE RETURN -1;
      END IF;
   END IF;
END will_fit;
/


I will greatly appreciate if you find any other flaw in this.
The "62" in the code above is what I experimentally found (TO_CHAR's format length limitation) as I could not read about it in the documentation - Chris maybe you can confirm?

Connor McDonald

Followup  

October 13, 2020 - 4:35 am UTC

Thanks for posting this - others will find it useful

That limit has existed for as long as I can remember...I'll check internally to see if there's a reason

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.