Answer / shivaindu

When you use a Collection (Records, VARRAY etc having
records of data) as a Return type (OUT or IN OUT parameter)
as we know, it will be passed by value only. That means the
entire data will be copied to the formal parameter location,
which leads to lot of CPU and memory consumption.

Here comes the usage of NOCOPY parameter. Provide NOCOPY
after the OUT/IN OUT parameter, which instructs the PL/SQL
Engine to avoid using the pass by value method. So, the
Engine uses pass by reference instead. This helps reducing a
lot of CPU usage as well as memory consumption.
Eg: Just an example how to put the NOCOPY parameter.

PROCEDURE PRC_NOCOPY_EXAMPLE
(
P_EMPNO IN
NUMBER,
P_ENAME IN
VARCHAR2(50),
P_SAL OUT NOCOPY
RECORD_SAL --PL/SQL Record
);
REC_SAL RECORD_SAL;

Now if you call the Procedure PRC_NOCOPY_EXAMPLE, the
parameter P_SAL would only use Pass by reference method.
Disadvantage:
However, you need to be extremely careful while
using this method in coding for high priority data
transactions. When you pass parameters to a procedure by
reference, any change that happens on the passed Parameters
gets updated in the same memory location as the Actual
parameter. So when an exception occurs in a
procedure/function, these changes are not Rolled back . To
be precise, the PL/SQL engine cannot rollback these
parameter changes. So, if the actual values get changed,
then it may result in incorrect results.
Thus, to conclude, it is up to a Developer to take a
trade-off between using or not using the NOCOPY parameter.
NOCOPY could provide better performance by reducing Memory
and CPU cost, but at the same time, could result in
incorrect results too.

Answer / suresh somayajula

OUT and IN OUT Parameters which will avoid copy overhead.so we will use NOCOPY.

i have a table emp and columns ename,empno,mgr_id,i need ename,manager name as result i.e employee respective manager.. example empno ename mgr_id 1 john 3 2 paul 3 3 smith 1 4 kevin 1 5 stewart 2 result has to look like this ename manager john smith paul smith smith john kevin john stewart paul can u plz help me out in this.....

8 Answers  

---

What is dynamic SQl and how you will create and execute dynamic sql?

2 Answers   TCS,

---

Easy way to convert tableau "IF - ELSEIF" statements to Netezza "CASE" statements.

1 Answers   CTS,

---

How do temporal tables work?

0 Answers  

---

What is left inner join in sql?

0 Answers  

---

How to delete same emp id in sql query for exmaple in emp table emp id, empname, emp address. for example emp id =5, repeated in two times in rows same id how to delete same empid please any one of the write query send in my id

8 Answers   CGCI,

---

Is left join faster than join?

0 Answers  

---

I have a CURSOR then why we need BULK COLLECT again?

1 Answers  

---

what are numeric data types? : Sql dba

0 Answers  

---

how to remove records from table? no name 1 a 2 b 1 a 2 b 3 c

8 Answers   Oracle,

---

How to get list of all tables from a database?

0 Answers  

---

In table three columns with 1 milion records(here there is no sequence values) i want add one more column with sequence values from the first how it is posible?

4 Answers   Tech Soft,

---