Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-10142 PL/SQL parser
  3. MDEV-10577

sql_mode=ORACLE: %TYPE in variable declarations

    XMLWordPrintable

    Details

    • Sprint:
      10.2.2-3, 10.2.2-1, 10.2.2-2, 10.2.2-4, 10.1.18

      Description

      When running in sql_mode=ORACLE, MariaDB should support %TYPE in variable declarations, in function and procedure parameter delcarations, and function RETURN clauses:

        tmp t1.a%TYPE;
      

      The above statement declares a variable that has the same data type with the column a in the table t1.

      Example:

      DROP TABLE t1;
      CREATE TABLE t1 (a INT);
      INSERT INTO t1 VALUES (10);
      INSERT INTO t1 VALUES (20);
       
      DROP FUNCTION f1;
      CREATE FUNCTION f1(prm t1.a%TYPE) RETURN t1.a%TYPE
      AS
        tmp t1.a%TYPE;
      BEGIN
        SELECT MAX(a) INTO tmp FROM t1;
        RETURN tmp + prm;
      END;
      /
      SELECT f1(5) FROM DUAL;
      

      SQL> 
           F1(5)
      ----------
      	25
      

      Scope of this task

      This task will implement %TYPE only for local variables and parameters. Using %TYPE in stored function RETURN clause will be done under terms of MDEV-11210.

      Oracle behavior

      1. %TYPE and missing tables during routine CREATE time

      Oracle checks if the referenced table exists during the routine CREATE time. If the referenced table does not exists, a warning is issued, but the routine is created.

      DROP TABLE t1;
      DROP PROCEDURE p1;
      CREATE PROCEDURE p1
      AS
       a t1.a%TYPE := 123;
      BEGIN
        EXECUTE IMMEDIATE 'INSERT INTO t1 (a) VALUES(:a)' USING a;
      END;
      /
      SHOW ERRORS;
      

      LINE/COL ERROR
      -------- -----------------------------------------------------------------
      3/4	 PL/SQL: Item ignored
      3/4	 PLS-00201: identifier 'T1.A' must be declared
      5/3	 PL/SQL: Statement ignored
      5/59	 PLS-00320: the declaration of the type of this expression is
      	 incomplete or malformed
      

      The routine created with a missing identifier warning can be used as soon as the referenced table is created:

      CREATE TABLE t1 (a INT);
      CALL p1();
      SELECT * FROM t1;
      

      	 A
      ----------
             123
      

      Notice, the CALL statement inserted 123 into t1.

      2. Dropping the referenced table before the routine execution leads to an error on CALL

      DROP TABLE t1;
      CREATE TABLE t1 (a VARCHAR(10));
      DROP PROCEDURE p1;
      CREATE PROCEDURE p1
      AS
        a t1.a%TYPE:='xxx';
      BEGIN
        NULL;
      END;
      /
      CALL p1();
      DROP TABLE t1;
      CALL p1();
      SELECT * FROM user_errors;
      

      NAME			       TYPE	      SEQUENCE	     LINE   POSITION
      ------------------------------ ------------ ---------- ---------- ---------- 
      TEXT
      --------------------------------------------------------------------------------
      ATTRIBUTE MESSAGE_NUMBER
      --------- --------------
      P1			       PROCEDURE	     2		3	   5
      PL/SQL: Item ignored
      ERROR		       0
       
      P1			       PROCEDURE	     1		3	   5
      PLS-00201: identifier 'T1.A' must be declared
      ERROR		     201
       
      NAME			       TYPE	      SEQUENCE	     LINE   POSITION
      ------------------------------ ------------ ---------- ---------- ----------
      TEXT
      --------------------------------------------------------------------------------
      ATTRIBUTE MESSAGE_NUMBER
      --------- --------------
      

      3. Dropping the referenced table during the routine execution does not affect %TYPE variables

      DROP TABLE t1;
      DROP TABLE t2;
      CREATE TABLE t1 (a VARCHAR(10));
      CREATE TABLE t2 (a VARCHAR(10));
      DROP PROCEDURE p1;
      CREATE PROCEDURE p1
      AS
      BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE t1';
        DECLARE
          a t1.a%TYPE:='xxx';
        BEGIN
          EXECUTE IMMEDIATE 'INSERT INTO t2 VALUES (:1)' USING a;
        END;
      END;
      /
      CALL p1();
      SELECT * FROM t2;
      

      A
      ----------
      xxx
      

      Notice, the variable was declared after DROP TABLE.

      MariaDB behavior

      To make MariaDB reproduce Oracle's behavior as close as possible, we'll assing data type to %TYPE variables at sp_rcontext::create() time.

      Like Oracle, we'll not require the referenced tables to exists at the routine CREATE time. Unlike Oracle, we won't check the referenced tables and won't generate warnings.

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                bar Alexander Barkov
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: