Answer Posted / dinesh mishra
Suppose a subprogram declares an IN parameter, an OUT
parameter, and an IN OUT parameter. When you call the
subprogram, the IN parameter is passed by reference. That
is, a pointer to the IN actual parameter is passed to the
corresponding formal parameter. So, both parameters
reference the same memory location, which holds the value
of the actual parameter.
By default, the OUT and IN OUT parameters are passed by
value. That is, the value of the IN OUT actual parameter is
copied into the corresponding formal parameter. Then, if
the subprogram exits normally, the values assigned to the
OUT and IN OUT formal parameters are copied into the
corresponding actual parameters.
When the parameters hold large data structures such as
collections, records, and instances of object types, all
this copying slows down execution and uses up memory. To
prevent that, you can specify the NOCOPY hint, which allows
the PL/SQL compiler to pass OUT and IN OUT parameters by
reference.
In the following example, you ask the compiler to pass IN
OUT parameter my_staff by reference instead of by value:
DECLARE
TYPE Staff IS VARRAY(200) OF Employee;
PROCEDURE reorganize (my_staff IN OUT NOCOPY Staff)
IS ...
Remember, NOCOPY is a hint, not a directive. So, the
compiler might pass my_staff by value despite your request.
Usually, however, NOCOPY succeeds. So, it can benefit any
PL/SQL application that passes around large data
structures.
In the example below, 5000 records are loaded into a local
nested table, which is passed to two local procedures that
do nothing but execute NULL statements. However, a call to
one procedure takes 26 seconds because of all the copying.
With NOCOPY, a call to the other procedure takes much less
than 1 second.
SQL> SET SERVEROUTPUT ON
SQL> GET test.sql
1 DECLARE
2 TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE;
3 emp_tab EmpTabTyp := EmpTabTyp(NULL); -- initialize
4 t1 CHAR(5);
5 t2 CHAR(5);
6 t3 CHAR(5);
7 PROCEDURE get_time (t OUT NUMBER) IS
8 BEGIN SELECT TO_CHAR(SYSDATE,'SSSSS') INTO t FROM
dual; END;
9 PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS
10 BEGIN NULL; END;
11 PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp)
IS
12 BEGIN NULL; END;
13 BEGIN
14 SELECT * INTO emp_tab(1) FROM emp WHERE empno =
7788;
15 emp_tab.EXTEND(4999, 1); -- copy element 1 into
2..5000
16 get_time(t1);
17 do_nothing1(emp_tab); -- pass IN OUT parameter
18 get_time(t2);
19 do_nothing2(emp_tab); -- pass IN OUT NOCOPY
parameter
20 get_time(t3);
21 DBMS_OUTPUT.PUT_LINE('Call Duration (secs)');
22 DBMS_OUTPUT.PUT_LINE('--------------------');
23 DBMS_OUTPUT.PUT_LINE('Just IN OUT: ' || TO_CHAR(t2 -
t1));
24 DBMS_OUTPUT.PUT_LINE('With NOCOPY: ' || TO_CHAR(t3 -
t2));
25* END;
SQL> /
Call Duration (secs)
--------------------
Just IN OUT: 26
With NOCOPY: 0
| Is This Answer Correct ? | 20 Yes | 1 No |
Post New Answer View All Answers
What is view explain with example?
What is the purpose of normalization?
Explain select statements in sql?
Whis is not false in primary key?
How do I run a pl sql procedure in sql developer?
What is a .db file?
Explain 3 basic parts of a trigger.
Which join condition can be specified using on clause?
What is the use of primary key?
What is %rowtype in pl sql?
How does a self join work?
What is sql in java?
what are the advantages of using stored procedures? : Sql dba
What are the two different parts of the pl/sql packages?
Can a table contain multiple primary key’s?
