02Aug
By: Steven Feuerstein On: August 2, 2022 In: APEX Developer Solutions, Feuertips Comments: 0

Let’s talk about parameters. You can’t talk about parameters without talking about procedures and functions (well, ok, also cursors can have parameters). But you could talk about procedures and functions without talking about parameters. Because these subprograms could have no parameter list. Example:

function right_now return date
is
begin
   return sysdate;
end;

And I suppose in theory you could always avoid using parameters by relying entirely on package-based globals. Example:

create or replace package my_globalized_parameters is
   g_date date;
   g_id number;
end;
/

create or replace procedure do_stuff_with_date_and_id is
begin
   if my_globalized_parameters.g_date < sysdate then
      dbms_output.put_line(my_globalized_parameters.g_date);
   else
      dbms_output.put_line(g_id);
   end if;
end;
/

begin
   my_globalized_parameters.g_date := sysdate + 10;
   my_globalized_parameters.g_id := 100;
   do_stuff_with_date_and_id;
end;
/

But…oh my lordy! Hopefully you are all well aware of the dangers of this approach. Generally, you do not want to refer to global variables within your programs; these are hidden dependencies that make it difficult and dangerous to use those programs.

Much better is to make explicit via the parameter exactly what must be passed into the procedure for it work properly, as in:

create or replace procedure do_stuff_with_date_and_id (
   p_date in date, p_id in number)
is
begin
   if g_date < sysdate then
      dbms_output.put_line(p_date);
   else
      dbms_output.put_line(p_id);
   end if;
end;
/

Here’s some nomenclature that we use to talk about parameters:

  • formal parameter – the parameter defined in the subprogram DDL
  • argument (or argument value) – the literal, expression, variable or constant that is passed to the subprogram for a particular formal parameter
  • pass by reference – “The compiler passes the subprogram a pointer to the actual parameter. The actual and formal parameters refer to the same memory location.” (from the doc)
  • pass by value – The compiler assigns the value of the actual parameter to the corresponding formal parameter. The actual and formal parameters refer to different memory locations. (from the doc)

Parameters can have one of three modes:

  • in – the argument is passed by reference and can only be read (not changed) (the default)
  • out – a value is passed (by value) back to the invoker of the subprogram
  • in out – the argument is passed by value to the subprogram and can then be modified and passed back to the invoker

Here are some tips I cover in the video regarding parameters:

  • Use the nocopy hint to avoid the copy-in/copy-out semantics of the “pass by value” in PL/SQL.
  • Whenever possible, preserve backward compatibility when adding new parameters to an existing program (with a default value or overloading).
  • Related to that, always append new parameters to the end of the existing parameter list.
  • Interestingly enough, an out parameter can be read inside your subprogram.
  • If you don’t assign a value to an out parameter, then it is set to null upon return of control to the invoker.
  • You can associate arguments with formal parameters with either positional or named notation.

Resources:

Oracle Doc: Subprogram Parameters

Demonstration of Named Notation

Feuertips

Wednesdays at 11 am ET, we go live on YouTube and Facebook to provide you with a not-to-be-missed PL/SQL tip and fun conversation. Sure, we record it so you can always watch it later, but live is so much more rewarding!

A participant is selected to choose from 3 organizations dedicated to the preservation of our planet and its biodiversity. Insum will then make a donation of $25 to that group in your name.

What could be better than leveling up your PL/SQL tips and helping a worthy organization? Join us Wednesdays at 11!

Share this:
Share

Leave reply:

Your email address will not be published.