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

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)