Until today I was under the impression that parameters in PL/SQL were unrestricted in size. That is only partially correct. I was playing around with a simple script to swap two variables without introducing a temporary variable (I know, completely useless, but fun to think about a bit).
I created the following package
CREATE OR REPLACE PACKAGE swap_test
IS
PROCEDURE swap(
number_a IN OUT NUMBER
, number_b IN OUT NUMBER );PROCEDURE swap(
char_a IN OUT VARCHAR2
, char_b IN OUT VARCHAR2 );
END swap_test;
/
CREATE OR REPLACE PACKAGE BODY swap_test
IS
PROCEDURE swap(
number_a IN OUT NUMBER
, number_b IN OUT NUMBER )
IS
BEGIN
number_a := number_a + number_b;
number_b := number_a – number_b;
number_a := number_a – number_b;
END swap;PROCEDURE swap(
char_a IN OUT VARCHAR2
, char_b IN OUT VARCHAR2 )
IS
BEGIN
char_a := char_a || char_b;
char_b := SUBSTR( char_a, 1, LENGTH( char_a ) – LENGTH( char_b ));
char_a := SUBSTR( char_a, LENGTH( char_b ) + 1, LENGTH( char_a ));
END swap;
END swap_test;
/
and then I tried the following script:
SET SERVEROUTPUT ON
DECLARE
a NUMBER;
b NUMBER;
c VARCHAR2( 10 );
d VARCHAR2( 10 );
BEGIN
a := 3;
b := 5;
DBMS_OUTPUT.put( ‘ a=’ || a );
DBMS_OUTPUT.put_line( ‘ b=’ || b );
swap_test.swap( a, b );
DBMS_OUTPUT.put( ‘ a=’ || a );
DBMS_OUTPUT.put_line( ‘ b=’ || b );
c := ‘Dana’;
d := ‘Patrick’;
DBMS_OUTPUT.put( ‘ c=’ || c );
DBMS_OUTPUT.put_line( ‘ d=’ || d );
swap_test.swap( c, d );
DBMS_OUTPUT.put( ‘ c=’ || c );
DBMS_OUTPUT.put_line( ‘ d=’ || d );
END;
/
And it gave me this error:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at “SCOTT.SWAP_TEST”, line 18
ORA-06512: at line 19
It appeared that the varchar2 variable was too small to hold the concatenated value of both. When I made the varchar2 variables a bit bigger (big enough to hold both values concatenated) the error went away.
SET SERVEROUTPUT ON
DECLARE
….
c VARCHAR2( 20 );
d VARCHAR2( 20 );
BEGIN
….
c := ‘Dana’;
d := ‘Patrick’;
DBMS_OUTPUT.put( ‘ c=’ || c );
DBMS_OUTPUT.put_line( ‘ d=’ || d );
swap_test.swap( c, d );
DBMS_OUTPUT.put( ‘ c=’ || c );
DBMS_OUTPUT.put_line( ‘ d=’ || d );
END;
/
But, what about the number then? Changed the script again from number into number(1) (just one position, so numbers 0 to 9). I also changed the values, so if they are added, they will be 10 or higher (taking up 2 positions)
SET SERVEROUTPUT ON
DECLARE
a NUMBER( 1 );
b NUMBER( 1 );
….
BEGIN
a := 3;
b := 8;
DBMS_OUTPUT.put( ‘ a=’ || a );
DBMS_OUTPUT.put_line( ‘ b=’ || b );
swap_test.swap( a, b );
DBMS_OUTPUT.put( ‘ a=’ || a );
DBMS_OUTPUT.put_line( ‘ b=’ || b );
….
END;
/
And hey, that worked flawlessly. So, apparently Oracle will change the dimensions of a number as needed, but it cannot change the dimensions of a varchar2 parameter at runtime. I think it has to do with the parameters being OUT parameters. I tried this on Oracle 8i and 10G XE and it gave me the same results. I don’t know if this should be considered a bug. I also tried to build the function using a subtype for the varchar2 parameters, using the biggest size possible, but I got the same error.
Maybe someone can supply me with a solution for this (or more insight) except for the one I already supplied.