{"id":77,"date":"2007-03-13T17:25:25","date_gmt":"2007-03-13T16:25:25","guid":{"rendered":"http:\/\/bar-solutions.com\/wordpress\/?p=77"},"modified":"2007-03-13T17:25:30","modified_gmt":"2007-03-13T16:25:30","slug":"parameters-in-oracle","status":"publish","type":"post","link":"https:\/\/blog.bar-solutions.com\/?p=77","title":{"rendered":"Parameters in Oracle"},"content":{"rendered":"

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).<\/p>\n

I created the following package<\/p>\n

\n

CREATE OR REPLACE PACKAGE swap_test
IS
  PROCEDURE swap(
    number_a IN OUT NUMBER
  , number_b IN OUT NUMBER ); <\/font><\/p>\n

  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; <\/font><\/p>\n

  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;
\/<\/font><\/p>\n<\/blockquote>\n

and then I tried the following script:<\/p>\n

\n

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;
\/<\/font><\/p>\n<\/blockquote>\n

And it gave me this error:<\/p>\n

\n

ORA-06502: PL\/SQL: numeric or value error
ORA-06512: at “SCOTT.SWAP_TEST”, line 18
ORA-06512: at line 19<\/font><\/p>\n<\/blockquote>\n

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.<\/p>\n

\n

SET SERVEROUTPUT ON
DECLARE
  ….
  c VARCHAR2( 20 );
  d VARCHAR2( 20 );
BEGIN
  ….
<\/font>  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;
\/<\/font><\/p>\n<\/blockquote>\n

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)<\/p>\n

\n

SET SERVEROUTPUT ON
DECLARE
  a NUMBER( 1 );
  b NUMBER( 1 );
  ….
<\/font>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 );
  ….
<\/font>END;
\/<\/font><\/p>\n<\/blockquote>\n

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.<\/p>\n

Maybe someone can supply me with a solution for this (or more insight) except for the one I already supplied.<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[2,5],"tags":[],"class_list":["post-77","post","type-post","status-publish","format-standard","hentry","category-oracle","category-plsql"],"_links":{"self":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/77","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=77"}],"version-history":[{"count":0,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=\/wp\/v2\/posts\/77\/revisions"}],"wp:attachment":[{"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=77"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=77"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.bar-solutions.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=77"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}