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

Add support for the pre-defined weak SYS_REFCURSOR

Details

    Description

      CREATE OR REPLACE FUNCTION|PROCEDURE does not support RETURN REFCURSOR|SYS_REFCURSOR

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

      Under terms of this task we'll add support for weak cursor variables of the pre-defined SYS_REFCURSOR type.

      Example:

      DECLARE
      		 
        c SYS_REFCURSOR;
      		 
        a INT;
      		 
      BEGIN
      		 
        OPEN c FOR SELECT 1 FROM DUAL;
      		 
        FETCH c INTO a;
      		 
        CLOSE c;
      		 
        dbms_output.put_line(a); 
      END;
      		 
      /

      It will be possible to return a cursor in an OUT stored procedure parameter or as a function RETURN value:

      CREATE TABLE t1 (a INT);
      		 
      INSERT INTO t1 VALUES (10),(20);
      		 
       
      		 
      CREATE FUNCTION f1 RETURN SYS_REFCURSOR AS
      		 
        c SYS_REFCURSOR;
      		 
      BEGIN
      		 
        OPEN c FOR SELECT a FROM t1 ORDER BY a;
      		 
        RETURN c;
      		 
      END;
      		 
      /
      		 
       
      		 
      CREATE PROCEDURE p1 AS
      		 
        a INT;
      		 
        c SYS_REFCURSOR DEFAULT f1();
      		 
      BEGIN
      		 
        LOOP
      		 
          FETCH c INTO a;
      		 
          EXIT WHEN c%NOTFOUND;
      		 
          dbms_output.put_line(a);
      		 
        END LOOP;
      		 
        CLOSE c;
      		 
      END;
      		 
      /
      		 
       
      		 
      CALL p1();


      10
      		 
      20

      Features out of scope of this task

      • Cursor TYPE definitions (even weak ones) we'll be implemented under terms of a separate task MDEV-10152: 

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

      • Dynamic SQL with SYS_REFCURSOR we'll implemeted under terms of a separate task MDEV-33830 

        DECLARE
        		 
          c SYS_REFCURSOR;
        		 
          str1 VARCHAR2(200):= 'SELECT * FROM t1 WHERE a=:id';
        		 
          id INT:= 1;
        		 
        BEGIN
        		 
          OPEN c FOR str1 USING id;
        		 
        END;
        		 
        /

      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-34569 Oracle compatibility project 4

          • Major - Major loss of function.
          • Open
          causes

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36377 Assertion `thd->lex == sp_instr_lex' failed in LEX *sp_lex_instr::parse_expr(THD *, sp_head *, LEX *)

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36409 Server crashes when creating a table using function with a return type sys_refcursor

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          is blocked by

          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
          is part of

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

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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-31661 Assertion `thd->lex == sp_instr_lex' failed in LEX* sp_lex_instr::parse_expr(THD*, sp_head*, LEX*)

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36079 Stored routine with a cursor crashes on the second execution if a DDL statement happened

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

          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

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36114 Assertion `(mem_root->flags & 4) == 0' failed in Field_iterator_table::create_item

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36390 Minor refactoring of the method get_expr_query at the classes sp_instr_cpush/sp_instr_cursor_copy_struct

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36642 Bad IS NULL result on an expression containing a ROW SP variable

          • Major - Major loss of function.
          • Open

          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-33830 Support for cursors on prepared statements

          • Major - Major loss of function.
          • Open

          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

          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 Progress

          Bug - A problem which impairs or prevents the functions of the product. MDEV-36462 Crash on `DECLARE spvar1 ROW TYPE OF cursor1` after a table recreation

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

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov added a comment - - edited

            I've limited:

            • the scope this task to SYS_REFCURSOR only (a pre-defined weak cursor data type)
            • the scope of MDEV-10152 to TYPE .. IS REF CURSOR only (user defined weak and strong cursor data types)

            They don't duplicate each other any more.

            bar Alexander Barkov added a comment - - edited I've limited: the scope this task to SYS_REFCURSOR only (a pre-defined weak cursor data type) the scope of MDEV-10152 to TYPE .. IS REF CURSOR only (user defined weak and strong cursor data types) They don't duplicate each other any more.
            monty Michael Widenius added a comment -

            Implementation reviewed. Ok to push after a few minimal changes

            monty Michael Widenius added a comment - Implementation reviewed. Ok to push after a few minimal changes
            bar Alexander Barkov added a comment - - edited

            Hello ramesh, please find the patch for this task here:

            https://github.com/MariaDB/server/commit/b057fa93dec9b36521cb10e7d50c1458d49cf2d6

            The branch link:

            https://github.com/MariaDB/server/tree/bb-11.4-bar-MDEV-20034

            Thanks!

            bar Alexander Barkov added a comment - - edited Hello ramesh , please find the patch for this task here: https://github.com/MariaDB/server/commit/b057fa93dec9b36521cb10e7d50c1458d49cf2d6 The branch link: https://github.com/MariaDB/server/tree/bb-11.4-bar-MDEV-20034 Thanks!
            ramesh Ramesh Sivaraman added a comment -

            bar ok to push

            ramesh Ramesh Sivaraman added a comment - bar ok to push

            People

              bar Alexander Barkov
              Faisal Faisal Saeed (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.