Answer Posted / anks

Prior to Oracle 8i releases, the IN OUT parameters in
PL/SQL routines were passed using the copy-in and copy-out
semantics (call by value).

For example:


create or replace procedure cpy_chk
(pi_val in varchar2,
po_dat out date,
pio_status in out varchar2) is
begin
po_dat := sysdate;
pio_status := pio_status || 'amar is testing';
end;
When the above code is executed, Oracle will copy the value
being passed to parameters PO_DAT and PIO_STATUS in a
separate buffer for the routine. On completion of the
routine, Oracle will copy the value being held in the
PO_DAT and PIO_STATUS parameters back to the original
variable. This results in multiple buffers being opened in
memory and the overhead of copying data back and forth.
This can be huge in terms of CPU and Memory overhead if the
parameter is meant for large strings or collection objects.
The IN parameter is passed by reference; that is, a pointer
to the actual IN parameter is passed to the corresponding
formal parameter. So, both parameters refer to the same
memory location and no copying overhead is involved.
However, the OUT and IN OUT parameters are passed by value.

To get around this, package variables were being used to
pass values around. Though serviceable as an alternative to
prevent multiple buffers and copy overhead, it resulted in
higher maintenance cost.

From Oracle 8i onwards, the NOCOPY parameter hint has been
introduced for OUT and IN OUT parameters. Using this hint
tells Oracle to make a call by reference. Use this hint
when there is no need to preserve the original value (in
case the called routine raises an exception). Oracle's
internal benchmark testing shows improvements of 30% to
200% for PL/SQL tables being passed as parameters. NOCOPY
is the ideal hint for OUT and IN OUT parameters when the
original value is not to be preserved (as is generally the
case).

Here's an example:


create or replace procedure cpy_chk
(pi_val in varchar2,
po_dat out nocopy date,
pio_status in out nocopy varchar2) is
begin
po_dat := sysdate;
pio_status := pio_status || 'amar is testing';
end;


Drawbacks
NOCOPY is a hint and Oracle does not guarantee a parameter
will be passed by reference when explicitly mentioned. Here
are some places where this is not possible:

When the call is a remote procedure call
When the actual parameter being passed is an expression
When there is an implicit conversion involved
There may be other situations where Oracle may decide a
call by value over a call by reference. Since this is not
clearly specified, it is advisable not to build any process
logic on this feature when exceptions being raised in the
called routine are being trapped in the calling routine.

shall we execute our java programmes in jre

2288

---

write algo for cobol program whichuse three flat file to extract some specific information 8 marks mainframe

2012

---

Explain polymorphism. Provide an example.

1034

---

Write a program to create a process in UNIX

2561

---

What is diffrent between Method and and function in c#

2019

---

5. Which of the following can you do with DB2 Express- C? Query databases with SQL Query databases with XML using XQuery and XPath Use SQL in Xquery and Xquery in SQL All of the above

2566

---

1. Consider the following code in our example assembly language: ; an example bit of assembly code ROOT: W FATHER FATHER: W SON1 W SON2 SON1: W NIL W NIL ; ---------------------- SON2: W GRANDSON W NIL GRANDSON: W NIL W NIL NIL = 0 Assemble this code carefully following the two pass model, and show the symbol table at the point marked by the dashed line during each pass.

2304

---

details description on this mantis? who is founder of this mantis?

2137

---

what are the activities you enjoy most and How do you see these Developing in the Future with Reference to in your work life and in your personal life

1478

---

I m Abdullah Ansari compleated MCA from Jamia Hamdard,i have appeared the test of IBM on 2 august at oxford college of engineering Bangalore.waiting for hr round.. This is the first round for IBM.02/08/08 Paper consists of 4 sections 15 questions from matrices(time very less but no negative marking). 25 questions from series completion section (this section is very easy but negative marking) 15 questions from aptitude(little bit tough time limit 15 minute negative marking) 4th section is from computer science (c,c++,operating system,digital electronics ,basic question..) result came at 3 o'clock.i was selected... In interview they asked questions like 1 they asked about final yr project.. 2 what are dynamic and static memory location? 3 linked list and array difference between them. 4 what is function ? what is difference betwen function and inline function? 5 about structure 6 about binary tree, traversal, call by value. 7 storage class and many more basic questions..

3306

---

Explain the types of operations? Draw the figure for shift and rotate operations?

3345

---

What is easiest way to get the PL/i compiler,I didn't have found the compiler in my library. Is there any extra cost if we want to access the PL/1 programs?Actually we r having Mainframe rented training Ids

2216

---

why we need to take u?

2120

---

code for connection from windows forms to sql server

2239

---

What is SOLID Principle in Programming Language?

1229

---