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
List out the acid properties and explain?
Can a key be both primary and foreign?
Can we use view in stored procedure?
Why is normalization important?
what is error ora-03113: end-of-file on communication channel?
What is query optimization in sql?
What is pl sql block structure?
What are the qualities of 2nf?
Are there any features that are decommissioned in 11g that are not present in 11g?
Is sql procedural language?
What is procedure explain with program?
how is exception handling handled in mysql? : Sql dba
How do I filter in sql profiler?
How are sql commands classified?
how to return query output in html format? : Sql dba
