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.

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

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 ;-)

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 ;-)

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 :-(

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.

FM99999999999999999999999999999999999999D999999999999999999999999999999

FM99999999D999999999999999999999999999999

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...

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?