Hi
CREATE OR REPLACE PROCEDURE APPOINTMENT_DATE ( p_start_date IN DATE ,
p_end_date IN DATE)
AS
v_count NUMBER;
v_date DATE;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM PR_OPS_APPOINT_SETS
WHERE APPOINT_DATE BETWEEN TRUNC(p_start_date) AND TRUNC(p_end_date);
IF v_count = 0 THEN
INSERT INTO PR_OPS_APPOINT_SETS (APPOINT_DATE)
WITH
DATE_RANGES AS
(
SELECT TO_DATE( TO_CHAR ( p_start_date + LEVEL - 1 , 'MM/DD/RRRR' ) ,'MM/DD/RRRR')AS dates
FROM DUAL
CONNECT BY LEVEL <= p_end_date - p_start_date + 1
)
SELECT dates
FROM DATE_RANGES;
COMMIT;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('dates ' || p_start_date || ' does not have a date for CLINIC. No update.');
WHEN OTHERS THEN raise;
END;
/