When you multiply integers in PL/SQL it's actually using pls_integers. These use hardware arithmetic. This is faster than number operations. They use library arithmetic.
But there's a catch. The upper limit of these are 2,147,483,647. Your calculation of 1024 * 1024 * 1024 * 5 goes waaaay over this! You'll hit the limit at 1024 * 1024 * 1024 * 2:
SQL> declare
2 l number := 1024 * 1024 * 1024 * 2;
3 begin
4 null;
5 end;
6 /
declare
*
ERROR at line 1:
ORA-01426: numeric overflow
ORA-06512: at line 2
But why does it fail?
From the docs:
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 https://docs.oracle.com/cloud/latest/db112/LNPLS/datatypes.htm#LNPLS99938 So how to avoid this?
Make one of the values in the expression a number! Then Oracle will implicitly convert the whole calculation to number arithmetic.
Here are a few ways you could do this:
Cast one of the literalsSQL> declare
2 l number := cast(1024 as number) * 1024 * 1024 * 2;
3 begin
4 null;
5 end;
6 /
PL/SQL procedure successfully completed.
Add a decimal to one of the literals, making it a numberSQL> declare
2 l number := 1024 * 1024 * 1024 * 2.0;
3 begin
4 null;
5 end;
6 /
Include a variable that's a number in the calculationSQL> declare
2 n number := 1;
3 l number := n * 1024 * 1024 * 1024 * 2;
4 begin
5 null;
6 end;
7 /
This is why your second example works. All the multiplications use number arithmetic. So don't have the 2,147,483,647 limit.
The SQL statement works because it's processed in the SQL engine. PLS_integer doesn't exist there. So it uses number arithmetic.