Monday, February 13, 2017

Intermittent ORA-06502 error when running APEX 5 on 12c

We upgraded our environment from 11.2 to 12c last week. This week we noticed an error in one of our APEX pages, a rather simple form for entering data. Nothing fancy, nothing spectacular - only a spectacular error when you tried to edit a record. 
Running the page in debug mode (even on LEVEL9) wasn't very helpful as you can see below (10 points who immediately spots the error!).
The error didn't occur for all records. It seems that a long(er) Code and Description field on the page resulted in an error more frequently. Also fiddling with a couple of the checkbox fields on the page had an effect.
So I asked Twitter for help. And within a few minutes I got all kinds of tips and advice. But especially one, from Peter Raganitsch was spot on. He suggested, looking at the debug output above, to take a look at the #CLOSE# position in the template. .
My buttons are positioned in that #CLOSE# position, so I took a closer look at those. There were three that passed the Id, Code and Description to other pages as a parameter. So ... the longer the description ... the longer the generated URL would be. And as one of the checkboxes would toggle the availability of one of the buttons, that would also have an effect on the total length of the URL's in that region position. 
So I changed a little code, to pass only the Id's to the other pages (and add a select statement there to (re)fetch the Code and Description. And since then ... it runs as a breeze.

Very weird though this was not on issue on 11.2, but is on 12c....

So what causes this issue, what is the difference?
The answer is: the checksum!

If I create a Report and a Form on DEMO_CUSTOMERS, using the CUSTOMER_ID as the Primary Key, the URL has this checksum on 11.2 : " &cs=3Fqn7QOYkP-TDSLkssvzU7i7QL1E "
On 12c however, the checksum is :" &cs=36Drf3ULxGXswIlT5btBujofcmgwdjJsr-luSfpLjSMpXP71DILliWBz31qkpRJHyPj2RS8iLce5qNkXSSeGbvQ "
And that was probably enough extra length to pass a certain varchar2(<some number here>) in the APEX code that generates the page output....


Post a Comment