title: Only one row added "ORA-02290: check constraint (SYS_C0012762) violated ORA-01403: no data found"
body: <p>I created a package which loads data from a staging table to a live table in Oracle DB. The package comprises of four functions which perform live load, update, insert and error check on the records. For some reason, I am only able to load one record from the staging table and then the following error occurs:</p>
  
  <blockquote>
    <p>ORA-02290: check constraint (VIQDATA.SYS_C0012762) violated ORA-01403: no data found</p>
  </blockquote>
  
  <pre><code>create or replace PACKAGE     VAL_PROC
  AS
  pi_sif_id        NUMBER := 0;
  
   CURSOR v_d_c (pi_sif_id NUMBER)
   IS
    SELECT * 
    FROM S_V_C_R
     WHERE SIF_ID =PI_SIF_ID
     AND   SVC_PROCESS_FLAG='N';
  
   TYPE v_d_c_t IS TABLE OF v_d_c%ROWTYPE;
  
   FUNCTION vr_live_load (pi_sif_id              NUMBER,
                             po_err_msg         OUT VARCHAR2,
                             po_success_count   OUT NUMBER,
                             po_failed_count    OUT NUMBER)
      RETURN NUMBER;
  
      FUNCTION check_error_record (p_v_d_r   IN     vr_dtl_cur%ROWTYPE,
                              lv_prc_code         OUT VARCHAR2,
                              lv_err_flag         OUT VARCHAR2)
      RETURN NUMBER;
  
      FUNCTION insert_v (p_v_d_r IN v_d_c%ROWTYPE, po_err_msg OUT VARCHAR2, po_prc_code OUT VARCHAR2)
      RETURN NUMBER;
  
      FUNCTION update_v (p_v_d_r IN v_d_c%ROWTYPE, po_err_msg OUT VARCHAR2, po_prc_code OUT VARCHAR2)
      RETURN NUMBER; 
  
  END VAL_PROC;
  
  create or replace PACKAGE BODY     VAL_PROC
  AS
  LV_X1_ID     SERIALS.X1_ID%TYPE;
  LV_VC_ID     VR_COM.X1_ID%TYPE; --change name of this variable later
  
   FUNCTION vr_live_load(pi_sif_id              NUMBER, --live load function
                             po_err_msg         OUT VARCHAR2,
                             po_success_count   OUT NUMBER,
                             po_failed_count    OUT NUMBER) 
    RETURN NUMBER
    IS
    v_d_r             v_d_c_tbl;
    dup_prc                 NUMBER := 0;
    E_DUP_PRC               EXCEPTION;
    RETVAL                  NUMBER;
    pi_d_co             VARCHAR2(10);
    ERR_FLAG                VARCHAR2 (1);
    ret_prc_code            VARCHAR2 (15);
    lv_err_code             VARCHAR2 (1000);
    lv_found                NUMBER;
    lv_PRS_ID               NUMBER;
    lv_sif_filename         VARCHAR2 (200);
    lv_proc_success_count   NUMBER := 0;
    lv_proc_failed_count1   NUMBER := 0;
    BEGIN
    BEGIN
       SELECT COUNT (1)
         INTO DUP_PRC
         FROM s_fi
        WHERE SIF_ID = PI_SIF_ID AND s_p_f = 'P';
  
       IF dup_prc &gt; 0
       THEN
          RAISE e_dup_prc;
       END IF;
  
       SELECT sif_filename
         INTO lv_sif_filename
         FROM s_fi
        WHERE sif_id = pi_sif_id;
  
       INSERT INTO p_s (PCG_CODE,
                                  SIF_ID,
                                  PRS_START_DATETIME,
                                  sif_filename)
            VALUES ('V_ODL',
                    pi_sif_id,
                    SYSDATE,
                    lv_sif_filename)
         RETURNING PRS_ID
              INTO lv_PRS_ID;
  
       COMMIT;
  
       OPEN v_d_c(pi_sif_id);
       LOOP
          FETCH v_d_c
          BULK COLLECT INTO v_d_r
          LIMIT 1000;
  
          FOR i IN 1 .. v_d_r.COUNT
          LOOP
             retval :=
                check_error_record (p_v_d_r   =&gt; v_d_r(i),
                                    lv_prc_code      =&gt; ret_prc_code,
                                    lv_err_flag      =&gt; err_flag);
  
             IF retval = 0
             THEN
                IF ERR_FLAG = 'N'
                THEN
                   BEGIN
                      SELECT 1
                        INTO lv_found
                        FROM VR_COM
                       WHERE X1_ID = LV_X1_ID; --v_d_r(i).SVC_d_co = D.X1_ID;                         
  
                      retval :=
                         update_vr (p_v_d_r   =&gt; v_d_r (i),
                                        po_err_msg       =&gt; lv_err_code,
                                        po_prc_code      =&gt; ret_prc_code);
  
                      UPDATE STG_COM_RECORDS
                         SET PRC_CODE = ret_prc_code,
                             S_E_M = lv_err_code,
                             S_P_F = 'P'
                       WHERE S_ID = v_d_r (i).S_ID;
  
                      IF retval = 0
                      THEN
                         lv_proc_success_count := lv_proc_success_count + 1;
                      ELSE
                         lv_proc_failed_count1 := lv_proc_failed_count1 + 1;
                      END IF;
                   EXCEPTION
                      WHEN NO_DATA_FOUND
                      THEN
                         retval :=
                            insert_v (
                               p_v_d_r   =&gt; v_d_r (i),
                               po_err_msg       =&gt; lv_err_code,
                               po_prc_code      =&gt; ret_prc_code);
  
  
                         UPDATE STG_COM_RECORDS
                            SET PRC_CODE = ret_prc_code,
                                S_E_M = lv_err_code,
                                S_P_F = 'P'
                          WHERE S_ID = v_d_r(i).S_ID;
  
                         IF retval = 0
                         THEN
                            lv_proc_success_count :=
                               lv_proc_success_count + 1;
                         ELSE
                            lv_proc_failed_count1 :=
                               lv_proc_failed_count1 + 1;
                         END IF;
                   END;
                ELSE /*update table with prc_code when err_flag &lt;&gt; 'N' */
                   UPDATE STG_COM_RECORDS
                      SET PRC_CODE = ret_prc_code,
                          S_E_M = lv_err_code,
                          S_P_F = 'E'
                    WHERE S_ID = v_d_r (i).S_ID;
  
                   lv_proc_failed_count1 := lv_proc_failed_count1 + 1;
                END IF;
                ELSE              --if check_error_record return non-zero                      value
                    UPDATE STG_COM_RECORDS
                      SET PRC_CODE = ret_prc_code,
                          S_E_M = lv_err_code,
                          S_P_F = 'E'
                    WHERE S_ID = v_d_r (i).S_ID;
  
                lv_proc_failed_count1 := lv_proc_failed_count1 + 1;
             END IF;
             COMMIT;
          END LOOP;
  
          EXIT WHEN v_d_c%NOTFOUND;
       END LOOP;
       CLOSE v_d_c;
       COMMIT;               
           EXCEPTION
       WHEN e_dup_prc
       THEN                                         --file already processed
          UPDATE s_fi
             SET s_p_f = 'E'
           WHERE sif_id = pi_sif_id;
  
          COMMIT;
          po_err_msg := 'This File has previously been processed.';
    END;
  
    UPDATE s_fi
       SET s_p_f = 'P'
     WHERE sif_id = pi_sif_id;
  
    UPDATE p_s
       SET PLS_END_DATETIME = SYSDATE, PRS_ERRORED = 'Successful'
     WHERE PRS_ID = lv_PRS_ID;
  
    --Populate file load summary
   BEGIN
       p_p_f_l_s (P_SIF_ID =&gt; pi_sif_id);
    EXCEPTION
       WHEN OTHERS
       THEN
          NULL;
    END; 
  
    COMMIT;
  
    po_success_count := lv_proc_success_count;
    po_failed_count := lv_proc_failed_count1;
    RETURN 0;
     EXCEPTION
    WHEN OTHERS
    THEN
       lv_err_code := SQLERRM;
  
       UPDATE s_fi
          SET s_p_f = 'E'
        WHERE sif_id = pi_sif_id;
  
       UPDATE p_s
          SET PLS_END_DATETIME = SYSDATE, PRS_ERRORED = lv_err_code
        WHERE PRS_ID = lv_PRS_ID;
  
       COMMIT;
       po_err_msg := SQLERRM;
  
       CLOSE v_d_c;
  
       RETURN SQLCODE;
  
   END vr_live_load;
  
         FUNCTION insert_v (p_v_d_r IN v_d_c%ROWTYPE, po_err_msg OUT VARCHAR2,po_prc_code OUT VARCHAR2) --inserts records to live table 
     RETURN NUMBER
       IS
       RETNUM   number := 0;
  
        begin
  
      INSERT INTO VR_COM (VC_ID,
                                 co_pe,
                               COUNTRY_CODE,
                               X1_ID,
                               TIME_PERIOD
                               )
            VALUES (VC_ID_SQ.NEXTVAL,
                 p_v_d_r.s_perc,
                 p_v_d_r.s_coun,
                 (select X1_ID from SERIALS where d_co=p_v_d_r.SVC_d_co),
                 p_v_d_r.s_time
                 );
  
         po_prc_code := 'S11';
          RETURN retnum;
       EXCEPTION
       WHEN OTHERS
       THEN
       PO_ERR_MSG := SQLERRM;
       PO_PRC_CODE := 'O11';
       DBMS_OUTPUT.PUT_LINE('SQLERRM :'||SQLERRM);         
       RETURN SQLCODE;
  
     END insert_v;
  
     FUNCTION update_vr (p_v_d_r IN v_d_c%ROWTYPE, po_err_msg OUT VARCHAR2,                     po_prc_code OUT VARCHAR2) --update the live table if data matches by ID 
       RETURN NUMBER
      IS
       LV_d_co     SERIALS.d_co%TYPE;
    BEGIN
    SELECT VRC.X1_ID, D.d_co
      INTO LV_VC_ID, LV_d_co
      FROM VR_COM VRC, SERIALS D
     WHERE VRC.X1_ID = D.X1_ID;
  
    update VR_COM
       set co_pe         = p_v_d_r.s_perc,      
           COUNTRY_CODE             = p_v_d_r.s_coun,
           TIME_PERIOD              = p_v_d_r.s_time                
  
     WHERE X1_ID = LV_VC_ID;
     return 0;
     EXCEPTION
    WHEN OTHERS
    THEN
       po_err_msg := SQLERRM;
       PO_PRC_CODE := 'O11';
       DBMS_OUTPUT.PUT_LINE('SQLERRM :'||SQLERRM);
       RETURN SQLCODE;
     END update_vr;
  
  
      FUNCTION check_error_record (p_v_d_r   IN     v_d_c%ROWTYPE,
                              lv_prc_code         OUT VARCHAR2,
                              lv_err_flag         OUT VARCHAR2) --check error
      RETURN NUMBER
     IS
    --LV_d_co     SERIALS.d_co%TYPE;
    retnum      NUMBER := 0;
    lv_found    VARCHAR2 (10);
    LV_FLAG     varchar2 (2);
    LV_PROC_FLAG     VARCHAR2 (2);
    BEGIN
    lv_prc_code := NULL;
    lv_err_flag := NULL;
  
    BEGIN
    IF LV_X1_ID IS NOT NULL  THEN
    SELECT d_co, d_a_s_c
    INTO LV_X1_ID, LV_FLAG
    FROM SERIALS
    WHERE p_v_d_r.SVC_d_co =d_co;
    END IF; 
  
          lv_err_flag := 'N';
           EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
             lv_prc_code := 'P5';
             lv_err_flag := 'Y';
             lv_proc_flag := 'E';
       END;
  
    IF p_v_d_r.SVC_d_co IS NULL
    THEN
       lv_prc_code := 'V2';
       lv_err_flag := 'Y'; 
    ELSIF p_v_d_r.s_perc IS NULL
    THEN
       lv_prc_code := 'V6';
       lv_err_flag := 'Y';
    ELSIF p_v_d_r.s_time IS NULL
    THEN
      lv_prc_code := 'V4';
      lv_err_flag := 'Y';
    ELSIF p_v_d_r.s_coun IS NULL
     THEN
      lv_prc_code := 'V1';
      lv_err_flag := 'Y';
    ELSE
      lv_prc_code := 'S11';
      lv_err_flag := 'N';
    end if;
    RETURN retnum;
      EXCEPTION
      WHEN OTHERS
    THEN
      lv_err_flag := 'Y';
      RETURN SQLCODE;
     END check_error_record;
  
   END VAL_PROC;
  </code></pre>
  
  <p>P.S <code>SYS_C0012762 constraint    Check SVC_PROCESS_FLAG IN ('E', 'N', 'S')</code></p>