CODEWITHSUNDEEP
oracle-databaseplsql
Venkat
Venkat

Reputation: 1

ORA-01001: invalid cursor in PL SQL

CURSOR asset_cursor IS 
    
       select File_DATA from RSI_ASSET_DETAILS 
       where ACCOUNT_TYPE= 'COST' Order By SEQ ASC; 
       TYPE Cost_data IS TABLE OF asset_cursor%rowtype;
       cost_data_tbl cost_data;


  OPEN asset_cursor;
       LOOP
       
       FETCH  asset_Cursor  BULK COLLECT INTO cost_data_tbl LIMIT ln_limit;
       EXIT WHEN cost_data_tbl.COUNT = 0;
          
         FOR IDX IN 1 .. cost_data_tbl.COUNT                                         
        --FOR IDX IN cost_data_tbl.FIRST..cost_data_tbl.LAST
    LOOP
        UTL_FILE.PUT_LINE(lv_COST_File,cost_data_tbl(IDX).File_DATA);
        --UTL_FILE.PUT_LINE
       END LOOP;                           
       CLOSE asset_cursor;                         
        
    END LOOP;

I wrote like above. with the above query iam getting Invalid cursor

want to know what mistake i did?

Upvotes: 0

Views: 356

Answers (1)

Littlefoot
Littlefoot

Reputation: 142705

It is indeed invalid, because you closed it within the loop and then tried to fetch again but - it is close, you can't. Move close out of the loop.

For example (and simple sample data):

SQL> SELECT * FROM rsi_asset_details;

FIL ACCO
--- ----
abc COST
def COST

PL/SQL code:

SQL> DECLARE
  2     CURSOR asset_cursor IS
  3        SELECT file_data
  4          FROM rsi_asset_details
  5         WHERE account_type = 'COST';
  6
  7     TYPE cost_data IS TABLE OF asset_cursor%ROWTYPE;
  8
  9     cost_data_tbl  cost_data;
 10     ln_limit       NUMBER := 100;
 11  BEGIN
 12     OPEN asset_cursor;
 13
 14     LOOP
 15        FETCH asset_cursor BULK COLLECT INTO cost_data_tbl LIMIT ln_limit;
 16        EXIT WHEN cost_data_tbl.COUNT = 0;
 17
 18        FOR idx IN 1 .. cost_data_tbl.COUNT
 19        LOOP
 20           DBMS_OUTPUT.put_line (cost_data_tbl (idx).file_data);
 21        END LOOP;
 22     END LOOP;
 23
 24     CLOSE asset_cursor;          --> should be out of the loop
 25  END;
 26  /
abc
def

PL/SQL procedure successfully completed.

SQL>

Upvotes: 3

Related Questions