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.
| Is This Answer Correct ? | 2 Yes | 0 No |
Post New Answer View All Answers
SELECT A~FTRMI A~AUFNR B~MATNR D~MAKTX B~PSMNG B~WEMNG C~MTART E~CHARG C~MATKL C~SPART R~BWART R~CHARG R~MATNR E~BWART R~BDMNG INTO TABLE ITAB FROM AFKO AS A INNER JOIN AFPO AS B ON A~AUFNR = B~AUFNR * INNER JOIN AFVC AS A1 ON A~AUFPL = A1~AUFPL AND B~DWERK = A1~WERKS INNER JOIN MARA AS C ON B~MATNR = C~MATNR INNER JOIN MAKT AS D ON C~MATNR = D~MATNR INNER JOIN MSEG AS E ON B~MATNR = E~MATNR AND A~AUFNR = E~AUFNR AND B~DWERK = E~WERKS INNER JOIN RESB AS R ON A~AUFNR = R~AUFNR AND E~AUFNR = R~AUFNR AND R~RSNUM = A~RSNUM AND R~WERKS = E~WERKS AND R~BAUGR = E~MATNR INNER JOIN MARA AS C1 ON R~MATNR = C1~MATNR WHERE A~FTRMI IN S_DATE AND A~AUFNR IN S_AUFNR AND C~MTART IN S_TYPE AND C~MATKL = 'T' AND R~BWART ='261' AND E~BWART = '101' AND R~XWAOK ='X' AND B~DWERK = '2000' AND R~XLOEK EQ SPACE AND E~KZBEW ='F' AND D~SPRAS = 'E' AND R~KZEAR = 'X' AND C~MATNR IN S_MATNR. This query gives repetative records from RESB.There must be some common field which i am not taking thats why its giving repetative records. If u find any solution pls lety me know ASAP. Thanks Swati Ghadge
How can we develop a multi-tier application in Java?
How to merge Action Form with Dyna Action Form in Struts.
I have try to write a record in a TDQ from a fle.... what are the steps to do... can anybody plz come with a solution
Hi can you please send me recent(present) interview questions and technical qyestions with answers for "BUSSIESS OBJECTS" and "DATA WAREHOUSE".pls its urgent for me my mail id is sekhar.cs82@gmail.com,manjuforgis@gmail.com. thanks in advance
What is BASIS
what is throws keyword
why we use mantis? what u mean mantis in IT trends? addvantages of mantis?
What is the difference between CLEAR & RESET and OPEN & CLOSE OPCOEDS(USING RPG/400).wheare we can use this?can any body tell me in real time senario with example please?
3 members in a pf.how we read 3 members without using ovrdbf.using rg pgms....
Given an array of size n. It contains numbers in the range 1 to n. Find the numbers which aren?t present.
< No Frames > tag is used for
Can any one give an example (Source Code) on virtual function implemetation in Java?
what is the difference between rename and label
Given a Binary Search Tree, write a program to print the kth smallest element without using any static/global variable. You can?t pass the value k to any function also.
