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
Explain sql data types?
How delete all data from all tables in sql?
What is the syntax to add a record to a table?
what is the difference between union and union all? : Sql dba
What is difference between stored procedures and application procedures?
Why cross join is used?
what are the maximum number of rows that can be constructed by inserting rows directly in value list? : Transact sql
Can we use distinct and group by together?
What is normalization? How many normalization forms are there?
What are the parameter modes supported by pl/sql?
What is rename in sql?
What are the operators in sql?
Can we create table inside stored procedure?
What do you mean by field in sql?
What is pl sql record in oracle?
