A script-enabled browser is required for this page to function properly.

Defining Parameters

Using parameters is optional. Subprograms that do not take parameters are written without parentheses. If you declare a parameter, you must indicate its data type and mode in the formal parameter List.

Parameter Data Types

In a function, parameters can be declared as type VARCHAR2, DATE, NUMBER, or BOOLEAN, or any of the Oracle Forms object types. In a procedure, parameters can be of any valid PL/SQL type. When you create a function or procedure, enter the data type after the name of each formal parameter:

PROCEDURE display_overdue_shipments (dept_id NUMBER);
FUNCTION credit_ok (customer VARCHAR2) RETURN BOOLEAN;

Parameter Modes

The mode of a parameter can be either IN (the default), OUT, or IN OUT. An IN parameter passes a value to a subprogram. The subprogram can read the value of the IN parameter, but cannot write its value. An actual IN parameter can be a constant, literal, initialized variable, or expression.

An OUT parameter returns a value from a subprogram. An actual OUT parameter can only be a local variable (not a bind variable), and the subprogram must assign a value to the variable. If it does not, the predefined PL/SQL exception NO_DATA_FOUND is raised.

An IN OUT parameter passes a value to a subprogram and returns a value from the subprogram. An actual IN OUT parameter can be a local variable or a bind variable. A subprogram can write the value of an IN OUT parameter, but is not required to do so.

Both functions and procedures can take OUT and IN OUT parameters. A procedure that returns a value by way of an OUT or IN OUT parameter works much like a function, and can be used to accomplish anything that a function would normally be used for.

If you do not explicitly indicate the mode of a formal parameter in the procedure or function specification, the parameter is declared as an IN parameter. Thus, the following procedure specifications are equivalent:

PROCEDURE calculate_discount(multiplier NUMBER);
PROCEDURE calculate_discount(multiplier IN NUMBER);

You can explicitly state the mode of a parameter by entering IN, OUT, or IN OUT after the formal parameter name in the subprogram specification.

Note: When you call a subprogram that takes an IN OUT or OUT parameter, be aware that a value is always assigned to the actual parameter, even if the subprogram itself does not explicitly write the value of the parameter.

For example, if you reference a text item as an actual parameter for a formal IN OUT parameter, the value of the text item is updated when the subprogram executes, even if the subprogram itself does not do an explicit assignment statement. This can cause the record status to become CHANGED, and the record to be marked for updateor insert.

Assigning Default Values to IN Parameters

You can assign a default value to a formal IN parameter when you declare the parameter. Many of the Oracle Forms Built-in subprograms use this technique. When you call these subprograms, you do not have to enter an actual parameter for the default formal parameter unless you want to override the parameter's default value.

To assign a default value to a formal parameter, use the PL/SQL assignment operator in the formal parameter List. The value you assign must be a constant or expression of the same data type as the formal parameter:

PROCEDURE calculate_discount(multiplier IN NUMBER := 15);

When you call this procedure, you can omit the multiplier parameter if you want the procedure to execute with the default value (15):

calculate_discount;

Or, you can override the default value by providing an actual parameter:

calculate_discount(10);

Note that when using positional notation, only trailing parameters can have default values.