Lab 12.2 Passing Parameters In and Out of Procedures

Lab Objective

After this Lab, you will be able to:

Use IN and OUT Parameters with Procedures


Parameters are the means to pass values to and from the calling environment to the server. These are the values that will be processed or returned via the execution of the procedure. There are three types of parameters: IN, OUT, and IN OUT.


Modes specify whether the parameter passed is read in or a receptacle for what comes out.

Figure 12.1 illustrates the relationship between the parameters when they are in the procedure header versus when the procedure is executed.

Figure 12.1. Matching Procedure Call to Procedure Header


Formal and Actual Parameters

Formal parameters are the names specified within parentheses as part of the header of a module. Actual parameters are the values ”expressions specified within parentheses as a parameter list ”when a call is made to the module. The formal parameter and the related actual parameter must be of the same or compatible datatypes. Table 12.1 explains the three types of parameters.

Passing of Constraints (Datatype) with Parameter Values

Formal parameters do not require constraints in datatype ”for example, instead of specifying a constraint such as VARCHAR2(60), you just say VARCHAR2 against the parameter name in the formal parameter list. The constraint is passed with the value when a call is made.

Matching Actual and Formal Parameters

Two methods can be used to match actual and formal parameters: positional notation and named notation. Positional notation is simply association by position: The order of the parameters used when executing the procedure matches the order in the procedure's header exactly. Named notation is explicit association using the symbol =>.

Table 12.1. Three Types of Parameters





Passes a value into the program

  • Read only value

  • Constants, literals, expressions

  • Cannot be changed within program Default Mode


Passes a value back from the program

  • Write only value

  • Cannot assign default values

  • Has to be a variable

  • Value assigned only if the program is successful


Passes values in and also sends values back

  • Has to be a variable

  • Values will be read and then written

 Syntax: formal_parameter_name => argument_value 

In named notation, the order does not matter. If you mix notation, list positional notation before named notation.

Default values can be used if a call to the program does not include a value in the parameter list. Note that it makes no difference which style is used; they will both function similarly.

Oracle PL[s]SQL by Example
Oracle PL[s]SQL by Example
ISBN: 3642256902
Year: 2003
Pages: 289 © 2008-2017.
If you may any questions please contact us: