The beginning of knowledge is the discovery of something we do not understand. [Frank Herbert]

Menu

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_testIS PROCEDURE swap( number_a IN OUT NUMBER , number_b IN OUT NUMBER );

ORA-06502: PL/SQL: numeric or value errorORA-06512: at “SCOTT.SWAP_TEST”, line 18ORA-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.

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)

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.