ORA-06502: PL/SQL: numeric or value error

[lcalvo]

Not sure if you already notice this, however I could not find it reported and also I’m unable to diagnostic / solve it for two days, so I’m reporting to you. Nothing really important, there is an easy workaround. Let me explain the issue I’m facing with the testcase in following:

$ sqlplus / as sysdba
Last Successful login time: Sat Jul 09 2016 06:56:31 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> create user check identified by check default tablespace users temporary tablespace temp;
SQL> grant connect,dba to check;
SQL> conn check/check
SQL> @mystats start

PL/SQL procedure successfully completed.

SQL> @mystats stop

==========================================================================================
MyStats report : 09-JUL-2016 06:48:46
==========================================================================================


------------------------------------------------------------------------------------------
1. Summary Timings
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
TIMER   snapshot interval (seconds)                                                   7.07
TIMER   CPU time used (seconds)                                                       0.00


------------------------------------------------------------------------------------------
2. Statistics Report
------------------------------------------------------------------------------------------

Type    Statistic Name                                                               Value
------  ----------------------------------------------------------------  ----------------
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1
ORA-06512: at line 135
ORA-06512: at line 203


SQL> @mystats start

PL/SQL procedure successfully completed.

SQL> @mystats stop
….
PL/SQL procedure successfully completed.

SQL> 

This is happening the first time we run the pair start/stop inside a new session without any argument (so I assume default s=a). Next time we run the script (start / stop again in same session) it works fine. I’ve tested in 11gR2 and 12c.

Using argument for start to avoid the issue:

sqlplus check/check
Last Successful login time: Sat Jul 09 2016 06:56:31 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> @mystats start s=a

PL/SQL procedure successfully completed.

SQL> @mystats stop
…
==========================================================================================
End of report
==========================================================================================

PL/SQL procedure successfully completed.

SQL>

The issue is happening in procedure ms_report. Line 135 points to the for loop start. With no arguments for start, values for sqlplus variables v_ms_option and v_ms_threshold are 0 and the rest are null, exactly the same as second execution (that works). I’ve not been able to diagnose / solve it, so I’m reporting to you in case want to take a look.

Thanks in advance and best regards

Luis