Question

Cursor error - cursor already open

  • 1 March 2023
  • 3 replies
  • 470 views
S
Rank
Userlevel 1
Badge +2

Hi,

 

I keep getting the error: ORA-06511: PL/SQL: cursor already open

when executing below, can someone please tell me why:- 

DECLARE
    info_ VARCHAR2(2000);
    attr_ VARCHAR2(2000);
    
    newrec_     identity_invoice_info%ROWTYPE;
    
    automatic_pay_auth_flag_db  VARCHAR2(1):= '1';
    
    CURSOR get_supp IS
    SELECT * FROM IDENTITY_INVOICE_INFO;
       
BEGIN

  FOR rec_ IN get_supp LOOP
  
   OPEN get_supp;
   FETCH get_supp INTO rec_;
   EXIT WHEN get_supp%NOTFOUND;
   CLOSE get_supp;
    
            Client_SYS.Clear_Attr(attr_);
       
            Client_SYS.Add_To_Attr('AUTOMATIC_PAY_AUTH_FLAG_DB',automatic_pay_auth_flag_db,attr_);
            
            IDENTITY_INVOICE_INFO_API.Modify__(info_,rec_.objid,rec_.objversion,attr_,'DO');
    
    END LOOP;

    COMMIT;

END;

3 replies

S
Rank
Userlevel 1
Badge +2

Nevermind, I fixed it by using an explicit cursor:

 

DECLARE
    info_ VARCHAR2(2000);
    attr_ VARCHAR2(2000);
    
    automatic_pay_auth_flag_db  VARCHAR2(1):= '1';
    
    CURSOR get_supp IS
    SELECT * FROM IDENTITY_INVOICE_INFO where automatic_pay_auth_flag_db != '1';
    
    rec_ get_supp%ROWTYPE;
       
BEGIN

 OPEN get_supp;
 LOOP
   FETCH get_supp INTO rec_;
   EXIT WHEN get_supp%NOTFOUND;
       
            Client_SYS.Clear_Attr(attr_);
            Client_SYS.Add_To_Attr('AUTOMATIC_PAY_AUTH_FLAG_DB',automatic_pay_auth_flag_db,attr_);
            
            IDENTITY_INVOICE_INFO_API.Modify__(info_,rec_.objid,rec_.objversion,attr_,'DO');
   END LOOP;
   CLOSE get_supp;
   
   COMMIT;

END;

InfFilipV
Rank
Userlevel 6
Badge +12

Hi,
problem was, that you used same cursor inside loop over same cursor.

It is unfortunately not allowed, even if you want to open it with different arguments.

In your case it make no sense to do it like that - because that loop body will never be executed when there is no record. So there is no reason to explicitly exit loop with that construction.
 

FOR rec_ IN get_supp LOOP
   Client_SYS.Clear_Attr(attr_);
   Client_SYS.Add_To_Attr('AUTOMATIC_PAY_AUTH_FLAG_DB',automatic_pay_auth_flag_db,attr_);

   IDENTITY_INVOICE_INFO_API.Modify__(info_,rec_.objid,rec_.objversion,attr_,'DO');
END LOOP;

is equal to

OPEN get_supp;
LOOP
   FETCH get_supp INTO rec_;
   EXIT WHEN get_supp%NOTFOUND;

   Client_SYS.Clear_Attr(attr_);
   Client_SYS.Add_To_Attr('AUTOMATIC_PAY_AUTH_FLAG_DB',automatic_pay_auth_flag_db,attr_);

   IDENTITY_INVOICE_INFO_API.Modify__(info_,rec_.objid,rec_.objversion,attr_,'DO');
END LOOP;
CLOSE get_supp;


BR

Filip | Developer | InfoConsulting.com
T
Rank
Userlevel 7
Badge +19

One more thing, IDENTITY_INVOICE_INFO contains data for both customers and suppliers.

I’m not sure about what you are trying to update but if you want to limit it to any of those types you can add a condition to field PARTY_TYPE_DB to be equal to CUSTOMER or SUPPLIER.

Reply


Terms & ConditionsCookie settings