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.
ps_var_dyn.diff
Description: ps_var_dyn.diff
_______________________________________________ Mailing list: https://launchpad.net/~maria-developers Post to : [email protected] Unsubscribe : https://launchpad.net/~maria-developers More help : https://help.launchpad.net/ListHelp
