Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

PL/SQL Call Notation Tips

Oracle Tips
 

PL/SQL Named Notation standards

Procedures and functions should always be called using named notation for their parameters. This helps identify the data that is being passed to the stored PL/SQL object (assuming that the identifiers chosen for the parameters are meaningful). Place only one parameter on each line of the call:

DaysBetween (dStartDate   => dEnrolledDate,
             dEndDate     => dGraduationDate,
             nGPA         => nFinalGPA,
             nDaysBetween => nDuration);

There are multiple methods of associating the actual parameters with the formal parameters during a subprogram call. And they are,

PL/SQL Positional Notation

The process of associating the actual parameter implicitly by considering the position of the formal parameters in the subprogram's header is called as the positional notation. With the positional notation, the PL/SQL compiler associates the first actual parameter from the subprogram call with the first formal parameter of the subprogram, second actual parameter from the subprogram call with the second formal parameter of the subprogram and so on.

 

The function in the above example can be called using the positional notation as like below,

 

DECLARE

  l_vc_dept VARCHAR2(30);

  l_n_sal   NUMBER;

BEGIN

  l_n_sal:=func_emp(7815,'John','13-June-2011',l_vc_dept);

  dbms_output.put_line('The Employee''s dept is '||l_vc_dept);

  dbms_output.put_line('The Employee''s sal is '||l_n_sal);

END;

/

 

In the above example, the actual parameters 7815, 'John', '13-June-2011' and the variable l_vc_dept are in the exact order of their formal parameters declared in the function header.

PL/SQL Named Notation

The process of associating the actual parameter explicitly by the formal parameter's name in the subprogram's header is called as the positional notation. The combination symbol => is used for combining the formal and the actual parameters during the named notation call.

 

The prototype for the named notation call of a procedure is as below,

 

Procedure_name(formal_parameter1=>actual_parameter1,formal_parameter2=>actual_parameter2,..);

 

As the name of the formal parameters are provided explicitly, the PL/SQL compiler does not have to rely on the order of the parameters to make the association between the formal and the actual parameters.

 

The function in the above example can be called using the named notation as like below. Here the order of the parameters are shuffled but the function identifies the parameters using their named notation.

 

DECLARE

  l_vc_dept VARCHAR2(30);

  l_n_sal   NUMBER;

BEGIN

  l_n_sal:=func_emp(emp_name=>'John',emp_id=>7815,emp_dept=>l_vc_dept,emp_join_date=>'13-June-2011');

  dbms_output.put_line('The Employee''s dept is '||l_vc_dept);

  dbms_output.put_line('The Employee''s sal is '||l_n_sal);

END;

/

Mixed Notation

The process of using both the positional notation and the named notation together is called as mixed notation. The important rule to keep in mind while performing this type of notation is that the positional notations should be used before the usage of the named notation. If a parameter is populated using the named notation, all subsequent parameters must also be populated using the named notation.

The function in the above example can be called using the mixed notation as like below. In the below example, the first two parameters are populated using the positional notation and the rest parameters are populated using the named notation.

 

DECLARE

  l_vc_dept VARCHAR2(30);

  l_n_sal   NUMBER;

BEGIN

  l_n_sal:=func_emp(7815,'John',emp_join_date=>'13-June-2011',emp_dept=>l_vc_dept);

  dbms_output.put_line('The Employee''s dept is '||l_vc_dept);

  dbms_output.put_line('The Employee''s sal is '||l_n_sal);

END;

/

 

The parameters using for the population can be skipped if it is not a mandatory one (by defaulting the parameter to some value during the subprogram creation).


 

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.