title: Is ORA-01403 being received by the procedure or the package being called within the procedure?
body: <p>Sorry to paste the long code. I have a procedure that inserts data in a table and calls several packages. On running the below procedure, I get following error - </p>
  
  <p><a href="https://i.stack.imgur.com/6apzE.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/6apzE.png" alt="ORA-01403"></a></p>
  
  <p>Now I know that the error is coming at stage 3 where package <code>PKG_CM_ACTIONS.P_NEW_CASE_NOTIFY</code> is being called. My questions are -</p>
  
  <ol>
  <li><p>Is the ORA 01403 being thrown because there is no data to call <code>PKG_CM_ACTIONS.P_NEW_CASE_NOTIFY</code>?</p></li>
  <li><p>The ORA 01403 is being thrown because <code>PKG_CM_ACTIONS.P_NEW_CASE_NOTIFY</code> is getting called but not returning anything.</p></li>
  </ol>
  
  <p>Code :</p>
  
  <pre><code>create or replace PROCEDURE P_BNS_CREATE_CASE_MNUL
      IS
      L_CASE_INTRL_ID         VARCHAR2(15);
      L_STAGE                 NUMBER;
      L_ERROR_CODE            NUMBER;
      L_ERROR_MSG             VARCHAR2(4000);
  
    TYPE R_PARAMS IS RECORD (
          CUSTOMER_ID        VARCHAR2(50),
          CUSTOMER_TYPE      VARCHAR2(50),
          SCORE_CT           NUMBER(6),
          PRIORITY_CD        VARCHAR2(20),
          JRSDCN_DESC        VARCHAR2(50),
          CASE_TITL_NM       VARCHAR2(200),
          CASE_TYPE_CD       VARCHAR2(20),
          CASE_SUBTYPE_CD    VARCHAR2(20),
          OWNER_ID           NUMBER(10), 
          ASSIGNED_TO_ID     NUMBER(10),
          SYSTEM_ID          NUMBER(10)
      );
  
      TYPE T_PARAMS IS TABLE OF R_PARAMS INDEX BY PLS_INTEGER;
      L_PARAMS T_PARAMS;
  
      BEGIN
      L_STAGE := 1;
  
      SELECT CUSTOMER_ID, t3.CODE_VAL, SCORE_CT, PRIORITY_CD, t2.JRSDCN_CD, CASE_TITL_NM, CASE_TYPE_CD, CASE_SUBTYPE_CD, T5.OWNER_SEQ_ID, T6.OWNER_SEQ_ID, T7.OWNER_SEQ_ID
      BULK COLLECT INTO L_PARAMS
      FROM TB_BNS_CREATE_CASE_MNUL t1 LEFT JOIN KDD_JRSDCN t2 
          ON upper(t1.JRSDCN_DESC) = upper(t2.JRSDCN_DESC_TX)
      LEFT JOIN   KDD_CODE_SET_TRNLN t3
          ON upper(t1.CUSTOMER_TYPE) = upper(t3.CODE_DISP_TX) AND t3.CODE_SET = 'CustomerType'
      LEFT JOIN KDD_REVIEW_OWNER t5
          ON upper(t1.OWNER_ID) = upper(t5.OWNER_DSPLY_NM)
      LEFT JOIN KDD_REVIEW_OWNER t6
          ON upper(t1.ASSIGNED_TO_ID) = upper(t6.OWNER_DSPLY_NM)
      LEFT JOIN KDD_REVIEW_OWNER t7
          ON upper(t7.OWNER_ID) = 'SYSTEM'   
      WHERE CASE_INTRL_ID IS NULL;
  
      FOR INDX IN 1 .. L_PARAMS.COUNT LOOP
  
      L_STAGE := 1.1;
      SELECT PARAM_VALUE_TX||CM_CASES_SEQ.NEXTVAL INTO L_CASE_INTRL_ID  
      FROM KDD_INSTALL_PARAM WHERE PARAM_ID=17; /*this statement also returns results */
  
      L_STAGE := 2;
  
      INSERT INTO KDD_CASES (ID,NAME,...) VALUES (....,...,....);     
  
      L_STAGE := 3;
      PKG_CM_ACTIONS.P_NEW_CASE_NOTIFY(L_CASE_INTRL_ID, 'SYSTEM', '0', L_PARAMS(INDX).SCORE_CT, L_PARAMS(INDX).CUSTOMER_ID, 'a', L_PARAMS(INDX).CASE_SUBTYPE_CD);
  
      L_STAGE := 4;
      PKG_CM_TABS.P_KYC_MANUAL_CASE_INS(L_CASE_INTRL_ID, L_PARAMS(INDX).CUSTOMER_ID, L_PARAMS(INDX).SCORE_CT);
  
      L_STAGE := 5;
      PKG_CM_TABS.P_UPDATE_CASE_DUEDT('CA9', L_CASE_INTRL_ID);
  
      L_STAGE := 6;
      UPDATE TB_BNS_CREATE_CASE_MNUL
      SET CASE_INTRL_ID = L_CASE_INTRL_ID
      WHERE CUSTOMER_ID = L_PARAMS(INDX).CUSTOMER_ID AND CASE_TYPE_CD = L_PARAMS(INDX).CASE_TYPE_CD;
  
      END LOOP;
  
      --COMMIT;
      EXCEPTION
          WHEN OTHERS THEN
              ROLLBACK;
              L_ERROR_CODE := SQLCODE;
              L_ERROR_MSG  := SQLERRM;
              DBMS_OUTPUT.PUT_LINE (L_ERROR_CODE||' - Error occured at stage -'||L_STAGE||' - '||L_ERROR_MSG);   
      END P_BNS_CREATE_CASE_MNUL;
  
  
    [1]: https://i.stack.imgur.com/Lde3j.png
  </code></pre>