Stupid Errors
Stupid errors are always the most frustrating. Today while running a SQL statement for an adhoc report I received a “ORA-12801: error signaled in parallel query server P050” followed by “ORA-01722: invalid number”. I knew the invalid number error was the real culprit and not the parallel query error. The statement runs for 15 minutes before erroring out so I am thinking the error occurs on some row in the results. My SQL statement uses several functions, some Oracle functions and some created by myself. So my first thought was one of my functions was not dealing with the data properly. Looking at each function I could not find any issues. My next step was going to be put my SQL into a procedure so I could handle the exception and find the problem row(s). Then I spotted the problem.
Right there at the beginning of my SQL statement I wrote “select substr(summary_date, ‘YYYYMM’)”, instead of “select to_char(summary_date, ‘YYYYMM’)”. Since this is not a syntax error I did not see it right away. It also did not produce the error right away since the substr function is not applied until after the items in the where clause are evaluated.
In the end sometimes issues are so simple they are right in front of your face. For the rest of today I will be telling myself to keep it simple stupid.
