SQL Error Cursor not open

[Former Member]

I run a Stored Procedure. After a while i cancel the execution and try to start it again. After that I always got a error message.

SQLCODE -180 Constant SQLE_CURSOR_NOT_OPEN SQLSTATE 24501 Sybase error code 559 ODBC 2 State 34000 ODBC 3 State 34000

I can't start the procedure anymore ????

[Former Member]

Here is the complete code.

ALTER PROCEDURE "PBS"."PBS_KRH_YBWIIRW"()
begin
  declare err_notfound exception for sqlstate value '02000';
  declare V_IK_KRH numeric(9);
  declare V_GELTUNGSBEREICH numeric(1);
  declare V_KAL_JAHR_MONAT numeric(6);
  declare V_WERTTYP varchar(4);
  declare v_max_typ varchar(4);
  declare YBWIIRW dynamic scroll cursor for select IK_KRH,GELTUNGSBEREICH,KAL_JAHR_MONAT from
      PBS.PBS_KRH_YBWIIRW
      group by IK_KRH,GELTUNGSBEREICH,KAL_JAHR_MONAT;
  open YBWIIRW;
  YBWIIRWLoop: loop
    fetch next YBWIIRW into V_IK_KRH,
      V_GELTUNGSBEREICH,V_KAL_JAHR_MONAT;
    if sqlcode <> 0 then
      leave YBWIIRWLoop
    end if;
    select max(WERTTYP) into v_max_typ 
           from "PBS"."PBS_KRH_YBWIIRW" 
           where IK_KRH = V_IK_KRH
           and GELTUNGSBEREICH = V_GELTUNGSBEREICH 
           and KAL_JAHR_MONAT = V_KAL_JAHR_MONAT;
    update "PBS"."PBS_KRH_YBWIIRW" a set a.PLANWERT= b.planwert 
           from "DBA"."GDB_S_KRH_PLANWERTE" b 
           where a."IK_KRH" = V_IK_KRH
           and a."GELTUNGSBEREICH" = V_GELTUNGSBEREICH
           and a."KAL_JAHR_MONAT" = V_KAL_JAHR_MONAT
           and a.WERTTYP =  v_max_typ;
commit;
  end loop YBWIIRWLoop;
  close YBWIIRW;
end

[VolkerBarth · Accepted solution]

SQLCODE -180 (SQLE_CURSOR_NOT_OPEN) means "You attempted to use a cursor that has not been opened.".

When you cancel the procedure's execution, it raises an error (SQLCODE -299, "Statement interrupted by user"). Without explicit error handling, I assume that the procedure just stops within its control flow -cf. the following quote from the SA 12 docs.

Generally, if a SQL statement in a procedure or trigger fails, the procedure or trigger stops executing and control returns to the application program with an appropriate setting for the SQLSTATE and SQLCODE values.

However, it should close its cursors on exiting, and should be able to be restarted afterwards (or from a parallel connection).

Here is a sample Watcom-SQL procedure for the SA 12 demo database that scrolls through a cursor and pauses 3 seconds after each row. When you cancel the execution, you can still restart it without problems:

CREATE OR REPLACE PROCEDURE STP_Test()
begin
   DECLARE err_notfound EXCEPTION FOR SQLSTATE VALUE '02000';
   DECLARE cur_employee CURSOR FOR
         SELECT Surname
         FROM Employees;
     DECLARE name CHAR(40);
     OPEN cur_employee;
  lp: LOOP
    FETCH NEXT cur_employee INTO name;
    MESSAGE 'Surname: ' || name TO CLIENT;
    WAITFOR DELAY '00:00:03';
    IF SQLCODE <> 0 THEN LEAVE lp END IF;
  END LOOP;
  CLOSE cur_employee;
  MESSAGE 'STP_Test finished' TO CLIENT;
end;

CALL STP_Test(); -- cancel after some seconds from DBISQL
CALL STP_Test(); -- run again, should work

Given this works as expected, I would conclude that there's a problem with the logic in the cursor loop itself inside your procedure (why would you cancel it anyway?).

Have you tried to use an UPDATE without cursor (as suggested in the other FAQ)?

FWIW, v12 has introduced the sa_list_cursors() system procedure. That might help to test whther the cursor remains declared and open after the procedure's execution:

CALL sa_list_cursors();

---

EDIT: The real problem might be due to cursor behaviour on commit:

With the default setting of option close_on_endtrans (ON), the COMMIT in your procedure will close the cursor. That surely explains the error message - and should even appear for a "normal" (i.e. uncancelled) execution. You might use WITH HOLD in the cursor declaration or defer the COMMIT after the CLOSE statement...

[Former Member · Accepted solution]

You're right Volker. The commit occurs the problem. I put the Commit after the close and the Procedure can be executed.

I want to answer you in the other FAQ, now i can handle it in this one.

I think the Update with Max(value) is not for my special Case.

I will set a planwert as a new field in the dataset "PBS"."PBS_KRH_YBWIIRW".

There are a lot of Werttyp Values in the this table but only one should get this update.

It's difficult to explain. 🐵

And thanks for the code, it helps me out to unterstand Sybase Syntac a little bit more.

I'm wirting some Procedures in PL/SQL, but the Syntax is different to T-SQL.

[VolkerBarth · Accepted solution]

Thansk for the feedback, and in case the answer was helpful, you might consider this FAQ...:)