Interested to Buy Any Domain ? << Click Here >> for more details...
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 a full join sql?
How to run sql commands in sql*plus?
What is compiled query?
i have a column which may contain this kind of value: 123*67_80,12*8889_5,34*8_874 ,12*7_7 (can contain space before a comma, and this string length can be anything) now i want to split this value into two column like: column1: 123*67,12*8889,34*8,12*7 column2: 80,5,874,7 use function for this
What is nested table in pl sql?
how to fetch common records from two tables? : Sql dba
what is data control language? : Sql dba
Mention what is the plv (pl/vision) package offers?
how to include numeric values in sql statements? : Sql dba
Is a foreign key always unique?
Explain some predefined exceptions.
Show how functions and procedures are called in a pl/sql block.
What is duration in sql profiler trace?
What is the difference between join and natural join?
What is the difference between inner join and left join?
