Untitled

CREATE OR REPLACE PACKAGE pkg_lkp_regions_tbl
AS
  PROCEDURE check_ri (
    p_country_id syn_regions_tbl.country_id%TYPE,
    p_region_cd  syn_regions_tbl.region_cd%TYPE
  );
END pkg_lkp_regions_tbl;
/

CREATE OR REPLACE PACKAGE BODY pkg_lkp_regions_tbl
AS
  PROCEDURE check_ri (
    p_country_id syn_regions_tbl.country_id%TYPE,
    p_region_cd  syn_regions_tbl.region_cd%TYPE
  )
  AS
    v_country_id syn_regions_tbl.country_id%TYPE;
    v_region_cd  syn_regions_tbl.region_cd%TYPE;
  BEGIN
    IF (p_country_id IS NOT NULL AND p_region_cd IS NOT NULL) THEN
      BEGIN
        SELECT country_id, region_cd
        INTO v_country_id, v_region_cd
        FROM syn_regions_tbl
        WHERE country_id = p_country_id
          AND region_cd = p_region_cd;
      EXCEPTION
        WHEN no_data_found THEN
          RAISE_APPLICATION_ERROR(
            -20999, 
            'Composite key (country_id = ' || p_country_id || 
            ', region_cd = ' || p_region_cd || ') not found!'
          );
      END;
    END IF;
  END check_ri;
END pkg_lkp_regions_tbl;
/