Have you ever written a hairy piece of PL/SQL code and then from somewhere deep in the bowels of your many routines, you find that under some situations you are getting an ORA-6502: Value Error, but you can't figure out why? This error occurs, among other reasons, when you try to assign a value to a variable that it's too small to hold. For example, consider a very simple PLSQL procedure like:
procedure example( v varchar2) is lv_temp VARCHAR2(7); begin lv_temp := v||'X'; dbms_output.put_line(lv_temp); end;
If you happen to invoke this routine, passing a value that is already seven characters long or more, it's going to raise a VALUE_ERROR exception when the first line of the procedure body tries to concatenate an 'X', making it 8 long.
The JDeveloper debugger works for debugging PL/SQL code, but a little-known feature is that we also support exception breakpoints for PL/SQL. So, if you happen to know that you want to stop whenver an ORA-6502 error occurs, just create a (specially-named!) exception breakpoint on the exception "class" named $Oracle.EXCEPTION_ORA_6502
You can substitute the 6502 part in the name above with another ORA error code, too. When you debug your routine, it will stop the debugger in its tracks whever this VALUE_ERROR (ORA-6502) occurs, and you can figure out from the stack and the data window who is the culprit.
6:45:25 PM
|