ED HAYES

I have been working with Oracle APEX framework for work. I keep getting this error when saving data in an interactive grid:

APEX INTERACTIVE GRID ORA-06502: PL/SQL: numeric or value error: character to number conversion error

The application has been working for months, and then it breaks. I have no idea why. The error is happening when updating a Varchar field, so it’s certainly not table data type problem, even though, the error would lead you to believe that.

After hours, even days, of troubleshooting and only able to fix the problem by rebuilding the interactive grid, I found the root cause.

Under in the APEX development interface, for the page that is having the problem, in the Processing section, there is a process with type  Interactive Grid – Automatic Row Processing (DML) which processes row updates when data is changed and saved by the user. Under the settings there’s an options Prevent Lost Updates which is intended to prevent two users from saving the same record differently. I turned this off, and it’s working now.

There is obviously a bug in Oracle’s code:

1. I created a new record, then updated that same record, and received the error. There’s no way that record was opened by anybody else.
2. If this is the “feature” doing it’s job, and making sure the same record can’t be edited twice by different users, the error message is extremely poor, frankly, its so unrelated, it can’t possibly be the intended error when an conflicting update is detected.

I was thinking of logging an Service Request with Oracle, but two problems here, this isn’t really a supported application, as it’s “free” with the database. And also, the Oracle support page isn’t working; lovely. It’s been broken for at least two days.