Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle




Constraining Parameters
Oracle Tips by Burleson

While parameters must have a datatype specified, itís not possible to constrain the length of a parameter. That is, if you define a parameter of datatype varchar2, that parameter can accept between 0 and 2,000 characters via that parameter! Likewise, you cannot require that a parameter be passed to the procedure.

If you must constrain parameters, explicit checks must be made inside your procedure, like the ones shown in Listing 4.7.

Listing 4.7 Checking the values of parameters.

PROCEDURE Test_Parameters (vString  IN     varchar2,
                           nBalance IN     number)
  IF (length (vString) > 20) THEN
  IF (nBalance < 0) THEN
END Test_Parameters;

%TYPE Parameters

Parameters can reference the datatype of a column in a table using %TYPE, as shown in Listing 4.8.

Listing 4.8 Defining a parameter using %TYPE.


If a parameter references the datatype of a column this way and the datatype of the column changes, the datatype of the parameter changes to correspond to the columnís datatype.

%ROWTYPE Parameters

Parameters can also reference the structure of a table or record by using %ROWTYPE, as shown in Listing 4.9.

Listing 4.9 Defining a parameter using %ROWTYPE.

PROCEDURE Print_Diploma (nStudent_rec IN     STUDENTS%ROWTYPE);

Parameters defined using %ROWTYPE change their definition if the referenced record type or row structure changes.

Parameter Types

There are three types of parameters for stored procedures: IN, OUT, and IN OUT. Each parameter type is described in Table 4.1.

Table 4.1 The three parameter types.




IN parameters are used to pass a value to the procedure. The procedure is not able to alter the value of the parameter in any way. This is the most commonly used type of parameter.


OUT parameters are used to return a value from the procedure. The procedure can assign a value to the parameter but can never read the value contained in the parameter.


IN OUT parameters are used to pass a value to the procedure, which the procedure can then alter. The procedure is able to read values from and write values to the parameter.

If a type is not specified for a parameter, the parameter defaults to type IN.

Default Values

IN parameters (and only IN parameters) can be given a default value by using either the assignment operator (:=) or the DEFAULT statement, as shown in Listing 4.10.

Listing 4.10 Default values for parameters.

PROCEDURE Raise_Salary (nEmployeeID IN     number,
                        nRaiseAmt   IN     number DEFAULT .001);
PROCEDURE Raise_Salary (nEmployeeID IN     number,
                        nRaiseAmt   IN     number := .001);

When a NULL value is passed for a parameter with a default value, the parameterís value is set to the default value. If a value is passed for the parameter, the default value has no effect.

This is an excerpt from the book "High Performance Oracle Database Automation" by Jonathan Ingram and Donald K. Burleson, Series Editor.


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter