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

Add support for TYPE .. IS REF CURSOR

    XMLWordPrintable

Details

    • Q4/2025 Server Development

    Description

      Implement TYPE .. REF CURSOR.

      REF CURSOR is a flexible way to return query results/record sets from stored procedures and functions.

      https://docs.oracle.com/database/121/LNPLS/cursor_variable.htm#LNPLS01312

      https://oracle-base.com/articles/misc/using-ref-cursors-to-return-recordsets

      Under terms of this task we'll implement strong and weak cursor types that need a TYPE definition:

      DECLARE
      		 
        TYPE weak_cursor IS REF CURSOR;                              -- weak type
      		 
        TYPE storong_cursor IS REF CURSOR RETURN employees%ROWTYPE;  -- strong type

      The pre-defined SYS_REFCURSOR is out of scope of this task and we'll be implemented under terms of MDEV-20034.

      Oracle grammar for ref cursor type declaration

      ref_cursor_type_definition ::=
      		 
         TYPE type IS REF CURSOR [ RETURN ref_cursor_return_type ] ;
      		 
       
      		 
      ref_cursor_return_type ::=
      		 
          record_type
      		 
        | record_variable%TYPE
      		 
        | table_or_view%ROWTYPE
      		 
        | cursor%ROWTYPE
      		 
        | cursor_variable%ROWTYPE
      		 
        | ref_cursor_type /*Won't be supported*/

      Let's support the entire Oracle's grammar (almost).

      RETURN record_type

      Explicit records should be supported

      DROP TABLE t1;
      		 
      CREATE TABLE t1 (a INT,b VARCHAR(10));
      		 
      INSERT INTO t1 VALUES (10,'b10');
      		 
      DECLARE
      		 
        TYPE rec_t IS RECORD (a INT, b VARCHAR(19));
      		 
        TYPE cur_rec_t IS REF CURSOR RETURN rec_t;
      		 
        c0 cur_rec_t;
      		 
        r0 rec_t;
      		 
      BEGIN
      		 
        OPEN c0 FOR SELECT * FROM t1;
      		 
        LOOP
      		 
          FETCH c0 INTO r0;
      		 
          EXIT WHEN c0%NOTFOUND;
      		 
          DBMS_OUTPUT.PUT_LINE(r0.a || ' ' || r0.b);
      		 
        END LOOP;
      		 
      END;
      		 
      /

      RETURN record_type%ROWTYPE with %TYPE inside RECORD

      Records with anchored data type fields should be supported:

      DROP TABLE t1;
      		 
      CREATE TABLE t1 (a INT,b VARCHAR(10));
      		 
      INSERT INTO t1 VALUES (10,'b10');
      		 
      DECLARE
      		 
        TYPE rec_t IS RECORD (a t1.a%TYPE, b t1.b%TYPE);
      		 
        TYPE cur_rec_t IS REF CURSOR RETURN rec_t;
      		 
        r0 rec_t;
      		 
        c0 cur_rec_t;
      		 
      BEGIN
      		 
        OPEN c0 FOR SELECT * FROM t1;
      		 
        LOOP
      		 
          FETCH c0 INTO r0;
      		 
          EXIT WHEN c0%NOTFOUND;
      		 
          DBMS_OUTPUT.PUT_LINE(r0.a || ' ' || r0.b);
      		 
        END LOOP;
      		 
      END;
      		 
      /

      RETURN record_variable%TYPE

      CREATE OR REPLACE PROCEDURE p1 IS
      		 
        TYPE rec0_t IS RECORD (a INT, b VARCHAR(10));
      		 
        v0 rec0_t;
      		 
        TYPE cur0_t IS REF CURSOR RETURN v0%TYPE;
      		 
        c0 cur0_t;
      		 
      BEGIN
      		 
        OPEN c0 FOR SELECT 1,2 FROM DUAL;
      		 
        FETCH c0 INTO v0;
      		 
        DBMS_OUTPUT.PUT_LINE(v0.a || v0.b);
      		 
        CLOSE c0;
      		 
      END;
      		 
      /
      		 
      CALL p1;

      RETURN cursor%ROWTYPE

      CREATE OR REPLACE PROCEDURE p1 IS
      		 
        CURSOR cs IS SELECT 1 AS a,2 AS b FROM DUAL;
      		 
        TYPE curs_t IS REF CURSOR RETURN cs%ROWTYPE;
      		 
        c0 curs_t;
      		 
        v0 cs%ROWTYPE;
      		 
      BEGIN
      		 
        OPEN c0 FOR SELECT 1,2 FROM DUAL;
      		 
        FETCH c0 INTO v0;
      		 
        DBMS_OUTPUT.PUT_LINE(v0.a || v0.b);
      		 
        CLOSE c0;
      		 
      END;
      		 
      /
      		 
      CALL p1;

      RETURN table_or_view%ROWTYPE

      Anchored table type cursors should be supported:

      DROP TABLE t1;
      		 
      CREATE TABLE t1 (a INT,b VARCHAR(10));
      		 
      INSERT INTO t1 VALUES (10,'b10');
      		 
      DECLARE
      		 
        TYPE cur_rec_t IS REF CURSOR RETURN t1%ROWTYPE;
      		 
        c0 cur_rec_t;
      		 
        r0 t1%ROWTYPE;
      		 
      BEGIN
      		 
        OPEN c0 FOR SELECT * FROM t1;
      		 
        LOOP
      		 
          FETCH c0 INTO r0;
      		 
          EXIT WHEN c0%NOTFOUND;
      		 
          DBMS_OUTPUT.PUT_LINE(r0.a || ' ' || r0.b);
      		 
        END LOOP;
      		 
      END;
      		 
      /

      RETURN cursor_variable%ROWTYPE

      A cursor variable whose type is declared with %ROWTYPE should itself work in %ROWTYPE

      DROP TABLE t1;
      		 
      CREATE TABLE t1 (a INT,b VARCHAR(10));
      		 
      INSERT INTO t1 VALUES (10,'b10');
      		 
      DECLARE
      		 
        TYPE cur1_t IS REF CURSOR RETURN t1%ROWTYPE;
      		 
        c1 cur1_t;
      		 
        TYPE cur0_t IS REF CURSOR RETURN c1%ROWTYPE;
      		 
        c0 cur0_t;
      		 
        r0 c0%ROWTYPE;
      		 
      BEGIN
      		 
        OPEN c0 FOR SELECT * FROM t1;
      		 
        LOOP
      		 
          FETCH c0 INTO r0;
      		 
          EXIT WHEN c0%NOTFOUND;
      		 
          DBMS_OUTPUT.PUT_LINE(r0.a || ' ' || r0.b);
      		 
        END LOOP;
      		 
      END;
      		 
      /

      RETURN ref_cursor_type – won't be supported

      Oracle docs says that ref_cursor_type is supported as an option for the RETURN clause (where ref_cursor_type is the name of a user-defined type that was defined with the data type specifier REF CURSOR). However it does not work. Possibly this is a mistake in the documentation. The following script returns an error during CREATE time:

      CREATE OR REPLACE PROCEDURE p1 IS
      		 
        TYPE rec0_t IS RECORD (a INT, b VARCHAR(10));
      		 
        TYPE cur0_t IS REF CURSOR RETURN rec0_t;
      		 
        TYPE cur1_t IS REF CURSOR RETURN cur0_t;
      		 
      BEGIN
      		 
        NULL;
      		 
      END;
      		 
      /


      invalid cursor return type; 'CUR0_T' must be a record type

      Attachments

        Issue Links

          blocks

          New Feature - A new feature of the product, which has yet to be developed. MDEV-34323 Oracle compatibility project 3

          • Major - Major loss of function.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-34560 Qualified package data types

          • Major - Major loss of function.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. MDEV-34569 Oracle compatibility project 4

          • Major - Major loss of function.
          • Open
          is blocked by

          Task - A task that needs to be done. MDEV-38109 Refactor sp_add_instr_fetch_cursor to get the target list argument

          • Critical - Crashes, loss of data, severe memory leak.
          • In Progress

          Task - A task that needs to be done. MDEV-38161 Refactor Type_extra_attributes: change void* for generic attributes to a stricter type

          • Critical - Crashes, loss of data, severe memory leak.
          • Open

          Task - A task that needs to be done. MDEV-38162 Refactoring: Change sp_type_def_composite2::m_def from Spvar_definition* to Spvar_definition

          • Critical - Crashes, loss of data, severe memory leak.
          • Open
          is part of

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-11070 Providing compatibility to other databases - Phase 2

          • Major - Major loss of function.
          • Open

          Task - A task that needs to be done. MDEV-35973 Oracle Compatibility Project 1 for 2025

          • Critical - Crashes, loss of data, severe memory leak.
          • Stalled
          relates to

          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

          New Feature - A new feature of the product, which has yet to be developed. MDEV-33830 Support for cursors on prepared statements

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

          New Feature - A new feature of the product, which has yet to be developed. MDEV-35979 Oracle: Cursor expressions

          • Major - Major loss of function.
          • Open

          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

          New Feature - A new feature of the product, which has yet to be developed. MDEV-36053 CURSOR declarations in PACKAGE BODY

          • Critical - Crashes, loss of data, severe memory leak.
          • In Review
          links to

          Web Link Oracle docs: Cursor type definition

          Activity

            People

              bar Alexander Barkov
              monty Michael Widenius
              Votes:
              6 Vote for this issue
              Watchers:
              11 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.