1 2 3 Previous Next 42 Replies Latest reply on Jul 20, 2018 1:24 PM by jaramill

    Commit data only if all procedures are successful

    Oracle_Monkey

      Hello

       

       

      create or replace procedure p1 as
      begin
      insert into t values ('p1');
      end;
      
      create or replace procedure p2 as
      begin
      insert into t values ('p2');
      
      
      end;
      
      
      
      create or replace procedure main_p as
      begin
      p1;
      p2;
      end;
      
      
      
      begin
      main_p;
      commit;
      
      
      
      
      end;
      

       

      I need to commit the data in the table only if both P1 and P2 procedure are successful.Am i doing the correct implementation.Or there is any better way?

        • 1. Re: Commit data only if all procedures are successful
          Cookiemonster76

          That is the best way generally - you call a procedure that frames the transaction - it calls other other procedures that do the individual changes that are part of the transaction. If the initial procedure completes without error you commit.

          That will ensure all the changes are committed as a single transaction or all of it is rolled back.

          The only way that doesn't work is if you have exception handlers in the procedures that swallow errors without re-raising them.

          Say you have:

          create or replace procedure p1 as 

          begin 

          insert into t values ('p1'); 

          EXCEPTION WHEN OTHERS

            <some logging>

          end; 

           

          Then p1 could fail, main_p wouldn't know and the insert p2 did could be committed without the corresponding insert to p1.

           

          The solution to that is to avoid writing exception handlers like that.

          • 2. Re: Commit data only if all procedures are successful
            Oracle_Monkey

            Cookiemonster76 Thanks so i shouldn't write exception handling in p1 and p2 at all?

            • 3. Re: Commit data only if all procedures are successful
              Cookiemonster76

              If you really want you can write this in the procedures:

              EXCEPTION WHEN OTHERS THEN

              <some logging>

              RAISE;

              END;

               

              But I personally have never seen the need. On my system we just let all exceptions propagate up to the client and have it log the full error stack.

               

              And of course if there is an exception you expect to happen in the procedure and don't want to crash the transaction then you should obviously write an exception handler to catch that (no_data_found is the most common one for this).

              • 4. Re: Commit data only if all procedures are successful
                BluShadow

                Oracle_Monkey wrote:

                 

                Cookiemonster76 Thanks so i shouldn't write exception handling in p1 and p2 at all?

                 

                 

                You can, but if you want the calling code to know that there was an exception you should always include a RAISE statement to raise up the exception after you've logged it (or done whatever the exception handling needs to do).

                 

                We have a community document about Exception Handling: PL/SQL 101 : Exception Handling

                • 5. Re: Commit data only if all procedures are successful

                  I wish more sites stuck to this plan.

                  At a previous site, just before I left, they decided to implement the brilliant idea of not returning error codes to the user, only a generic message that an error had occurred.

                  The reason was that 'users find the messages confusing and it makes them feel like they've done something wrong'.

                  Not sure exactly how they were expecting anybody to identify what the actual error was as they didn't also insist that the error was logged elsewhere.

                  • 6. Re: Commit data only if all procedures are successful
                    Cookiemonster76

                    To my mind re-interpreting oracle errors at the client should follow the same rules as catching them in the DB - only do if it's something you would expect to come up.

                    So if you've got a client screen that doesn't preemptively prevent them from sending duplicate data to the DB then I don't have a problem with catching the ORA-00001 and replacing it with a more user friendly message.

                    However, if the way the screen works means that ORA-00001 shouldn't happen then you shouldn't be catching it.

                    The only thing you should do with error text in those cases is maybe stick a "An error occurred, please contact support" sort of text on the front, while still displaying the full error stack - and of course log the whole error stack to a file so that support isn't relying on the user sending them screen-grabs to see what it's complaining about.

                     

                    A lot of people out there of the opinion that you need to minimise/hide/pretty up error display to avoid freaking out the users.

                    I'm of the opinion that freaking out users is the best way to ensure bugs don't get ignored.

                    • 7. Re: Commit data only if all procedures are successful
                      Stefan Jager

                      Oracle_Monkey wrote:

                       

                      Cookiemonster76 Thanks so i shouldn't write exception handling in p1 and p2 at all?

                      I wouldn't, depending on what exception it is and what the business requirements are. If there is the possibility for example that one of the selects in either procedure won't find anything, and for the Business it is acceptable if that happens, and it does not interfere with the workflow/process, then you could handle the no data found exception. But any other exception should simply be raised. Since only you know what the business requirements are you'll have to judge whether to handle which exceptions.

                       

                      With respect to the workflow in your code: I'd suggest you read this post from RP: Re: function with large code -how to make it more efficient.

                      Apply what he proposes, then the controller code (read his post, you'll see what I mean) can do the commit (or rollback) after evaluating the results of the other blocks of code.

                      • 8. Re: Commit data only if all procedures are successful
                        Stefan Jager

                        The Real Rob the Relic wrote:

                        it makes them feel like they've done something wrong.

                        Which was probably true

                        • 9. Re: Commit data only if all procedures are successful
                          GregV

                          Hi,

                           

                          If your procedures are somehow connected then place them in a package. You then have the main procedure calling p1 and p2 and committing at the end. Only one WHEN OTHERS block is necessary in the main procedure.

                          • 10. Re: Commit data only if all procedures are successful
                            Cookiemonster76

                            This may be a matter of personal preference / what clients you have but I rarely put commit or exception when others in the DB layer.

                            The client issues the commit if the stored proc it called to do the transaction returns without error, and if it does error out the client writes the full error stack to it's own log file.

                            The only exceptions to that are procedures that are run by the scheduler.

                             

                            What I've found a few times is that you can have a procedure that is it's own transaction that ends up being incorporated in a larger transaction. If that procedure committed that would be a problem. So I like to leave the committing to whatever code kicked the process off in the first place - and that's generally out in the client.

                            • 11. Re: Commit data only if all procedures are successful
                              Cookiemonster76

                              Just thinking about this: If the client was a completely external system over which I had zero control then I may well lean towards Greg's approach instead.

                              • 12. Re: Commit data only if all procedures are successful
                                L. Fernigrini

                                We used to log all errors using a stored procedure with autonomous transaction. And then raise the error depending on the environment

                                  • Development / Testing / User Acceptance--> Full error stack message + internal error log id.
                                  • Production --> Simpler message "Error - Contact Support - Error #zzzzzzzz" with internal error log id.

                                 

                                The idea was to avoid providing users with details that may confuse them, the single error log ID pointed to the place were all information required by help desk was available.

                                • 13. Re: Commit data only if all procedures are successful

                                  The key points for me here are ;-

                                  "We used to log all errors ..."

                                  "the single error log ID pointed to the place were all information required by help desk was available"

                                   

                                  The muppets at the site I was at, as stated, didn't insist on logging of the original error message and, as such, you can bet that most developers would not bother.

                                   

                                  I do, however, also have issues with logging elsewhere,

                                  a)  If logging to a table, how are errors handled which occur when inserting into that table, e.g. space issues, data issues

                                  b)  If logging to a file, how are errors handled e.g. if the directory/folder doesn't exist, incorrect permissions.

                                   

                                  I've seen code that, when trying to log errors to a table and hitting a failure condition due to space issues, tries to log that error to the same table, which hits a space error ... you can guess the rest

                                  • 14. Re: Commit data only if all procedures are successful
                                    GregV

                                    You're right my approach is more for batch processing. Maybe because that's what I mostly code packages for .

                                    I prefer to have control on the commit and exception logging within my procedures rather than trusting the caller, especially the one my company uses .

                                    To me the log table is more for the support team rather than for the customer, though I've seen screen in the app where you could query the log table.

                                    1 2 3 Previous Next