Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-15941

Explicit cursor FOR loop does not close the cursor

    Details

      Description

      I run this script:

      SET sql_mode=ORACLE;
      DELIMITER $$
      DECLARE
        CURSOR cur IS SELECT 1 AS a FROM DUAL;
        v INT;
      BEGIN
        FOR rec IN cur
        LOOP
          NULL;
        END LOOP;
        FETCH cur INTO v;
        CLOSE cur;
      END;
      $$
      DELIMITER ;
      

      It ends successfully. This is wrong. The FOR loop for an explicit cursor should close the cursor. In fact it does not seem to, so FETCH and CLOSE are still possible with this cursor after the loop.

      In Oracle, the cursor is in closed state after the loop. So attempts to use it with FETCH (or CLOSE) after the loop return an error:

      DECLARE
        CURSOR cur IS SELECT 1 AS a FROM DUAL;
        v INT;
      BEGIN
        FOR rec IN cur
        LOOP
          NULL;
        END LOOP;
        FETCH cur INTO v;
      END;
      /
      

      ERROR at line 1:
      ORA-01001: invalid cursor
      ORA-06512: at line 9
      

        Attachments

          Activity

            People

            • Assignee:
              bar Alexander Barkov
              Reporter:
              bar Alexander Barkov
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: