Bug: "ORA-06502: numeric or value error" when executing SQL in debug window with too many columns

[Claus Pedersen]

I am running a test session on 10g Express Edition release 10.2.0.1.0

When executing SQL in the debug session, I sometimes get the error: ORA-06502: PL/SQL: numeric or value error: character string buffer too small.

An example:

SELECT dummy AS my_very_long_named_column_0001,
       dummy AS my_very_long_named_column_0002,
       dummy AS my_very_long_named_column_0003,
       dummy AS my_very_long_named_column_0004,
       dummy AS my_very_long_named_column_0005,
       dummy AS my_very_long_named_column_0006,
       dummy AS my_very_long_named_column_0007,
       dummy AS my_very_long_named_column_0008
FROM   dual

If I select the first 7 columns only, it works.
Also, if I name the columns a,b,c, ... I am able to select 15 columns, so the error is dependant of a combination of the number of columns and the length of the column names.

If I run the same select on e.g. versions 11 or 12 (11g Enterprise Edition Release 11.2.0.4.0 / 12c Enterprise Edition Release 12.1.0.1.0), it works better, but always with a limitation of last column up to ...my_very_long_named_column_0035. If I try with last column ...0036, I get error "ORA-06558: buffer in dbms_pipe package is full. No more items allowed"
If I correct the error, I now get the error "ORA-06510: PL/SQL: unhandled user-defined exception" and my entire debug session has now stalled/crashed and can only be terminated by killing the entire PLD session from the task manager.

I am using PLD version 10.0.5.1710

 

[Marco Kalter]

There are indeed some size limitations in the Oracle Debug API. It's difficult to predict.

 

[Claus Pedersen]

OK, but please catch the Oracle errors and show a more meaningful error message to the user, e.g. "Due to limitations in the Oracle debugger, the entered SQL could not be performed. Please lower the number of requested columns and/or handled records". This way the user would get a hint on how to solve the problem.
ORA-06502 or ORA-06558 does not provide much help to the user.

 

[Marco Kalter]

Okay, we'll look into that.

 

[Claus Pedersen]

Thanks, not a biggie, but rather a "nice-to-have"