Parameters in Oracle

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.

Leave a Reply

Your email address will not be published.