maria-developers team mailing list archive

[jerome brauge <j.brauge@xxxxxxxxxxx>]

Hi Alexander,
In sql_mode=oracle, when a stored procedure varchar parameter is defined without size, you have chosen to translate it by VARCHAR(4000) (or CHAR(2000) for CHAR parameter).
Oracle does not work like this. Size is inherited from the size of argument at runtime.
Example:
CREATE OR REPLACE PROCEDURE p1(p OUT VARCHAR)
AS
BEGIN
  p:='0123456789';
END;
/
declare w varchar(10);
begin
  p1(w);
end;
/
--> work fine

declare w varchar(8);
begin
  p1(w);
end;
/
--> failed with :
 ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "OPS$DBO.P1", line 4
ORA-06512: at line 3

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).

I've done the attached patch to resize these parameters at runtime. What do you think about it ?

Another question : Oracle does not allow to change the value of an IN  parameter.
The following procedure cannot be compile :
CREATE OR REPLACE PROCEDURE p1(p IN VARCHAR)
AS
BEGIN
  p:='0123456789';
END;
/

Warning: Procedure created with compilation errors.

//sun10:1521/CS> select * from user_errors;

NAME                           TYPE           SEQUENCE       LINE   POSITION
------------------------------ ------------ ---------- ---------- ----------
TEXT
------------------------------------------------------------------------------------------------------------------------------------
ATTRIBUTE MESSAGE_NUMBER
--------- --------------
P1                             PROCEDURE             1          4          3
PLS-00363: expression 'P' cannot be used as an assignment target
ERROR                363

Mariadb allow this. It's fine for us but do you have plan to change this behavior ? (or add a new STRICT_xxx mode ?)

Regard,
Jérôme.

Attachment: ps_var_dyn.diff
Description: ps_var_dyn.diff