I define a procedure do parse a formula (type value String) to a value number: Some case value in formula can execute or raise error numeric overflow. Code :
DECLARE
formula VARCHAR2(1000) := '1111111111 * 2';
val NUMBER;
BEGIN
EXECUTE IMMEDIATE 'BEGIN :res := '||formula||'; END;' USING OUT val;
DBMS_OUTPUT.PUT_LINE ( 'formula = ' || formula );
DBMS_OUTPUT.PUT_LINE ( 'val = ' || val );
END;
In this case then raise error numeric overflow. When i increase value operands more/less than 10 character 1 then it working right.
I try to check a few values and see most of the error if the value in the formula contains the number 1. Is this the reason?
Thank guys!
When you multiply integers in PL/SQL it's actually using pls_integers.
There is an upper limit for pls_integer 2,147,483,647 and multiplication used in the example of yours 2,222,222,222 exceeds this and thus result in numeric overflow.
From the offcial docs and also from ask Tom site:
A calculation with two PLS_INTEGER values that overflows the PLS_INTEGER range raises an overflow exception, even if you assign the result to a NUMBER data type.
To get rid of this we can make one of the values in the expression a number.
Case1: with CAST
DECLARE
formula VARCHAR2(1000) := 'cast(1111111111 as number) * 2';
val NUMBER;
BEGIN
EXECUTE IMMEDIATE
'BEGIN '||
' :res := '||formula||';'||
'END;' USING OUT val;
DBMS_OUTPUT.PUT_LINE ( 'formula = ' || formula );
DBMS_OUTPUT.PUT_LINE ( 'val = ' || val );
END;
/
Case2: by making one of the expression to decimal and Oracle will implicitly convert it to number
DECLARE
formula VARCHAR2(1000) := '1111111111 * 2.0';
val NUMBER;
BEGIN
EXECUTE IMMEDIATE
'BEGIN '||
' :res := '||formula||';'||
'END;' USING OUT val;
DBMS_OUTPUT.PUT_LINE ( 'formula = ' || formula );
DBMS_OUTPUT.PUT_LINE ( 'val = ' || val );
END;
/