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

CURSOR declarations in PACKAGE BODY

Details

    • Server 12.1 dev sprint

    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.
          • Stalled

          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

            Ascending order - Click to sort in descending order
            bar Alexander Barkov created issue -
            bar Alexander Barkov made changes -
            Field Original Value New Value
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Description This script return a syntax error near 'CURSOR cur IS...':
            {code:sql}
            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;
            {code}
            {noformat}
            ERROR 1064 (42000): You have an error in your SQL syntax; ... 'CURSOR cur IS SELECT 1 AS c FROM DUAL..';
            {nofomat}

            If I run a similar scrip on Oracle, it works fine and returns "1" as expected:

            {code:sql}
            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;
            {code}
            {noformat}
            PKG.F1()
            1
            {noformat}
            		This script return a syntax error near 'CURSOR cur IS...':
            {code:sql}
            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;
            {code}
            {noformat}
            ERROR 1064 (42000): You have an error in your SQL syntax; ... 'CURSOR cur IS SELECT 1 AS c FROM DUAL..';
            {noformat}

            If I run a similar scrip on Oracle, it works fine and returns "1" as expected:

            {code:sql}
            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;
            {code}
            {noformat}
            PKG.F1()
            1
            {noformat}
            bar Alexander Barkov made changes -
            elenst Elena Stepanova made changes -
            Assignee Alexander Barkov [ bar ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Description This script return a syntax error near 'CURSOR cur IS...':
            {code:sql}
            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;
            {code}
            {noformat}
            ERROR 1064 (42000): You have an error in your SQL syntax; ... 'CURSOR cur IS SELECT 1 AS c FROM DUAL..';
            {noformat}

            If I run a similar scrip on Oracle, it works fine and returns "1" as expected:

            {code:sql}
            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;
            {code}
            {noformat}
            PKG.F1()
            1
            {noformat}
            		This script return a syntax error near 'CURSOR cur IS...':
            {code:sql}
            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;
            {code}
            {noformat}
            ERROR 1064 (42000): You have an error in your SQL syntax; ... 'CURSOR cur IS SELECT 1 AS c FROM DUAL..';
            {noformat}

            If I run a similar script in Oracle, it works fine and returns "1" as expected:

            {code:sql}
            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;
            {code}
            {noformat}
            PKG.F1()
            1
            {noformat}
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Fix Version/s 12.1 [ 29992 ]
            Fix Version/s 11.4 [ 29301 ]
            bar Alexander Barkov made changes -
            bar Alexander Barkov made changes -
            Affects Version/s 10.5 [ 23123 ]
            Affects Version/s 11.8 [ 29921 ]
            Issue Type Bug [ 1 ] New Feature [ 2 ]
            bar Alexander Barkov made changes -
            Summary Syntax error on a CURSOR..IS declaration in PACKAGE BODY CURSOR declarations in PACKAGE BODY
            serg Sergei Golubchik made changes -
            Sprint Server 12.1 dev sprint [ 793 ]
            bar Alexander Barkov made changes -
            Description This script return a syntax error near 'CURSOR cur IS...':
            {code:sql}
            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;
            {code}
            {noformat}
            ERROR 1064 (42000): You have an error in your SQL syntax; ... 'CURSOR cur IS SELECT 1 AS c FROM DUAL..';
            {noformat}

            If I run a similar script in Oracle, it works fine and returns "1" as expected:

            {code:sql}
            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;
            {code}
            {noformat}
            PKG.F1()
            1
            {noformat}
            		Under the scope of this task we'll add support for CURSOR declarations in CREATE PACKAGE BODY.

            Currently it's not supported. This script return a syntax error near 'CURSOR cur IS...':
            {code:sql}
            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;
            {code}
            {noformat}
            ERROR 1064 (42000): You have an error in your SQL syntax; ... 'CURSOR cur IS SELECT 1 AS c FROM DUAL..';
            {noformat}

            If I run a similar script in Oracle, it works fine and returns "1" as expected:

            {code:sql}
            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;
            {code}
            {noformat}
            PKG.F1()
            1
            {noformat}
            bar Alexander Barkov made changes -
            Description Under the scope of this task we'll add support for CURSOR declarations in CREATE PACKAGE BODY.

            Currently it's not supported. This script return a syntax error near 'CURSOR cur IS...':
            {code:sql}
            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;
            {code}
            {noformat}
            ERROR 1064 (42000): You have an error in your SQL syntax; ... 'CURSOR cur IS SELECT 1 AS c FROM DUAL..';
            {noformat}

            If I run a similar script in Oracle, it works fine and returns "1" as expected:

            {code:sql}
            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;
            {code}
            {noformat}
            PKG.F1()
            1
            {noformat}
            		Under the scope of this task we'll add support for CURSOR declarations in CREATE PACKAGE BODY.

            Currently it's not supported. This script return a syntax error near 'CURSOR cur IS...':
            {code:sql}
            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;
            {code}
            {noformat}
            ERROR 1064 (42000): You have an error in your SQL syntax; ... 'CURSOR cur IS SELECT 1 AS c FROM DUAL..';
            {noformat}

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

            {code:sql}
            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;
            {code}
            {noformat}
            PKG.F1()
            1
            {noformat}
            bar Alexander Barkov made changes -
            Description Under the scope of this task we'll add support for CURSOR declarations in CREATE PACKAGE BODY.

            Currently it's not supported. This script return a syntax error near 'CURSOR cur IS...':
            {code:sql}
            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;
            {code}
            {noformat}
            ERROR 1064 (42000): You have an error in your SQL syntax; ... 'CURSOR cur IS SELECT 1 AS c FROM DUAL..';
            {noformat}

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

            {code:sql}
            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;
            {code}
            {noformat}
            PKG.F1()
            1
            {noformat}
            		Under the scope of this task we'll add support for CURSOR declarations in CREATE PACKAGE BODY.

            Currently CURSORs are not supported supported in CREATE PACKAGE BODY. This script return a syntax error near 'CURSOR cur IS...':
            {code:sql}
            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;
            {code}
            {noformat}
            ERROR 1064 (42000): You have an error in your SQL syntax; ... 'CURSOR cur IS SELECT 1 AS c FROM DUAL..';
            {noformat}

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

            {code:sql}
            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;
            {code}
            {noformat}
            PKG.F1()
            1
            {noformat}
            bar Alexander Barkov made changes -
            Description Under the scope of this task we'll add support for CURSOR declarations in CREATE PACKAGE BODY.

            Currently CURSORs are not supported supported in CREATE PACKAGE BODY. This script return a syntax error near 'CURSOR cur IS...':
            {code:sql}
            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;
            {code}
            {noformat}
            ERROR 1064 (42000): You have an error in your SQL syntax; ... 'CURSOR cur IS SELECT 1 AS c FROM DUAL..';
            {noformat}

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

            {code:sql}
            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;
            {code}
            {noformat}
            PKG.F1()
            1
            {noformat}
            		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 return a syntax error near 'CURSOR cur IS...':
            {code:sql}
            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;
            {code}
            {noformat}
            ERROR 1064 (42000): You have an error in your SQL syntax; ... 'CURSOR cur IS SELECT 1 AS c FROM DUAL..';
            {noformat}

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

            {code:sql}
            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;
            {code}
            {noformat}
            PKG.F1()
            1
            {noformat}
            bar Alexander Barkov made changes -
            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 return a syntax error near 'CURSOR cur IS...':
            {code:sql}
            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;
            {code}
            {noformat}
            ERROR 1064 (42000): You have an error in your SQL syntax; ... 'CURSOR cur IS SELECT 1 AS c FROM DUAL..';
            {noformat}

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

            {code:sql}
            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;
            {code}
            {noformat}
            PKG.F1()
            1
            {noformat}
            		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...':
            {code:sql}
            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;
            {code}
            {noformat}
            ERROR 1064 (42000): You have an error in your SQL syntax; ... 'CURSOR cur IS SELECT 1 AS c FROM DUAL..';
            {noformat}

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

            {code:sql}
            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;
            {code}
            {noformat}
            PKG.F1()
            1
            {noformat}
            bar Alexander Barkov made changes -
            Priority Major [ 3 ] Critical [ 2 ]
            bar Alexander Barkov made changes -
            Status Open [ 1 ] In Progress [ 3 ]

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.