1 2 Previous Next 18 Replies Latest reply on Apr 23, 2019 4:46 PM by Paulzip

    Exception usage

    negru

      I have the following procedure:

       

        procedure insert_header(p_push_date      in date,
                              p_source_serial  in varchar2,
                              p_source_start_date  in date,
                              p_source_end_datein date,
                              p_source_rus_type_id in number,
                              p_output_serial  in varchar2,
                              p_filters_rus    in varchar2,
                              p_filters_indicators in varchar2,
                              o_cur_results        out sys_refcursor,
                              result_code    out number) is
                            

        id_temp number;

       

        begin

      -- check for errors
      if p_push_date is null
      then
        result_code := 9213;
        --raise_application_error(-RESULT_CODE, 'push_date cannot be null');
      elsif p_source_serial is null
      then
        result_code := 9333;
        --raise_application_error(-RESULT_CODE, 'source_serial cannot be null');

       

      elsif p_source_start_date is null
      then
        result_code := 9223;
        --raise_application_error(-RESULT_CODE, 'source_start_date cannot be null');
      elsif p_source_end_date is null
      then
        result_code := 9300;
        --raise_application_error(-RESULT_CODE, 'source_end_date cannot be null');
      elsif p_output_serial is null
      then
        result_code := 9301;
        --raise_application_error(-RESULT_CODE, 'output_serial cannot be null');
      end if;

       

      -- if there are no errors, do insert
      if result_code is null
      then
        -- fetch sequence number
        id_temp := header_seq.nextval;
        result_code:=0;

        open o_cur_results for

        select id_temp as id

        from dual;

         

        insert into log_push_readouts_headers
          (id, push_date, source_serial, source_start_date, source_end_date, source_rus_type_id, output_serial, filters_rus, filters_indicators)
        values
          (id_temp, p_push_date, p_source_serial, p_source_start_date, p_source_end_date, p_source_rus_type_id, p_output_serial, p_filters_rus, p_filters_indicators);
      end if;

         

       

       

      commit;

       

        end;

       

       

      Instead of raise_application_error, I want to use EXCEPTION, such that the procedure doesn't break completely.

      Could someone show how to do this?

        • 1. Re: Exception usage
          Cookiemonster76

          Use exception to do what exactly?

           

          Raise_application_error lets you raise oracle exceptions with error numbers in the range -20001 to -20999 and attach custom messages to them.

          It's often used for this sort of thing.

           

          I'm not at all sure what you think the problem is with using it or what you think replacing it will accomplish.

          • 2. Re: Exception usage
            John Thorton

            negru wrote:

             

            I have the following procedure:

             

            procedure insert_header(p_push_date in date,
            p_source_serial in varchar2,
            p_source_start_date in date,
            p_source_end_datein date,
            p_source_rus_type_id in number,
            p_output_serial in varchar2,
            p_filters_rus in varchar2,
            p_filters_indicators in varchar2,
            o_cur_results out sys_refcursor,
            result_code out number) is

            id_temp number;

             

            begin

            -- check for errors
            if p_push_date is null
            then
            result_code := 9213;
            --raise_application_error(-RESULT_CODE, 'push_date cannot be null');
            elsif p_source_serial is null
            then
            result_code := 9333;
            --raise_application_error(-RESULT_CODE, 'source_serial cannot be null');

             

            elsif p_source_start_date is null
            then
            result_code := 9223;
            --raise_application_error(-RESULT_CODE, 'source_start_date cannot be null');
            elsif p_source_end_date is null
            then
            result_code := 9300;
            --raise_application_error(-RESULT_CODE, 'source_end_date cannot be null');
            elsif p_output_serial is null
            then
            result_code := 9301;
            --raise_application_error(-RESULT_CODE, 'output_serial cannot be null');
            end if;

             

            -- if there are no errors, do insert
            if result_code is null
            then
            -- fetch sequence number
            id_temp := header_seq.nextval;
            result_code:=0;

            open o_cur_results for

            select id_temp as id

            from dual;

             

            insert into log_push_readouts_headers
            (id, push_date, source_serial, source_start_date, source_end_date, source_rus_type_id, output_serial, filters_rus, filters_indicators)
            values
            (id_temp, p_push_date, p_source_serial, p_source_start_date, p_source_end_date, p_source_rus_type_id, p_output_serial, p_filters_rus, p_filters_indicators);
            end if;

             

             

             

            commit;

             

            end;

             

             

            Instead of raise_application_error, I want to use EXCEPTION, such that the procedure doesn't break completely.

            Could someone show how to do this?

            exactly what should be done for each EXCEPTION above?

            What should be done when more than 1 exception exists?

            • 3. Re: Exception usage
              negru

              When using raise_application_error, it stops the procedure completely when some invalid parameter is set. I think that using EXCEPTION instead will not stop the whole procedure.

              • 4. Re: Exception usage
                negru

                When using raise_application_error, it stops the procedure completely when some invalid parameter is set. I think that using EXCEPTION instead will not stop the whole procedure.

                • 5. Re: Exception usage
                  John Thorton

                  negru wrote:

                   

                  When using raise_application_error, it stops the procedure completely when some invalid parameter is set. I think that using EXCEPTION instead will not stop the whole procedure.

                  Nobody here prevents you from implementing customized EXCEPTION handlers.

                  Please proceed to do so.

                   

                  What action do you want to occur when P_PUSH_DATE IS NULL?

                  • 6. Re: Exception usage
                    L. Fernigrini

                    If you do not want to "stop" the execution flow, what do you want to do instead? If an input parameter is NULL and the table does not accept null, then an error would arise on the INSERT sentence (and the procedure would "stop")

                     

                    You may add an output parameter and assign it the messages, and process them in the calling procedure or application. That way you will not consider those as "errors" but rather as additional information....

                    • 7. Re: Exception usage
                      Cookiemonster76

                      negru wrote:

                       

                      When using raise_application_error, it stops the procedure completely when some invalid parameter is set. I think that using EXCEPTION instead will not stop the whole procedure.

                      I think you need to have a read of PL/SQL 101 : Exception Handling

                      Exceptions raised with raise_application_error behave no differently to exceptions raised any other way.

                       

                      Also you really need to explain what you want the code to do, not just what you don't want it to do.

                      • 8. Re: Exception usage
                        negru

                        I want that the procedure continues to run, and not stop. I want that failed parameter is inserted in log, but that procedure continues to execute.

                        • 9. Re: Exception usage
                          John Thorton

                          negru wrote:

                           

                          I want that the procedure continues to run, and not stop. I want that failed parameter is inserted in log, but that procedure continues to execute.

                          You are free to write EXCEPTION handler that does what you desire.

                           

                          Consider to Read The Fine Manual below

                           

                          https://docs.oracle.com/database/121/LNPLS/errors.htm#LNPLS007

                          • 10. Re: Exception usage
                            Cookiemonster76

                            So modify the code to insert into a log table if result_code is not null.

                             

                            The code as written only does a single insert per procedure call so what exactly do you want to run if there's an issue?

                            • 11. Re: Exception usage
                              negru

                              So, when some invalid parameter is inserted, the procedure should not show any popup error window. Instead, it should only return result_code variable. I don't need log table.

                              • 12. Re: Exception usage
                                negru

                                How to do that?

                                • 13. Re: Exception usage
                                  John Thorton

                                  negru wrote:

                                   

                                  How to do that?

                                  write PL/SQL code.

                                  Do  you know how to write PL/SQL code?

                                  • 14. Re: Exception usage
                                    jaramill

                                    negru wrote:

                                     

                                    So, when some invalid parameter is inserted, the procedure should not show any popup error window. Instead, it should only return result_code variable. I don't need log table.

                                    Unless you're using Oracle Forms with event-based Form triggers with PL/SQL code inside said triggers, then back-end database stored programs have NO CONCEPT of pop-up error windows.  Your procedure does NOT indicate any code to show that a pop-up error window is showing.

                                    1 2 Previous Next