Answer / 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

Answer / santanu

NO COPY is compiler hint that process at run time not
compile time. this clause are use for pass the actual out
or in out parameter value as a call by referance insted of
call by value. call by value (mode out/inout) are copy the
value of actual parameter to the formal parameter if the
passing value is a large object it will take vary large
memory and it effect the performence.

create procedule (x in varchar2,y out nocopy varchar2)

Answer / suresh kumar somayajula

OUT and IN OUT parameters passed by reference which avoid
COPY overhead.

Answer / rahul k.

very nicely explained by Dinesh...

Answer / surbhi

Well the answer as i browsed was this regarding NOCOPY:

The automatic transfer of data from the internal data
memory to the memory card can be prevented by the command

NOCOPY

which instructs the dataTaker not to transfer data from the
internal data memory to the data area of the inserted
memory card. The NOCOPY state remains active until the
memory card which contains the command is removed. When
another memory card is inserted, automatic data transfer is
again enabled.

The following example illustrates the use of the NOCOPY
command in a card program. This program is stored in the
program area of a memory card.

;NOCOPY
;ALARM5(1TK>100)"Temperature Alarm"
;ALARM6(2TK>105)AND
;ALARM7(3TK<140)4DSO

The NOCOPY command is used in this program to instruct the
logger not to transfer data from the internal data memory
to the memory card at any time while this memory card is
inserted in the logger.

This programming example is typical of a memory card being
used as a program only card, to enter new alarms or alarm
setpoints, change Schedule triggers, etc. while the current
program continues.

Answer / payal

well is it mean NCOPY command ..?

What is left inner join in sql?

0 Answers  

How to generate a salary slip like jan 1000 1000 feb 1000 2000 ... dec 1000 12000

0 Answers   BT,

What is pl sql package?

0 Answers  

What is difference between triggers and stored procedures. And advantages of SP over triggers ?

4 Answers   Microsoft, TCS,

Is sql the best database?

0 Answers  

How we can update the view?

0 Answers  

Explain dml and ddl?

0 Answers  

what are the 'mysql' command line arguments? : Sql dba

0 Answers  

How can check sql version from command line?

0 Answers  

What is window clause?

1 Answers   TCS,

Mention what does plv msg allows you to do?

0 Answers  

What is sql basics?

0 Answers