Oracle Database Tips by Donald Burleson

IN, OUT and INOUT Modes

Values are passed to a procedure in three modes; IN, OUT and INOUT.  The mode which a variable is passed defines how the variables can be used inside the procedure.  Let's take a closer look at each mode.

IN Mode

A variable passed as mode IN is always read-only.  A variable using IN mode can be read and used by the procedure/function but can not be changed and it cannot be the receiver of an assignment operation.  Internal to the scope of the procedure or function, variables pass using IN mode can be considered a constant.  The IN mode is the default mode to pass a variable, however it is recommended for maintainability reasons to always define the variable passing mode when you define the variable.  Variables passed IN can also be assigned a default value as discussed above.

In the example below, three variables are defined as IN variables.  Note that on line 7 the code attempts to assign the variable n_1 the sum of the other two variables.  This procedure fails on compile because n_1 was assigned a mode of IN and therefore can not be used in an assignment.

SQL> create or replace procedure example_defaults
  2    (n_1 in number := 5,
  3     n_2 in number := 6,
  4     n_3 in number := 7)
  5  as
  6  begin
  7    n_1 := n_2 + n_3;
  8  end;
  9  / 

Warning: Procedure created with compilation errors. 

SQL> show errors
Errors for PROCEDURE EXAMPLE_DEFAULTS: 

LINE/COL ERROR
-------- ------------------------------------------------7/3      PLS-00363: expression 'N_1' cannot be used as an
assignment target 

7/3      PL/SQL: Statement ignored

OUT Mode

A variable passed in OUT mode is used to pass information back from the procedure to the calling program.  It is a write-only variable and has no value until the block assigns it a value.  Internally, an OUT variable is created and not initialized when the procedure is called.  When the procedure ends, the variable value (upon ending) is copied to the variable passed in the call.  As such, a variable passed in OUT mode can not be assigned a default value nor can it be read inside the procedure.  Because the variable value is copied back to the passed variable when the procedure terminates, the calling code can not pass an OUT variable a literal value.  If the procedure raises an exception that is not caught, it will result in the OUT variable not being copied when the procedure terminates. 

SQL> create or replace procedure example_defaults
  2    (n_1 in number := 5,
  3     n_2 in number := 6,
  4     n_3 out number := 7)
  5  as
  6  begin
  7    null;
  8  end;
  9  / 

Warning: Procedure created with compilation errors. 

SQL> show errors
Errors for PROCEDURE EXAMPLE_DEFAULTS:
 

LINE/COL ERROR
-------- -------------------------------------------
4/4      PLS-00230: OUT and IN OUT formal parameters may not have default expressions

INOUT Mode

A variable passed in INOUT mode has characteristics of both the IN and the OUT mode.  The variable value is passed in and can be read by the procedure.  The procedure can also change the value and it will be copied back to the passed variable when the procedure completes.  Like a variable passed in OUT mode, an INOUT variable can not have a default value and can not be passed as a literal.  If the procedure terminates abnormally (as in an exception) the INOUT variable will not be copied back to the variable passed in.

The above book excerpt is from:

Easy Oracle PL/SQL Programming

Get Started Fast with Working PL/SQL Code Examples

ISBN 0-9759135-7-3   

John Garmany 

http://www.rampant-books.com/book_2005_1_easy_plsql.htm