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

sql_mode=ORACLE: Derive length of VARCHAR SP parameters with no length from actual parameters

    XMLWordPrintable

    Details

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

      Description

      In sql_mode=oracle, when an SP parameter of the VARCHAR data type is defined without length,
      the length should be inherited from the actual argument at call time. This is how Oracle works.

      This script works fine in Oracle:

      CREATE OR REPLACE PROCEDURE p1(p OUT VARCHAR)
      AS
      BEGIN
        p:='0123456789';
      END;
      /
      declare w varchar(10);
      begin
        p1(w);
      end;
      /
      

      This script:

      declare w varchar(8);
      begin
        p1(w);
      end;
      /
      

      fails with an error:

      ERROR at line 1:
      ORA-06502: PL/SQL: numeric or value error: character string buffer too small
      

      Furthermore, since Oracle 9, VARCHAR datatype in PL/SQL is not limited to 4000 char but to 32k.
      It's the size of varchar column in a table that is limited to 4000 (until Oracle 12C which allow 32k when MAX_STRING_SIZE=EXTENDED).

        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:

                Git Integration