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

 

 

   
 

PL/SQL Notations
Oracle Tips by Burleson
 

PL/SQL supports two types of notation when calling procedures with parameters: named notation and positional notation.

Named Notation

Named notation is calling a stored procedure by specifying both the parameter names and each parameterís value. Listing 4.13 illustrates the use of named notation when calling a stored procedure.

Listing 4.13 Calling a procedure using named notation.

Check_Source_For_Insert (vOwner  => 'JSCHMOE',
                         vObject => 'CALCULATE_GPA');

When using named notation, the parameters for a procedure can be specified in any order (after all, the developer knows which values are intended for which parameters). Obviously, the use of named notation requires that the developer knows the names of the parameters and specifies the parameter names in procedure calls.

Positional Notation

Positional notation is calling a stored procedure by simply passing parameter values and assuming that the values will be associated with parameters in the order of declaration. The first value passed is associated with the first parameter, the second value with the second parameter, and so forth. Listing 4.14 illustrates a call to a stored procedure using positional notation.

Listing 4.14 Calling a stored procedure using positional notation.

Check_Source_For_Insert ('JSCHMOE',
                         'CALCULATE_GPA');

When using positional notation, the values must be passed to the stored procedure in the proper order. This requires that developers know the order of the parameters.

Mixing Notations

Itís possible to use both named notation and positional notation in a single call to a stored procedure, as shown in Listing 4.15.

Listing 4.15 Mixing named and positional notation.

Approve_For_Credit (999999999,
                    9032012912,
                    'C',
                    iBounced_Checks => 0,
                    iOverdrafts     => 0);

When mixing notation like this, itís important to keep in mind that positional notation can be used only before named notation has been used. If even one parameter is populated by using named notation, all subsequent parameters must also be populated using named notation.


Named Or Positional Notation?
Consider again the code in Listing 4.15. While you may have figured out that the first parameter is a social security number, you probably have no idea what the second and third parameters are supposed to represent. If the code had used named notation (or if variables were being passed instead of literal values), you would be able to see that the second parameter holds an account number and the third parameter indicates whether the account is a checking or savings account.

I prefer the use of named notation when calling procedures and functions. While somewhat more work is involved during development, itís much clearer down the road to see what values are being passed to which parameters. And when combined with meaningfully named variables, named notation contributes a great deal to making the code self-documenting.


        
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