What exactly are IN, OUT, IN OUT parameters in PL/SQL

Question

I've looked up questions here as well as looking online and watching videos but I'm still confused exactly what IN, OUT is. The reason I'm asking is because I'm writing a procedure that will log an error based on the IN parameters in other procedures,

Cheers!

Giraudoux

@dwjv Unfortunately the question not answered correctly. For some weird reason the only answer in that question is about plpgsql rendering the question useless. – Pylorus

Answer 1

The Oracle documentation here does a good job of explaining:

The mode of a parameter indicates whether the parameter passes data to a procedure (IN), returns data from a procedure (OUT), or can do both (IN OUT).

And about OUT parameters specifically:

... you cannot use it to pass a value to the procedure. Nor can you read its value inside the procedure, even after a value has been assigned to it.

EDIT

Actually, though the information provided above is valid, I linked to a poor resource (SQL*Module for Ada Programmer's Guide).

A much better and more complete resource to better understand the 3 modes can be found here: Table 8-1 PL/SQL Subprogram Parameter Modes.

IN mode:

• Default mode

• Passes a value to the subprogram.

• Formal parameter acts like a constant: When the subprogram begins, its value is that of either its actual parameter or default value, and the subprogram cannot change this value.

• Actual parameter can be a constant, initialized variable, literal, or expression.

• Actual parameter is passed by reference.

OUT mode:

• Must be specified.

• Returns a value to the invoker.

• Formal parameter is initialized to the default value of its type. The default value of the type is NULL except for a record type with a non-NULL default value.

• When the subprogram begins, the formal parameter has its initial value regardless of the value of its actual parameter. Oracle recommends that the subprogram assign a value to the formal parameter.

• If the default value of the formal parameter type is NULL, then the actual parameter must be a variable whose data type is not defined as NOT NULL.

• By default, actual parameter is passed by value; if you specify NOCOPY, it might be passed by reference.

IN OUT mode:

• Must be specified.

• Passes an initial value to the subprogram and returns an updated value to the invoker.

• Formal parameter acts like an initialized variable: When the subprogram begins, its value is that of its actual parameter. Oracle recommends that the subprogram update its value.

• Actual parameter must be a variable (typically, it is a string buffer or numeric accumulator).

• By default, actual parameter is passed by value (in both directions); if you specify NOCOPY, it might be passed by reference.

Garrard