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

Explicit cursor FOR loop does not close the cursor

    XMLWordPrintable

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

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

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.