Adding IN parameter

Robert Baillie has posted a nice article reminding us about the use of “named notation” for program parameters, which triggered my memory about a problem I have had to deal with. I’m sure that some of you have encountered a situation when you needed to add an additional IN parameter to a program and use this new parameter in only one call. The other calls to this program did not need to pass any value to the new parameter. But if you added the new parameter to the program, you would break all the other calls. At the same time, you did not have the luxury to modify all the other calls. You were faced with a dilemma. Here is the solution, consider:

scott@eddev> create or replace procedure test_proc
  2  (param1_in in number, param2_out out varchar2)
  3  as
  4  begin
  5      param2_out := 'done';
  6  end;
  7  /

Procedure created.

Normally, I do not create procedures. I always use packages. But I’ll make an exception here for the sake of proving a point. Let’s test calling the procedure:

scott@eddev> declare
  2      value_out varchar2(20);
  3  begin
  4      test_proc (1, value_out);
  5      dbms_output.put_line(value_out);
  6  end;
  7  /
done

PL/SQL procedure successfully completed.

Now, let’s add another IN parameter, param2_in:

scott@eddev> create or replace procedure test_proc
  2  (param1_in in number, param2_in in number, param2_out out varchar2)
  3  as
  4  begin
  5      param2_out := 'done';
  6  end;
  7  /

Procedure created.

If you now call the procedure again exactly like we did above, i.e. test_proc (1, value_out); you get the following error:

ERROR at line 4:
ORA-06550: line 4, column 5:
PLS-00306: wrong number or types of arguments in call to 'TEST_PROC'
ORA-06550: line 4, column 5:
PL/SQL: Statement ignored

Unless you change the call to something like this test_proc (1, 2, value_out);, you will always get an error. But assuming that you cannot modify the call, you can still overcome this problem by doing two things:

  1. Put the new IN parameter at the end of the parameters list
  2. Default it to NULL

So, the procedure declaration would be:

test_proc (
    param1_in in number, 
    param2_out out varchar2, 
    param2_in in number default null)

Problem solved, you can now successfully call the procedure like this

test_proc (1, value_out);

or like this

test_proc (1, value_out, 2);

without getting any errors.

Possibly related:

Tagged | Post a Comment