Untitled

CREATE OR REPLACE PACKAGE pkg_lkp_regions_tbl
AS
  PROCEDURE check_ri (
    p_country_id syn_regions_tbl.country_id%TYPE,
    p_region_id syn_regions_tbl.region_id%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_id syn_regions_tbl.region_id%TYPE
  )
  AS
    v_count NUMBER;
  BEGIN
    -- Check if both country_id and region_id are provided
    IF p_country_id IS NOT NULL AND p_region_id IS NOT NULL THEN
      BEGIN
        -- Validate the composite key in the table
        SELECT COUNT(*)
        INTO v_count
        FROM syn_regions_tbl
        WHERE country_id = p_country_id
          AND region_id = p_region_id;

        -- If no match is found, raise an exception
        IF v_count = 0 THEN
          RAISE_APPLICATION_ERROR(-20999, 'Composite key (country_id: ' || p_country_id || ', region_id: ' || p_region_id || ') not found!');
        END IF;

      EXCEPTION
        WHEN OTHERS THEN
          RAISE; -- Propagate any unexpected errors
      END;
    ELSE
      RAISE_APPLICATION_ERROR(-20998, 'Both country_id and region_id must be provided!');
    END IF;
  END check_ri;
END pkg_lkp_regions_tbl;
/