This has to be a silly programming error...but at this point I can't see it.
I have 2 cursors. Cursor 1 selects a number and 2 dates (values in the Table are in the format mm/dd/yyyy for both the columns).
I pass these values to Cursor 2.
v_promotion_id promotion.promotion_id%TYPE;
v_promotion_start_date promotion.start_date%TYPE;
v_promotion_end_date promotion.end_date%TYPE;
CURSOR c_promotion_list IS
(SELECT DISTINCT p.promotion_id,
p.start_date - 5,
p.end_date + 5
FROM promotion p,
promotion_list pl
WHERE p.promotion_id = pl.promotion_id
AND TRIM (pl.source_code) IS NOT NULL
AND SYSDATE BETWEEN p.start_date AND p.end_date + 5);
CURSOR c_trans_date_list (
p_promotion_id promotion.promotion_id%TYPE,
p_promotion_start_date promotion.start_date%TYPE,
p_promotion_end_date promotion.end_date%TYPE
) IS
SELECT DISTINCT th.transaction_date
FROM transaction_header th,
transaction_detail td,
transaction_coupon tc
WHERE th.transaction_id = td.transaction_id
AND td.transaction_id = tc.transaction_id
AND td.transaction_line_no = tc.transaction_line_no
AND th.transaction_date BETWEEN TO_DATE (p_promotion_start_date - 5, 'mm/dd/yyyy') AND TO_DATE (p_promotion_end_date, 'mm/dd/yyyy')
AND tc.coupon_code = TO_CHAR (p_promotion_id);
OPEN c_promotion_list;
LOOP
FETCH c_promotion_list
INTO v_promotion_id,
v_promotion_start_date,
v_promotion_end_date;
EXIT WHEN c_promotion_list%NOTFOUND;
OPEN c_trans_date_list (TO_CHAR (v_promotion_id), TO_DATE (v_promotion_start_date, 'mm/dd/yyyy'), TO_DATE (v_promotion_end_date, 'mm/dd/yyyy'));
LOOP
FETCH c_trans_date_list
INTO v_date;
EXIT WHEN c_trans_date_list%NOTFOUND;
I am getting this error: ORA-01843: not a valid month
Initially I was passing just the variables without To_date. But I got the same error.
Please help!
