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


 

HTML Text

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)
 
IS
 
  xSTRING_TOO_LONG    EXCEPTION;
  xNEGATIVE_BALANCE   EXCEPTION;
 
BEGIN
  IF (length (vString) > 20) THEN
     RAISE xSTRING_TOO_LONG;
  END IF;
 
  IF (nBalance < 0) THEN
     RAISE xNEGATIVE_BALANCE;
  END IF;
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.

PROCEDURE Calculate_GPA (nSSN IN     STUDENTS.ssn%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.

Type

Description

IN

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

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

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