13 Replies Latest reply: May 14, 2012 5:52 AM by 709156 RSS

    Raise Application Error

    709156
      Hello,

      I have the following code where I am specifying raise_application error when condition is true so that exception will raise in my java code with matching error_code
      20122. But I have noticed that error is getting logged in my db_table which I have specified in EXCEPTION WHEN OTHERS
      THEN.

      I would like to know why there is no exception is getting raised in java class with error code 20122

      PLSQL Code.
      BEGIN
      
       IF (value > 0)
          THEN
              raise_application_error (-20122, 'DB Commit Not Allowed');
          END IF;
      
      ___
      
      EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
              NULL;
          WHEN OTHERS
          THEN
               insert into db_errors values(DBMS_UTILITY.format_error_backtrace,SQLERRM);
      Thanks
        • 1. Re: Raise Application Error
          indra budiantho
          DECLARE
           ecode NUMBER;
           emesg VARCHAR2(200);
          BEGIN
            NULL;
            ecode := SQLCODE;
            emesg := SQLERRM;
            dbms_output.put_line(TO_CHAR(ecode) || '-' || emesg);
          END;
          /
          • 2. Re: Raise Application Error
            HuaMin Chen
            That error does speicifically belong to 'Others'; no specific error code in Pl/sql is for it.
            • 3. Re: Raise Application Error
              709156
              My problem is why this is not being fired
              raise_application_error (-20122, 'DB Commit Not Allowed');
              Thanks
              • 4. Re: Raise Application Error
                indra budiantho
                change your exception, and let me know:
                EXCEPTION
                    WHEN NO_DATA_FOUND
                    THEN
                        NULL;
                    WHEN OTHERS
                    THEN
                raise_application_error (-20122, 'DB Commit Not Allowed');
                • 5. Re: Raise Application Error
                  709156
                  Orchard wrote:
                  change your exception, and let me know:
                  EXCEPTION
                  WHEN NO_DATA_FOUND
                  THEN
                  NULL;
                  WHEN OTHERS
                  THEN
                  raise_application_error (-20122, 'DB Commit Not Allowed');
                  Yes this worked.

                  I have question is in WHEN OTHERS THEN part I would like to catch exceptions if occurs in my plsql procedure and catch exception if the following is true. Is this possible?
                  IF (value > 0)
                      THEN
                          raise_application_error (-20122, 'DB Commit Not Allowed');
                      END IF;
                  • 6. Re: Raise Application Error
                    indra budiantho
                    yes it is possible:
                    IF (value > 0)
                        THEN
                                   rollback;
                         insert into db_errors values(DBMS_UTILITY.format_error_backtrace,SQLERRM);
                         commit;
                            raise_application_error (-20122, 'DB Commit Not Allowed');
                        END IF;
                    and remove the exception parts.
                    • 7. Re: Raise Application Error
                      709156
                      Orchard wrote:
                      yes it is possible:
                      IF (value > 0)
                      THEN
                      rollback;
                           insert into db_errors values(DBMS_UTILITY.format_error_backtrace,SQLERRM);
                           commit;
                      raise_application_error (-20122, 'DB Commit Not Allowed');
                      END IF;
                      and remove the exception parts.
                      Thanks for the explanation.

                      I have one more question, how can I log or catch any errors if happens in my procedure other than
                       IF (value > 0) 
                      or some other general errors in the code?
                      • 8. Re: Raise Application Error
                        indra budiantho
                        DECLARE
                         ecode NUMBER;
                         emesg VARCHAR2(200);
                        BEGIN
                        ....
                        exception
                          
                        when others then
                        
                          ecode := SQLCODE;
                          emesg := SQLERRM;
                          dbms_output.put_line(TO_CHAR(ecode) || '-' || emesg);
                          if  -20122 = ecode  then
                             NULL;
                          else
                            insert...
                          end if;
                        END;
                        / 
                        • 9. Re: Raise Application Error
                          709156
                          Would they following works if some other exception happens in my code
                           WHEN OTHERS
                              THEN
                              RAISE;
                                  insert into db_errors values(DBMS_UTILITY.format_error_backtrace,SQLERRM);
                               commit;
                          • 10. Re: Raise Application Error
                            indra budiantho
                            When an exception is reraised, the first block searched is the enclosing block, not the current block.

                            so, put RAISE; at the end.
                            • 11. Re: Raise Application Error
                              BluShadow
                              {thread:id=697262}
                              • 12. Re: Raise Application Error
                                Galbarad
                                using autonomous transaction for log error
                                - create separated procedure for logging errors
                                - in this procedure use autonomous transaction
                                - use this procedure in exception block
                                - re-throw exception - raise;

                                when others then
                                log_procedure(sqlerrm);
                                raise;
                                • 13. Re: Raise Application Error
                                  709156
                                  I have modified my procedure like the following, kindly correct if problem still exists
                                  IS
                                  exec_my_error exception;
                                  BEGIN
                                   
                                   IF (value > 0)
                                      THEN
                                          raise exec_my_error;
                                  
                                      END IF;
                                   
                                  ___
                                   
                                  EXCEPTION
                                  WHEN       exec_my_error
                                     raise_application_error (-20122, 'DB Commit Not Allowed');
                                      WHEN NO_DATA_FOUND
                                      THEN
                                          NULL;
                                      WHEN OTHERS
                                      THEN
                                          logerrors(SQLERRM,DBMS_UTILITY.format_error_backtrace);
                                  RAISE;
                                  ---
                                  Edited by: ponic on May 14, 2012 1:52 PM