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

sql_mode=ORACLE: Allow VARCHAR and VARCHAR2 without length as a data type of routine parameters and in RETURN clause

    Details

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

      Description

      In Oracle, VARCHAR, VARCHAR2, RAW can be specified without length in parameters and RETURN clause.

      Note, variable declarations still require length.

      Example:

      DROP FUNCTION f1;
      CREATE FUNCTION f1(a VARCHAR2) RETURN VARCHAR2
      AS
        b VARCHAR2(20):= a;
      BEGIN
        RETURN b;
      END;
      /
      SELECT f1('test') FROM DUAL;
      

      Example:

      DROP FUNCTION f1;
      CREATE FUNCTION f1(a RAW) RETURN RAW
      AS
        b RAW(20):= a;
      BEGIN
        RETURN b;
      END;
      /
      SELECT f1('616263') FROM DUAL;
      

      In case of CHAR data type, when used in a parameter or a return value, a data type without parentheses means maximum size, while in a variable definition, CHAR with no parentheses still means CHAR(1).

      DROP FUNCTION f1;
      CREATE FUNCTION f1(a CHAR) RETURN CHAR
      AS
        b CHAR(10):= a;
      BEGIN
        RETURN b;
      END;
      /
      

      In Oracle, CHAR and RAW data types have a limit of 2000 bytes. In MariaDB, fixed length types are limited to 255 characters. To guarantee that all possible values can fit, we'll translate CHAR without length in SP parameters or return values to VARCHAR(2000).

      In Oracle, VARCHAR is limited to 4000 bytes. We'll translate a VARCHAR with no length (in SP parameters or return values) to VARCHAR(4000) as well.

      The full translation list:

      • CHAR -> VARCHAR(2000)
      • NCHAR -> NVARCHAR(2000)
      • RAW -> VARBINARY(2000)
      • VARCHAR -> VARCHAR(4000)
      • NVARCHAR -> NVARCHAR(4000)

        Attachments

          Activity

            People

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

              Dates

              • Created:
                Updated:
                Resolved: