Parameter aliasing, PL-SQL Programming

Parameter Aliasing 

To optimize the subprogram call, the PL/SQL compiler can decide between the two techniques of the parameter passing. With the by-value techniques, the value of a real parameter is passed to the subprogram. With the by-reference techniques, only a pointer to the value is passed, in that case the actual and formal parameters reference the similar item.

The NOCOPY compiler hint increases the possibility of aliasing (i.e. having the two different names refer to the similar memory location). This can happen when a global variable appears as the actual parameter in a subprogram call and then is referenced within the subprogram. The result is indeterminate as it depends on the technique of parameter passing chosen by the compiler.

In the illustration below, the procedure add_entry refers to varray lexicon in two various ways: as the parameter and as a global variable. Therefore, if add_entry is called, the identifiers word_list & lexicon name the similar varray.


TYPE Definition IS RECORD (

word VARCHAR2(20),

meaning VARCHAR2(200));

TYPE Dictionary IS VARRAY(2000) OF Definition;

lexicon Dictionary := Dictionary();

PROCEDURE add_entry (word_list IN OUT NOCOPY Dictionary) IS


word_list(1).word := 'aardvark';

lexicon(1).word := 'aardwolf';






-- prints 'aardvark' if parameter was passed by value

-- prints 'aardwolf' if parameter was passed by reference


The output depends on the technique of parameter passing chosen by the compiler. If the compiler chooses the by-value technique, word_list and lexicon are individual copies of the similar varray. Therefore, changing one does not affect the other. Whereas, if the compiler chooses the by-reference technique, word_list and lexicon are merely different names for the similar varray. (And Hence, the word "aliasing.")

The Aliasing can also occur if similar actual parameter appears more than once in a subprogram call. In the illustration below, n2 is an IN OUT parameter, therefore the value of the actual parameter is not updated till the procedure exits. This is why the first PUT_LINE prints 10 (the initial value of n) and the third PUT_LINE prints 20.

Though, n3 is a NOCOPY parameter, for this reason the value of the actual parameter is updated instantly. That is why the second PUT_LINE prints 30.


n NUMBER := 10;

PROCEDURE do_something (





n2 := 20;

DBMS_OUTPUT.PUT_LINE(n1); -- prints 10

n3 := 30;

DBMS_OUTPUT.PUT_LINE(n1); -- prints 30



do_something(n, n, n);

DBMS_OUTPUT.PUT_LINE(n); -- prints 20


As they are pointers, the cursor variables also increase the possibility of the aliasing. Consider the illustration below. Later the assignment, emp_cv2 is an alias of the emp_cv1 as both points to the similar query work region. Therefore, both can alter its position. So are why the first fetch from emp_cv2 fetches the third row and why the second fetch from emp_cv2 fails after you close emp_cv1.

PROCEDURE get_emp_data (

emp_cv1 IN OUT EmpCurTyp,

emp_cv2 IN OUT EmpCurTyp) IS

emp_rec emp%ROWTYPE;


OPEN emp_cv1 FOR SELECT * FROM emp;

emp_cv2 := emp_cv1;

FETCH emp_cv1 INTO emp_rec; -- fetches first row

FETCH emp_cv1 INTO emp_rec; -- fetches second row

FETCH emp_cv2 INTO emp_rec; -- fetches third row

CLOSE emp_cv1;

FETCH emp_cv2 INTO emp_rec; -- raises INVALID_CURSOR



Posted Date: 10/5/2012 5:37:47 AM | Location : United States

Related Discussions:- Parameter aliasing, Assignment Help, Ask Question on Parameter aliasing, Get Answer, Expert's Help, Parameter aliasing Discussions

Write discussion on Parameter aliasing
Your posts are moderated
Related Questions
SQL outer join SELECT * FROM IS_CALLED NATURAL LEFT JOIN IS_ENROLLED_ON Note that adding LEFT to an invocation of CROSS JOIN has no effect unless the right-hand operand

Explicitly specifying the join condition - SQL SELECT * FROM IS_CALLED JOIN IS_ENROLLED_ON ON ( IS_CALLED.StudentId = IS_ENROLLED_ON.StudentId ) Now, the key word JO

Type versus Representation Confusion in SQL This describes how a value might have two or more distinct representations. For example, user-defined type POINT might have a decla

Write a pl/sql block that declares and uses cursors with parameters. In a loop, use a cursor to retrieve the department number and the department name from the departments table

SQL Operators The PL/SQL uses all the SQL set, comparison, and row operators in the SQL statements. This part briefly describes some of these operators.  1. Comparison Opera

Organi c Evolution Evolution is a slow continuous, irreversible and natural process of change to give rise to advance and diverse forms of life i.e. formation of new specie

Using Aggregation on Nested Tables Example is the most direct translation of its counterpart in the theory book that can be obtained in SQL but it is so over-elaborate that no

NULL Statement The NULL statement clearly specifies in action; it does nothing other than to pass control to the next statement. It can, though, improve the readability. In a

Declaring a Cursor The Forward references are not allowed in the PL/SQL. Therefore, you must declare a cursor before referencing it in other statements. Whenever you declare a

Authorisations - Privileges As relational theory is silent on the issue of authorisation, it offers nothing with which SQL's vast edifice in support of what it calls privilege