• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

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;

Open in new window

0
bschave2
Asked:
bschave2
1 Solution
 
sdstuberCommented:
you don't close FOR loop cursors,  the FOR-construct itself opens and closes them


remove this line...
close resupply_Cursor;
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now