CODEWITHSUNDEEP
sqloracle-database
shaiksha
shaiksha

Reputation: 1003

how to handle exception when no rows returned in sql

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.

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.

This is the procedure:

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 > 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;

Upvotes: 1

Views: 7513

Answers (1)

kevinskio
kevinskio

Reputation: 4551

You can use this Oracle exception to catch this

EXCEPTION
WHEN NO_DATA_FOUND THEN
--do something here
END;

You can put a BEGIN EXCEPTION END inside your cursor loop so the loop will continue even if no data is found for one record or just use it at the end of the procedure in which case the loop will exit and processing will stop when the first no data is found.

Upvotes: 2

Related Questions