ORA-20001: Error in multi row delete operation: row = nnn, ORA-1403, no data found. Error in multi row operation failed

Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.

Ever encountered the following error when attempting to delete one or more rows in a tabular form report?

ORA-20001: Error in multi row delete operation: row = nnn, ORA-1403, no data found. 

Error in multi row operation failed

If yes, it means you've been too adventurous and probably tampered with some specific columns that you ought not touch. As soon as you hit the wall, the instinct is to search the OTN Apex forum for a solution, but I found just a few threads without a precise explanation, so after fixing the problem myself, I thought it would be useful to write up a short article to show exactly where was the catch.

The not-so-quick-but-dirty solution when you hit this problem is to delete the tabular form and re-create it from scratch. Clearly this is suboptimal if you have spent some time adjusting the various column definitions, but if you can't get out of the mess, it will fix it at all times, it works like a sort of CTL-ALT-DEL in the apex tabular form era :-D
Indeed the Application Builder User's Guide warns the developer about changing the SQL query after running the Tabular Form creation wizard, but it doesn't go very far with the details (guessing if a tabular form will work after changing certain details is somewhat tricky as we shall see...), so if you don't like the idea to start over again, may be the following explanations are of some interest or at least worth a try.

Let's examine first in pictures what Apex (Apex 4.0.2 in my case) does when it creates a tabular form report based on the EMPLOYEES table in the HR schema (I will skip some intermediate screens to cut the story short):

Apex builds the following query definition basing on our selections:

Please note the double entry for the primary key column EMPLOYEE_ID.

Note also that the report column containing the primary key value is defined as Hidden, but even if it is not visible it will retain its state.

 Note also the following definition, it's only visible if the column was selected as a primary key (or member thereof).

 Now, let's try our freshly built tabular form and delete the first row (just make sure to pick an entry who is not a manager of someone else).

 It worked.

Now, let's see what happens as soon as I change some fundamental attribute, like the Display-As property.
If I choose Standard Report Column, the column won't save the corresponding value inside the collection any more.

 Imagine that the justification for doing this, was to display an edit link:

I need to change also the "Show" property in order to make it appear in the report.

Ok, now the Show column at the far right is checked also for EMPLOYEE_ID.

What happens if I test my report (make sure to reload the page first to work with the new tabular form structure).

It fails exactly with the run-time error being discussed:

ORA-20001: Error in multi row delete operation: row = nnn, ORA-1403, no data found. 

Error in multi row operation failed

Assuming you reverted the column to the "Hidden" value in Display-As and also removed the attributes for creating a link, you should be able to get it back to work again.

But it is not the only way to destroy the operation, for instance, see what happens if you move down the column list the "row selector". 

To begin with, the checkbox allowing you to check/uncheck all the rows in one shot it's gone, but that's the least of the problem.

Again, the operation is disrupted.

This happened because the "row selector" pseudo column was moved after another column that saves its state (in this case the EMPLOYEE_ID column). More on this at the end of the article.

So, if you need to customize the report with an edit link, either use the primary key column alias (EMPLOYEE_ID_DISPLAY) or add a new column link from the "Tasks" list on the right hand side of the apex page.
Here I opted for the former method.
 

The checked symbol appears under the Link column:

Make sure to move back up the "row selector" column to the top of the list and the tabular form should be working again normally.

Finally, what happens if you accidentally delete the primary key column from the query source?
Well, the operation is clearly disrupted, but if you put the column back, then you need to:

1. change the Display-As attribute to "Hidden" or to any other value thats "saves state"
2. move the primary key column to the top of the list, always after the "row selector". It doesn't really matter the exact position number as long any preceding column does not save state.

In case you don't need to delete rows, you can later safely remove the "row selector" pseudo column, which can also be reinstated from the "Tasks" list on the right hand side of the screen (note that when you do so, Apex will always insert it at the top of the list).

In conclusion, the best choice is to avoid tampering with the ordering of the columns containing the primary key and the row selector, and leave them in the same position where Apex originally created them.