Upgrading SQL*Plus Scripts and PL/SQL after Upgrading Oracle Database

To use features and functions of the new Oracle Database release, you must change existing SQL scripts to use the syntax of the new Oracle Database release.

If existing SQL scripts do not use features and functions of the new Oracle Database release, then they run unchanged on the new Oracle Database release, and require no modification.

Be aware that because of improved error checking in the new Oracle Database release, it may identify errors at compile time rather than at run time.

Evaluation of Numeric Literals
At least one constant in a numeric computation with literals must be a decimal specified to the 10th place.

Evaluation of Numeric Literals

At least one constant in a numeric computation with literals must be a decimal specified to the 10th place.

This requirement is needed because all Oracle Database releases later than Oracle Database 10g release 1 (10.1) use INTEGER arithmetic (approximately nine significant digits) for some expressions. Oracle9i release 2 (9.2) used NUMBER arithmetic (approximately 38 significant digits). If your results contain literals greater than nine (9) significant digits, then one literal should be in decimal format to prevent numeric overflow errors. For example, in Oracle Database 10g and later releases, the computation of v1 in the following example causes a numeric overflow error:

DECLARE
  v1 NUMBER(38);
BEGIN
  v1 := 256*256*256*256;
  DBMS_OUTPUT.PUT_LINE(v1);
END;
/

The solution to the error is to specify one numeric literal as a decimal (256.0), as follows:

DECLARE
  v1 NUMBER(38);
BEGIN
  v1 := 256*256*256*256.0;
  DBMS_OUTPUT.PUT_LINE(v1);
END;
/