Skip to main content

ORA-00936: missing expression error while updating data using Tabular form

Are you getting "ORA-00936: missing expression" error while trying to update existing data using tabular form?


Are you using "Multi Row Update" or "Tabular Form" processes (ApplyMRU or Apply MRD), then most probably the issue is with a space or new line character you have entered in "Runtime Where Clause" section of "ApplyMRU" process


However to know the exact reason behind the error, run page in debug mode and try saving data. Then see debug output. If the issue is with space or new line character which you have entered, then you will see entry similar to below


as you can see above (highlighted with yellow color), APEX finds there is some value entered for  "Runtime Where Clause" and it will append this where clause to update query. Since it's not a valid SQL Update statement, we will get "ORA-00936: missing expression" error.

Fix: It's obvious. Go to page designer and remove any spaces or new line characters entered in "Runtime Where Clause" section and save changes. This should fix the issue.

This issue or bug was also there in APEX 4.2 version, however, in ver. 4.2 when you try to edit "ApplyMRU" process (as developer using application builder), then APEX detects and ignores any spaces and new line characters in "Runtime Where Clause" section & TRIMs them our during page load itself. So in this case, you can just edit "ApplyMRU" process and you can click on "Apply Changes" button. This will fix the issue.



Labels:

Comments

Post a Comment

Popular posts from this blog

Interactive Grid - Aggregate Validations

In Oracle APEX, validating Interactive Grid (IG) data at row level, is easy and straight forward. You just need to select "Editable Region" for the validation and then you can use IG column values using :COLUMN_NAME syntax in SQL and PL/SQL code. However, if you want to create a validation at table level or validation which uses multiple rows data, then it becomes tricky. Let's consider a simple example of budget planning, where you can allocate percentage for each category, like 10% for healthcare, 10% for education etc. Here, we need to implement a simple table level validation to ensure total allocation % does not exceed 100. Please refer  Interactive Grid - Client Side Aggregate Validations  for implementing this validation entirely on client side. You can use technique explained below for more complex validations that can't be done on client side (checking data from multiple tables, number of rows in IG are more than 50) Before jumping into the implemen...
7 comments
Read more

Interactive Grid - Bulk Operation on Selected Rows

What's the Problem? Let's say you have an IG on employee table and you want to update selected employees commission based on user's input. Ideally, it should be very simple, where you can write UPDATE statement in page process and select IG region as "Editable Region" under "Execution Options" of the page process. But, when you select rows and submit page, you can see that, this process won't get executed! The reason is  Selection of 'Row Selector' check-boxes is not considered as row-change. Thus selected rows are not submitted to server. So, is there any work around?  Yes! Luckily there are lot of JavaScript (JS) APIs available to work with IG. If you are not already aware, you can refer "APEX IG Cookbook"  or  JavaScript API Reference documentation. If we continue with above Employee IG example, when user selects IG rows, enters "Commission %" and clicks on "Update Commission" button, then we can writ...
36 comments
Read more