bschave2
asked on
Why do I get the error invalid cursor?
I am getting the error: invalid cursor when running the code below. Am I doing this wrong
for resupply_Cursor in (SELECT DISTINCT TO_CHAR(availability_date, 'mm/dd/yyyy') availability_date
FROM v_call_option_avail
WHERE availability_date >= to_date(v_Begin_Date, 'mm/dd/yyyy')
AND contract_id = v_contract_id
AND call_option_type_id = v_call_option_type_id)
loop
--getting the number of hours to save for each date in the cursor.
if resupply_cursor.availability_date = v_DST_Date THEN
hours := 23;
elsif resupply_cursor.availability_date = v_ST_Date THEN
hours := 25;
else
hours := 24;
end if;
INSERT INTO V_CALL_OPTION_AVAIL (
CONTRACT_ID,
AVAILABILITY_DATE,
HOUR,
CALL_OPTION_TYPE_ID,
MW,
TRADES_DEAL_NO,
PRICE,
ENTRY_DATE,
ENTRY_USER,
SUPERSEDED_DATE) (
SELECT
NVL(a.contract_id, v_contract_id) contract_id,
NVL(a.availability_date, to_date(resupply_cursor.availability_date,'mm/dd/yyyy')) availability_date,
h.hour,
NVL(a.call_option_type_id, v_call_option_type_id) call_option_type_id,
NVL(a.mw, 0) MW,
NVL(a.trades_deal_no, v_Trades_Deal_No) POR_ID,
NVL(a.price, to_char(TO_NUMBER(0.00), '99D99')) PRICE,
NVL(a.entry_date, null),
NVL(a.entry_user, v_Entry_User),
NVL(a.superseded_date, null)
FROM (SELECT level AS hour FROM dual CONNECT BY level <= 24
) h
LEFT JOIN v_call_option_avail a
ON a.availability_date >= (TRUNC(sysdate) + (h.hour - 1)/24)
AND a.availability_date <= (TRUNC(sysdate) + (h.hour)/24)
AND a.call_option_type_id = v_call_option_type_id
AND contract_id = v_contract_id);
end loop;
close resupply_Cursor;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.