Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Dec 17, 2002

Call by Reference Parameters in PL/SQL (The NOCOPY Hint)

By DatabaseJournal.com Staff

Amar Kumar Padhi, amar_padhi@hotmail.com

Prior to Oracle 8i releases, the IN OUT parameters in PL/SQL routines were passed using the copy-in and copy-out semantics (call by value).

For example:

create or replace procedure cpy_chk 
               (pi_val     in      varchar2, 
                po_dat     out     date, 
                pio_status in out  varchar2) is 
   po_dat := sysdate; 
   pio_status := pio_status || 'amar is testing'; 

When the above code is executed, Oracle will copy the value being passed to parameters PO_DAT and PIO_STATUS in a separate buffer for the routine. On completion of the routine, Oracle will copy the value being held in the PO_DAT and PIO_STATUS parameters back to the original variable. This results in multiple buffers being opened in memory and the overhead of copying data back and forth. This can be huge in terms of CPU and Memory overhead if the parameter is meant for large strings or collection objects.

The IN parameter is passed by reference; that is, a pointer to the actual IN parameter is passed to the corresponding formal parameter. So, both parameters refer to the same memory location and no copying overhead is involved. However, the OUT and IN OUT parameters are passed by value.

To get around this, package variables were being used to pass values around. Though serviceable as an alternative to prevent multiple buffers and copy overhead, it resulted in higher maintenance cost.

From Oracle 8i onwards, the NOCOPY parameter hint has been introduced for OUT and IN OUT parameters. Using this hint tells Oracle to make a call by reference. Use this hint when there is no need to preserve the original value (in case the called routine raises an exception). Oracle's internal benchmark testing shows improvements of 30% to 200% for PL/SQL tables being passed as parameters. NOCOPY is the ideal hint for OUT and IN OUT parameters when the original value is not to be preserved (as is generally the case).

Here's an example:

create or replace procedure cpy_chk 
               (pi_val     in             varchar2, 
                po_dat     out    nocopy  date, 
                pio_status in out nocopy  varchar2) is 
   po_dat := sysdate; 
   pio_status := pio_status || 'amar is testing'; 


NOCOPY is a hint and Oracle does not guarantee a parameter will be passed by reference when explicitly mentioned. Here are some places where this is not possible:

  1. When the call is a remote procedure call
  2. When the actual parameter being passed is an expression
  3. When there is an implicit conversion involved

There may be other situations where Oracle may decide a call by value over a call by reference. Since this is not clearly specified, it is advisable not to build any process logic on this feature when exceptions being raised in the called routine are being trapped in the calling routine.

» See All Articles by Columnist Amar Kumar Padhi

Oracle Archives

Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM