|
|
|
|
Re: how cani pass reference varibles in a procedure [message #231198 is a reply to message #231153] |
Sun, 15 April 2007 22:33 |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
William Robertson wrote on Sun, 15 April 2007 18:02 | Passing by reference is the default behaviour for parameters in PL/SQL.
|
Actually, I think pass by value is the default behaviour:
SQL> create or replace procedure test (a number) is
2 begin
3 a := 1;
4 end;
5 /
Warning: Procedure created with compilation errors.
SQL> show err
Errors for PROCEDURE TEST:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/3 PL/SQL: Statement ignored
3/3 PLS-00363: expression 'A' cannot be used as an assignment target
SQL> create or replace procedure test (a in out number) is
2 begin
3 a := 1;
4 end;
5 /
Procedure created.
SQL>
So for the "feel" of pass by reference - where updates to variables within the procedure are visible upon completion of the procedure - you specify IN OUT in the parameter specification (as above).
But this is not true pass by reference. It makes a copy of the parameter, modifies it, then copies it back over the original when the procedure completes.
To achieve true pass by reference, you need to include the NOCOPY modifier in the paramter declaration to force PL/SQL to re-use the same memory location for updates. The example below demonstrates how the paramter is only updated upon successful exit of the procedure.
SQL> create or replace procedure test (a in out varchar2) is
2 begin
3 dbms_output.put_line('Before Assignment - Value is :' || a);
4 a:= 'UPDATED';
5 dbms_output.put_line('After Assignment - Value is :' || a);
6 raise_application_error(-20000, 'Test Error');
7 end;
8 /
Procedure created.
SQL>
SQL> declare
2 test_a varchar2(20) := 'original';
3 begin
4 dbms_output.put_line('Before Procedure - Value is :' || test_a);
5 begin
6 test(test_a);
7 exception
8 when others then null;
9 end;
10
11 dbms_output.put_line('After Procedure - Value is :' || test_a);
12 end;
13 /
Before Procedure - Value is :original
Before Assignment - Value is :original
After Assignment - Value is :UPDATED
After Procedure - Value is :original
PL/SQL procedure successfully completed.
Now in this one we use NOCOPY, and see that the paramter is updated even in the event of an error.
SQL>
SQL> create or replace procedure test (a in out nocopy varchar2) is
2 begin
3 dbms_output.put_line('Before Assignment - Value is :' || a);
4 a:= 'UPDATED';
5 dbms_output.put_line('After Assignment - Value is :' || a);
6 raise_application_error(-20000, 'Test Error');
7 end;
8 /
Procedure created.
SQL>
SQL> declare
2 test_a varchar2(20) := 'original';
3 begin
4 dbms_output.put_line('Before Procedure - Value is :' || test_a);
5 begin
6 test(test_a);
7 exception
8 when others then null;
9 end;
10
11 dbms_output.put_line('After Procedure - Value is :' || test_a);
12 end;
13 /
Before Procedure - Value is :original
Before Assignment - Value is :original
After Assignment - Value is :UPDATED
After Procedure - Value is :UPDATED
PL/SQL procedure successfully completed.
Ross Leishman
|
|
|
|
Re: how cani pass reference varibles in a procedure [message #238255 is a reply to message #231153] |
Thu, 17 May 2007 04:00 |
hobbes
Messages: 173 Registered: January 2006
|
Senior Member |
|
|
William Robertson wrote on Sun, 15 April 2007 03:02 | Passing by reference is the default behaviour for parameters in PL/SQL.
|
A little late on this but I figured I'd rather clarify than live in confusion.
Am I right when I say:
- IN is passed by REFERENCE
- IN OUT is passed by VALUE
- OUT is returned by VALUE
- IN OUT and OUT, when modified by NOCOPY, are passed by REFERENCE
|
|
|
Re: how cani pass reference varibles in a procedure [message #238275 is a reply to message #238255] |
Thu, 17 May 2007 05:12 |
William Robertson
Messages: 1643 Registered: August 2003 Location: London, UK
|
Senior Member |
|
|
hobbes wrote on Thu, 17 May 2007 10:00 | Am I right when I say:
- IN is passed by REFERENCE
- IN OUT is passed by VALUE
- OUT is returned by VALUE
- IN OUT and OUT, when modified by NOCOPY, are passed by REFERENCE
|
Yes, although NOCOPY is a hint that is ignored in a couple of cases (passing values across DB links and suchlike IIRC).
|
|
|