5 Replies Latest reply on Nov 16, 2018 10:37 AM by Dom Brooks

    exceptions

    3191197

      Hi Masters..

       

      I have written the proc .. please advise the exceptions part . whether it is correct or not..

       

      create or replace

      PROCEDURE           load_bt_qos_wan_shaper_test

      AS

      CURSOR INS_TAGS IS

      SELECT serialnumber

      ,filename

      ,rate

      ,inserted

      FROM bt_dbuser.bt_stg_qos_wan_shaper;

       

        my_limit number;

        my_inserted number;

       

       

      TYPE type_array IS TABLE OF bt_dbuser.bt_stg_qos_wan_shaper%ROWTYPE;

        my_batch_array type_array ;

       

      BEGIN

        my_inserted :=0;

        my_limit := 200;

       

        open INS_TAGS;

         loop

        fetch INS_TAGS bulk collect into my_batch_array limit my_limit;

                 FORALL i in 1..my_batch_array.count

                 insert into bt_dbuser.bt_qos_wan_shaper(id,serialnumber, filename, rate,inserted)

                 values(BT_DBUSER.BT_QOS_WAN_SHAPER_SEQ.nextval, my_batch_array(i).serialnumber,my_batch_array(i).filename, my_batch_array(i).rate, SYSDATE);

                 my_inserted := my_inserted + my_batch_array.count;

                 commit;

                 exit when INS_TAGS%notfound;

                 END LOOP;

        CLOSE INS_TAGS;

       

      EXCEPTION

       

         When No_Data_Found then

              dbms_output.put_line('No data in the table');

             

         when too_many_rows then

              dbms_output.put_line('Too many rows');

                    

      when others then

           dbms_output.put_line(' Diff Error');

          

        dbms_output.put_line('Total inserted '||my_inserted);

      END;

       

      Br

      Frd

        • 1. Re: exceptions
          Saubhik

          No, it is NOT correct. Read these:

          PL/SQL 101 : Exception Handling

          WHEN OTHERS - Oracle FAQ

          https://asktom.oracle.com/Misc/pokemon-and-when-others.html

          Also, read "My Mantra" from Tom Kyte: https://asktom.oracle.com/Misc/slow-by-slow.html

            • You should do it in a single SQL statement if at all possible.

           

            • If you cannot do it in a single SQL Statement, then do it in PL/SQL.

           

            • If you cannot do it in PL/SQL, try a Java Stored Procedure.

           

            • If you cannot do it in Java, do it in a C external procedure.

           

          • If you cannot do it in a C external routine, you might want to seriously think about why it is you need to do it…
          1 person found this helpful
          • 2. Re: exceptions
            top.gun

            From the doco there is an example:

             

            EXCEPTION

              WHEN NO_DATA_FOUND THEN

               DBMS_OUTPUT.PUT_LINE ('No Data found for SELECT on ' || temp);

              WHEN OTHERS THEN

              DBMS_OUTPUT.PUT_LINE ('Unexpected error');

              RAISE;

            END;

            1 person found this helpful
            • 3. Re: exceptions
              GregV

              Hi,

               

              It's good you're asking about exception handling because many pl/sql coders don't know how to do it properly. The thing to remember with pl/sql is you handle exceptions you expect. Unexpected exceptions must be raised to the caller.

              In your code how can you get no_data_found or too_many_rows exceptions? Also use dbms_output only for debug purpose. Use a proper log table instead.

              1 person found this helpful
              • 4. Re: exceptions
                JohnWatson2

                Given that you have a COMMIT within the loop, the exception you should be concerned about is 1555, snapshot too old.

                1 person found this helpful
                • 5. Re: exceptions
                  Dom Brooks

                  100% not.

                  I know you asked about the exceptions clause but the whole approach is wrong.

                  Your current code won't raise a no_data_found and it won't raise a too_many_rows.

                  The dbms_output won't go anywhere except serveroutput and you swallow the exceptions anyway so any calling code won't know it failed.

                   

                  create or replace PROCEDURE load_bt_qos_wan_shaper_test
                  AS
                  BEGIN
                     insert into bt_dbuser.bt_qos_wan_shaper(id,serialnumber, filename, rate,inserted)
                     select BT_DBUSER.BT_QOS_WAN_SHAPER_SEQ.nextval
                     ,      serialnumber
                     ,      filename
                     ,      rate
                     ,      inserted
                     FROM   bt_dbuser.bt_stg_qos_wan_shaper;
                     dbms_output.put_line('Total inserted '||SQL%ROWCOUNR);
                     COMMIT;
                  END;
                  

                   

                  You could add an optional dml error logging clause if relevant...