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

CURSOR declarations in PACKAGE BODY

Details

    • Q2/2025 Development

    Description

      Under the scope of this task we'll add support for CURSOR declarations in CREATE PACKAGE BODY.

      Currently CURSORs are not supported in CREATE PACKAGE BODY. This script returns a syntax error near 'CURSOR cur IS...':

      SET sql_mode=ORACLE;
      		 
      DROP PACKAGE pkg;
      		 
      DELIMITER /
      		 
      CREATE PACKAGE pkg AS
      		 
        FUNCTION f1 RETURN INT;
      		 
      END;
      		 
      /
      		 
      CREATE PACKAGE BODY pkg AS
      		 
        vc INT := 0;
      		 
        CURSOR cur IS SELECT 1 AS c FROM DUAL; -- Error is raised incorectly
      		 
        FUNCTION f1 RETURN INT AS
      		 
        BEGIN
      		 
          OPEN cur;
      		 
          FETCH cur INTO vc;
      		 
          CLOSE cur;
      		 
          RETURN vc;
      		 
        END;
      		 
      END;
      		 
      /
      		 
      DELIMITER ;
      		 
      SELECT pkg.f1() FROM DUAL;


      ERROR 1064 (42000): You have an error in your SQL syntax; ... 'CURSOR cur IS SELECT 1 AS c FROM DUAL..';

      A similar script in Oracle works fine and returns "1":

      DROP PACKAGE pkg;
      		 
      CREATE PACKAGE pkg AS
      		 
        FUNCTION f1 RETURN INT;
      		 
      END;
      		 
      /
      		 
      CREATE PACKAGE BODY pkg AS
      		 
        vc INT := 0;
      		 
        CURSOR cur IS SELECT 1 AS c FROM DUAL;
      		 
        FUNCTION f1 RETURN INT AS
      		 
        BEGIN
      		 
          OPEN cur;
      		 
          FETCH cur INTO vc;
      		 
          CLOSE cur;
      		 
          RETURN vc;
      		 
        END;
      		 
      END;
      		 
      /
      		 
      SELECT pkg.f1() FROM DUAL;


      PKG.F1()
      		 
      1

      Attachments

        Issue Links

          blocks

          New Feature - A new feature of the product, which has yet to be developed. MDEV-13139 CREATE PACKAGE: package-wide declarations

          • Critical - Crashes, loss of data, severe memory leak.
          • Stalled
          is duplicated by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36081 CURSOR is not allowed in the top level block in PACKAGE BODY

          • Major - Major loss of function.
          • Closed
          relates to

          New Feature - A new feature of the product, which has yet to be developed. MDEV-10152 Add support for TYPE .. IS REF CURSOR

          • Major - Major loss of function.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-20034 Add support for the pre-defined weak SYS_REFCURSOR

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36047 Package body variables are not allowed as FETCH targets

          • Major - Major loss of function.
          • Closed

          Activity

            bar Alexander Barkov added a comment -

            Hi Sergei,

            Please review a patch for:

            https://github.com/MariaDB/server/commit/39f1b4c93e741702ce1e396336d65c9af97c318e

            It's for 11.4. But will go into bb-11.4-compatibility and "main".

            bar Alexander Barkov added a comment - Hi Sergei, Please review a patch for: https://github.com/MariaDB/server/commit/39f1b4c93e741702ce1e396336d65c9af97c318e It's for 11.4. But will go into bb-11.4-compatibility and "main".

            People

              serg Sergei Golubchik
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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