Saturday, July 09, 2011

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.

10 comments:

Mohammed Taj said...

Nice post

Richard said...

Thank you! - the 'row-selector-must-be-first' rule was the cause of my problem. It would have taken a long time to figure that one out!

Anonymous said...

Thanks for your article. I just discovered another "funy" thing about tabular forms. If you for some reason have a hidden item on the page which gets the name f01 and id f01_0001, it will break the tabular form and return "No data found". The reason for this is that there is some javascript running in the background trying to fetch the checked items and it looks for f01.

In my case we had a report region on page 0, which had a hidden item that got the above name. After deleting it everything worked fine. Took some time to find that error though :)

Anonymous said...

Fantastic, thank you, just saved my life. Nice post, nice explaination, life saving solution.

Josep Coves said...

Thank you!! I was going crazy and I didn't know how to fix tabular form! I created a new PK on an existing table and I didn't know why I wasn't unable to update hidden fields! Placing PK column just after checkbox column solved the issue! Many, many thanks!

Anonymous said...

erdswerThanks, worked for me!

Janel said...

hi...hopefully this is an easy one...How were you able to access the HR table? Or rather how are you logged in as and what privileges does your parsing schema have?
I am trying to do something similar over an existing oracle table to which my schema has select, update, insert and delete privileges, but as soon as I select it from the Tabular Form wizard, I get an alert saying "You do not have access to the schema that you are importing. Import failed."
Any ideas would be greatly appreciated! (Apex 4.2.2.00.11)
Thanks,
Janel

Byte64 said...

Have you tried adding the schema as a secondary schema for the workspace?
You need to be a workspace admin to do that, just in case.

Alternatively, you may want to grant the required oracle privileges to the primary oracle schema user of your current workspace.

In either way, it should work, but you may need to use the schema prefix if you didn't create synonyms for HR's tables.

Anonymous said...

Fantastic, thank you.

I'd made a copy of the page and the primary key had switched to a standard report column. Changed it back to hidden as you said, works perfectly.

Would not have found that without this article, especially since the Apex error message is as useful as a chocolate fireguard, so thank you.

Hawk said...

Thank you very much. I have sprint demo tomorrow and just found that the tab form delete is NOT working. I had changed hidden field to save state (to fix the insert) and it broke the delete. I moved that field down a couple notches and now it works.

Thanks x1000, Hawk

yes you can!

Two great ways to help us out with a minimal effort. Click on the Google Plus +1 button above or...
We appreciate your support!

latest articles