title: how to handle exception when no rows returned in sql
body: <p>I just know basics of sql. I have a procedure in which we are looking fetching rows from a table based on some conditions. Earlier the assumption was we will always have at least one row in the result. </p>
  
  <p>But now there is  chance that we get no rows  as well. I need to update the procedure to handle this exception and return null values as the output of the procedure. Any help is very much appreciated. </p>
  
  <p>This is the procedure:</p>
  
  <pre><code>create or replace
  PROCEDURE C1_OSO_PC_LIST (v_service_order_id  VARCHAR2,
            C1_OSO_PC_LIST_cv IN OUT cv_types.customer_tp) IS
  
  d_count                 NUMBER := 1;
  d_periodic01            VARCHAR2(240) := NULL;
  d_apply_day             NUMBER(6);
  d_rc_term_id            NUMBER(10);
  d_offer_id            NUMBER(10);
  
  
  CURSOR c_pc_to_add_list IS
  
    SELECT ofv.offer_id,
      ofv.display_value,
      oi.member_inst_id component_inst_id,
      oi.member_inst_id2 component_inst_id_serv, 
      b.component_id component_id   ---- tymczasowe obejÅ›cie problemu
    FROM ord_item oi,
      EXTERNAL_ENTITY_MAPPING eem,
      EXTERNAL_ENTITY_MAPPING_TYPE eemt,
      C1_OFFER_VALUES ofv,
      P4_BUNDLES b
    WHERE eem.internal_id1          = oi.member_id 
      AND eem.entity_mapping_type   = eemt.entity_mapping_type
      AND eemt.internal_entity_type = 'Component'
      AND eemt.external_entity_type = 'SubSupplementaryOffer'
      AND oi.item_action_id         = 10 -- ADD
      AND oi.member_type            = 40 -- package component
      AND ofv.language_code        = 1
      AND ofv.offer_id              = eem.external_id1
      AND nvl(b.c1_mode, 0) not in (1,2)
      -- AND nvl(b.c1_mode, 0)         = 0
      AND EXISTS (SELECT 1  -- Assumed to have AT MOST one RC per OFFER in C1
                   FROM C1_OFFER_RC_TERM_MAP ortm, C1_OFFER_VALUES ofv2,        C1_RC_TERM_REF rtr
                  WHERE ortm.offer_id = ofv.offer_id
                    AND ortm.offer_id = ofv2.offer_id
        AND ortm.rc_term_id = rtr.rc_term_id
                    AND nvl(rtr.APPLY_DAY,0)     = 0
                    AND ofv2.language_code=1
                    AND ofv2.display_value NOT IN ('DATA_SHARE_10',
                                                  'DATA_SHARE_11',
                                                  'DATA_SHARE_12',
                                                  'DATA_SHARE_8',
                                                  'DATA_SHARE_9',
                                                  'EU_MIN_PACK_1',
                                                  'EU_MIN_PACK_2',
                                                  'ZERO_VP')
                )
      AND b.component_id  (+) = oi.member_id
      AND oi.service_order_id = TO_NUMBER(v_service_order_id)
    ORDER BY oi.is_cancelled, 
            b.component_id; ---- tymczasowe obejcie problemu
  
  d_periodic_charge_row c_pc_to_add_list%ROWTYPE;
  
  BEGIN
  
    OPEN c_pc_to_add_list;
    LOOP
  
      FETCH c_pc_to_add_list 
       INTO d_periodic_charge_row;
       EXIT WHEN c_pc_to_add_list%NOTFOUND;
  
      IF (d_count = 1) THEN
            -- d_periodic01 := d_periodic_charge_row.periodic_name;
            d_periodic01 := d_periodic_charge_row.display_value;
  
            SELECT nvl(rtr.APPLY_DAY,0), rtr.rc_term_id, d_periodic_charge_row.offer_id
             INTO d_apply_day, d_rc_term_id, d_offer_id
             -- NB:- This is dangerous because C1_OFFER_RC_TERM_MAP has pk on OFFER_ID, RC_TERM_ID, RESELLER_VERSION_ID
             -- and here we don't restrict on rc_term_id.
             -- This is because C1 solution is supposed to have AT MOST one RC per OFFER in C1
             -- 
             FROM C1_OFFER_RC_TERM_MAP ortm, C1_RC_TERM_REF rtr
            WHERE ortm.offer_id = d_periodic_charge_row.offer_id
              AND ortm.rc_term_id = rtr.rc_term_id
              -- AND nvl(rtr.APPLY_DAY,0)     = 0
              ;                          
      END IF;
  
      IF (d_count &gt; 1) THEN /* we can support only connection of up to 1 pc in one service order/bundle limitation*/
        exit;
      END IF;
  
      d_count := d_count + 1;
  
    END LOOP;
  
    OPEN C1_OSO_PC_LIST_cv FOR
      SELECT d_periodic01 AS pc_name01, d_apply_day as pc_apply_day, d_rc_term_id      as pc_rc_term_id, d_offer_id as pc_offer_id
        FROM DUAL;
  
  END;
  </code></pre>