1 2 Previous Next 22 Replies Latest reply: Sep 2, 2011 9:58 AM by Alexandra Robin RSS

    ORA-20505: Error in DML : ORA-20503:

    636824
      Hi, I am desperate today. I am running Application Express 3.1.1.00.09 and Oracle 11g on Windows Vista Business. Insert and update transactions are fine, but I cannot do any updates. Updates result in the following: ORA-20505: Error in DML: p_rowid=2, p_alt_rowid=CUSTOMER_ID, p_rowid2=, p_alt_rowid2=. ORA-20503: Current version of data in database has changed since user initiated update process. current checksum = "D44B184147F3B02B987DB8C08F6C7A9C" application checksum = "0". I can update the records through SQL Developer 1.5 without any problems.

      Please, please HELP!!!!!
        • 1. Re: ORA-20505: Error in DML : ORA-20503:
          60437
          Meyer,

          When you say "Insert and update transactions are fine, but I cannot do any updates.", what are you saying with respect to updates?

          If you can demonstrate the problem on apex.oracle.com, that would make it easier to diagnose.

          Scott
          • 2. Re: ORA-20505: Error in DML : ORA-20503:
            636824
            Sorry about my ignorance. I meant insert and delete don't have any problems. I have the problems on update.
            Thanks for your response.
            • 3. Re: ORA-20505: Error in DML : ORA-20503:
              60437
              Meyer,

              If you can show us what you're doing on apex.oracle.com, just tell me the workspace name and application and page ID and I'll check it.

              Scott
              • 4. Re: ORA-20505: Error in DML : ORA-20503:
                97164
                Hi Scott, there seems to be a bug in 3.1/3.1.1...
                We will see this using the DEMO_ schema and SampleApplication.
                I created a master-detail on ´demo_orders/order_items. It works fine at 3.0.1, but does NOT at 3.1/3.1.1. got error:
                ORA-20505: Error in DML: p_rowid=7, p_alt_rowid=ORDER_ID, p_rowid2=, p_alt_rowid2=. ORA-20503: Current version of data in database has changed since user initiated update process. current checksum = "8E8F095FF0703E79EC72771544E61DF7" application checksum = "D200003179CCF569BDB5995F64FD8778"
                Error Verarbeiten von Zeile der Tabelle DEMO_ORDERS nicht möglich.

                You can see this at:
                http://apex.oracle.com/pls/otn/f?p=TEST_MD

                If I use a dept/emp (with sequences for deptno/empno) it works at 3.1/3.1.1 fine.
                The difference is that order_items has an after_delete trigger for recalculation the orders_total in orders.
                If I disable this trigger it works fine.

                But why is the sampleapplication able to delete order_items?
                If we look at the process-area we find in SAMPLE:
                Processes After Submit
                30 Process Row of DEMO_ORDERS Automatic Row Processing (DML) Unconditional
                40 ApplyMRU Multi Row Update Unconditional
                50 ApplyMRD Multi Row Delete Conditional
                60 reset page Clear Cache for all Items on Pages (PageID,PageID,PageID) Conditional
                70 AddRows Add rows to tabular form Conditional

                But from APEX will be generated for Master-Detail by Default:
                Processes After Submit
                10 Get PK PL/SQL anonymous block Conditional
                30 ApplyMRU Multi Row Update Unconditional
                40 ApplyMRD Multi Row Delete Conditional
                50 Process Row of DEPT Automatic Row Processing (DML) Unconditional
                60 reset page Clear Cache for all Items on Pages (PageID,PageID,PageID) Conditional
                70 AddRows Add rows to tabular form Conditional

                in the 3.0.1 environment (it works) was generated:
                Processes After Submit
                10 Get PK PL/SQL anonymous block Conditional
                10 ApplyMRU Multi Row Update Conditional
                20 ApplyMRD Multi Row Delete Conditional
                30 ApplyMRU Multi Row Update Conditional
                30 Process Row of DEMO_ORDERS Automatic Row Processing (DML) Unconditional
                40 reset page Clear Cache for all Items on Pages (PageID,PageID,PageID) Conditional
                40 AddRows Add rows to tabular form Conditional

                So I assume there is a bug in 3.1/3.1.1.
                • 5. Re: ORA-20505: Error in DML : ORA-20503:
                  638663
                  Hello,

                  I am suffering from the same problem now.

                  I have a form with some fileds. I have no problem inserting new record. However, when I try to update, I receive an error message:

                  ORA-20505: Error in DML: p_rowid=5, p_alt_rowid=UNAVAIL_BED_CODE, p_rowid2=, p_alt_rowid2=. ORA-20503: Current version of data in database has changed since user initiated update process. current checksum = "3459CDD5EA9961FE8B92B2A1148E1B37" application checksum = "0"

                  Anyone can help?
                  • 6. Re: ORA-20505: Error in DML : ORA-20503:
                    363770
                    Hello,

                    I too am receiving the following message:

                    ORA-20505: Error in DML: p_rowid=152, p_alt_rowid=TASK_ID, p_rowid2=, p_alt_rowid2=. ORA-20503: Current version of data in database has changed since user initiated update process. current checksum = "43C58C88966C89F66B7B5625ED0B9D7E" application checksum = "406AFB0B0A1993697A363008A4D3D122"
                    Error Unable to process row of table TASK_T.
                    OK

                    All I am trying to do is edit a description field on a form. I can insert but not update or delete. There doesn't seem to be much documentation or resolution on this. I'm dead in the water...

                    Thanks,

                    /Dave
                    • 7. Re: ORA-20505: Error in DML : ORA-20503:
                      654495
                      I am also experiencing this same error.

                      My page is used to enter Metadata about a particular Content File in this case PDF. I went one step further and use a procedure to retrieve the file and display it in an iframe on the page so they can view the PDF and input Metadata all in the same web window. When the user clicks update data they get the 2050 error. even if they make no changes whatsoever and click submit they get the error. Now this works fine when I retrieve a video without using an iframe and I use the Quicktime and Flash embed HTML meaning no errors. PLEASE HELP.....

                      The iframe code is as follows:
                      <iframe src="#OWNER#.util_pkg.download_file?p_file_id=&P3_CONTENT_ID." style="height:50em" style="width:45em"</iframe>

                      The procedure download file is as follows:
                      CREATE OR REPLACE PROCEDURE download_file(p_file_id IN content.content_id%TYPE)
                      AS
                      CURSOR c_my_file IS
                      SELECT content_file, mime_type, dbms_lob.getlength(content_file) file_length, file_name
                      FROM content
                      WHERE content_id = p_file_id;

                      rec_my_file c_my_file%ROWTYPE;

                      BEGIN

                      OPEN c_my_file;
                      FETCH c_my_file INTO rec_my_file;

                      IF c_my_file%FOUND THEN
                      owa_util.mime_header(rec_my_file.mime_type, FALSE);
                      htp.p('Content-length: ' || rec_my_file.file_length);
                      htp.p('Content-Disposition: attachement; filename="'||rec_my_file.file_name||'"');
                      owa_util.http_header_close;
                      wpg_docload.download_file(rec_my_file.content_file);
                      END IF;

                      CLOSE c_my_file;

                      END download_file;


                      my exact error is as follows:
                      ORA-20505: Error in DML: p_rowid=40, p_alt_rowid=CONTENT_ID, p_rowid2=, p_alt_rowid2=. ORA-20503: Current version of data in database has changed since user initiated update process. current checksum = "3BFFAD5D72B9BA4329052DA7F6C9BBBC" application checksum = "0"

                      EDIT: Never Mind I figured it out I was missing a " at the end of my html generated via PLSQL.

                      Edited by: user651492 on Sep 18, 2008 8:53 AM
                      • 8. Re: ORA-20505: Error in DML : ORA-20503:
                        661687
                        When I try to edit any page in my applications and run it to view the changes I get the following error type ORA-20505: Error in DML: p_rowid=141916328735378178, p_alt_rowid=ID, p_rowid2=, p_alt_rowid2=. ORA-20503: Current version of data in database has changed since user initiated update process. current checksum = "7E30BB235342C7CEFDE6E56167644F62" application checksum = "305B2F38F16876019DE2D15CB027D04A"

                        I too am dead in the water on my new development pc with APEX 3.1.1.00.09. I am running Windows XP Professional version 2000 Service Pack 2 with IE 6.0.2900.2180.xpsp_sp2_gdr.070220-2254 Update Version SP2. However, I have no problem with APEX 3.1.1.00.09 on my old laptop running the same version of XP with IE 7. I tried loading IE 7 on my development pc to see if this would fix the problem, but it did not. I can reproduce this over and over agian, but cannot figure out what needs to changed. Any help would be appreciated.

                        Edited by: user4700695 on Sep 24, 2008 10:31 AM

                        EDIT: I run the same scenario in Firefox and everything works fine. For me this is definitely isolated to IE.

                        Edited by: user4700695 on Oct 10, 2008 10:57 AM

                        EDIT: Problem due to add-on in IE.

                        Edited by: user4700695 on Oct 13, 2008 11:28 AM
                        • 9. Re: ORA-20505: Error in DML : ORA-20503:
                          422402
                          Scott,

                          I am getting also: ORA-20505: Error in DML: ... ORA-20503: Current version of data in database has changed since user initiated update process. ...
                          My application is [http://apex.oracle.com/pls/otn/f?p=11751], the page with the problems is page 3. Can you please have a look what is generating it?

                          Thanks in advance for your support,
                          Florin
                          • 10. Re: ORA-20505: Error in DML : ORA-20503:
                            60437
                            I copied the setDateFormat process as an after-submit process. Maybe that fixed it.

                            Scott
                            • 11. Re: ORA-20505: Error in DML : ORA-20503:
                              422402
                              Thanks Scott. Seems to work ... The error message was very misleading in my opinion :) Anyway good that it works :)
                              • 12. Re: ORA-20505: Error in DML : ORA-20503:
                                60437
                                The error message was very misleading in my opinion
                                The error message was accurate and precise. I think you would like to have been presented a message at design time telling you that there was a possibility that the way you built your page might cause it to produce an error at runtime, but I don't know how in the world we could analyze every bit of PL/SQL developers put into a page and come to any useful conclusions.

                                Scott
                                • 13. Re: ORA-20505: Error in DML : ORA-20503:
                                  422402
                                  :)

                                  So the Before Header Process setDateFormat screwed the things? I put it in order to be able to display the Registration Date as 'DD-Mon-YYYY hh24:mi:ss'.
                                  What is the recommended way of doing that otherwise? Or what is wrong in the design of the page?

                                  Thanks for your prompt feedback,
                                  Florin
                                  • 14. Re: ORA-20505: Error in DML : ORA-20503:
                                    60437
                                    Florin,
                                    So the Before Header Process setDateFormat screwed the things?
                                    Yes. When the row was fetched and the date column was converted to character format using the format mask you established with "alter session" the md5 checksum of column values was based on that formatted date string. When the row was fetched again, after submit, for the optimistic locking check a database session was used which had the default date format. This resulted in a differently formatted date string and a checksum that did not match that submitted with the page. So the solution is to set up the database sessions consistently during both show and accept phases.

                                    Scott
                                    1 2 Previous Next