Today I came across a very strange problem in the code I am working on. The code raised a correct error, when executed the first time, but ran with no problems the second time. After a lot of debugging (even built a log package, hopefully so generic it will one day make it to my toolbox) I found out that the problem was a cursor that wasn’t closed properly when the error appeared.
IF NOT ggp_cur%ISOPEN THEN OPEN ggp_cur( lv_ggp_opt_seqnr ); END IF;
Of course, I know, there should always be an error handling section in every part of the code, but this is some really old and sometimes fuzzy code, that doesn’t have these sections almost anywhere.
So, the simplest solution was to make sure the cursor was closed before we checked and opened the cursor.
IF ggp_cur%ISOPEN THEN CLOSE ggp_cur; END IF; --ggp_cur%ISOPEN IF NOT ggp_cur%ISOPEN THEN OPEN ggp_cur( lv_ggp_opt_seqnr ); END IF;
This code could be made even simpler by removing the second check, but that’s something for another day. At this moment the code does what it is supposed to do and I don’t have the time or get the resources to rebuild this code, although I would like to do that.