Parameter aliasing, PL-SQL Programming

Assignment Help:

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.

DECLARE

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

BEGIN

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

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

END;

BEGIN

lexicon.EXTEND;

add_entry(lexicon);

DBMS_OUTPUT.PUT_LINE(lexicon(1).word);

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

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

END;

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.

DECLARE

n NUMBER := 10;

PROCEDURE do_something (

n1 IN NUMBER,

n2 IN OUT NUMBER,

n3 IN OUT NOCOPY NUMBER) IS

BEGIN

n2 := 20;

DBMS_OUTPUT.PUT_LINE(n1); -- prints 10

n3 := 30;

DBMS_OUTPUT.PUT_LINE(n1); -- prints 30

END;

BEGIN

do_something(n, n, n);

DBMS_OUTPUT.PUT_LINE(n); -- prints 20

END;

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;

BEGIN

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

...

END;


Related Discussions:- Parameter aliasing

Defining records, Defining and Declaring Records To create records, yo...

Defining and Declaring Records To create records, you have to define a RECORD type, and then declare records of that type. You may also define RECORD types in the declarative

Avoid the not null constraint - performance of application, Avoid the NOT N...

Avoid the NOT NULL Constraint In the PL/SQL, using the NOT NULL constraint incur a performance cost. Consider the illustration as shown below: PROCEDURE calc_m IS m NUMB

Declaring subprograms, Declaring Subprograms   You can declare subprog...

Declaring Subprograms   You can declare subprograms in any PL/SQL subprogram, block, or package. But, you should declare subprograms at the end of the declarative part after a

Cause of indeterminacy in sql, Cause of Indeterminacy in SQL One root ...

Cause of Indeterminacy in SQL One root cause of indeterminacy in SQL lies in its implementation of comparison for equality. For certain system-defined types it is possible for

Delimiters, Delimiters A delimiter is a simple or compound symbol whi...

Delimiters A delimiter is a simple or compound symbol which has a special meaning to PL/SQL. For example, you use delimiters to symbolize an arithmetic operation like additio

Assignment statement in pl sql, Assignment Statement: The assignment s...

Assignment Statement: The assignment statement sets the present value of the variable, parameter, field, or element. The statement consists of an assignment target followed by

Keyword & parameter description - exception_init pragma, Keyword & Paramete...

Keyword & Parameter Description: PRAGMA: These keywords signify that the statement is a pragma (i.e. compiler directive). The Pragmas are processed at the compile time, n

Library system, Hi,am developing a library system and relating all the tabl...

Hi,am developing a library system and relating all the table is somehow complex,could you kindly assist me

Semidifference and not - sql, Semidifference and NOT - SQL In this sec...

Semidifference and NOT - SQL In this section first describe the relational difference operator, named MINUS. Example here shows SQL's closest counterpart of that operator.

Advantages of pl/sql, Advantages of PL/SQL The PL/SQL is a high-perform...

Advantages of PL/SQL The PL/SQL is a high-performance transaction processing, completely portable language that offers the following advantages as shown: 1) Support for SQL

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd