2 Replies Latest reply: Apr 24, 2013 5:07 AM by O.Developer RSS

    How to Handle the exception while perform multiple insert in a procedure?

    1005119
      CREATE OR REPLACE PROCEDURE MUL_INS_EXCE AS
      CURSOR C IS SELECT EMP_NO,EMP_NAME,EMP_AGE FROM EMP;
      EMP_RECORD EMP %ROWTYPE;
      BEGIN

      OPEN C;
      LOOP
      FETCH C INTO EMP_RECORD;
      EXIT WHEN C%NOTFOUND;
      INSERT INTO (SELECT NO,NAME,AGE FROM E1 WHERE AGE >=23 WITH CHECK OPTION VALUE(EMP_RECORD.EMP_NO,EMP_RECORD.EMP_NAME,EMP_RECORD.EMP_AGE);
      END LOOP;
      CLOSE C;
      EXCEPTION
      WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      END;

      I try to insert the record from emp into e1 table with some condition using the procedure. If run this procedure it insert record with check the age > =23. The conditions is not satisfied, exception is thrown procedure will terminate. Again i run this procedure, it will insert the from first. but i want, insert rest of the records. pls help me.
        • 1. Re: How to Handle the exception while perform multiple insert in a procedure?
          Jim Smith
          This is the forum for Oracle's SQL Developer tool, not for general PL/SQL questions. Questions like this should be asked in the PL/SQL forum.

          Having said that, if you put your exception handler inside the loop, it will carry on after reporting each error.
          • 2. Re: How to Handle the exception while perform multiple insert in a procedure?
            O.Developer
            >






            CHange your insert code like below....it should work

            If emp_record.age > 23 Then
            Insert into table name (fileld......) values(values)...............

            End If;;



            >
            CREATE OR REPLACE PROCEDURE MUL_INS_EXCE AS
            CURSOR C IS SELECT EMP_NO,EMP_NAME,EMP_AGE FROM EMP;
            EMP_RECORD EMP %ROWTYPE;
            BEGIN

            OPEN C;
            LOOP
            FETCH C INTO EMP_RECORD;
            EXIT WHEN C%NOTFOUND;
            INSERT INTO (SELECT NO,NAME,AGE FROM E1 WHERE AGE >=23 WITH CHECK OPTION VALUE(EMP_RECORD.EMP_NO,EMP_RECORD.EMP_NAME,EMP_RECORD.EMP_AGE);
            END LOOP;
            CLOSE C;
            EXCEPTION
            WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(SQLERRM);
            END;

            I try to insert the record from emp into e1 table with some condition using the procedure. If run this procedure it insert record with check the age > =23. The conditions is not satisfied, exception is thrown procedure will terminate. Again i run this procedure, it will insert the from first. but i want, insert rest of the records. pls help me.