Home » SQL & PL/SQL » SQL & PL/SQL » how cani pass reference varibles in a procedure
how cani pass reference varibles in a procedure [message #223777] Sun, 11 March 2007 05:55 Go to next message
mkr_oracle
Messages: 6
Registered: March 2007
Location: chennai
Junior Member
hi,

plz help me how cani pass reference variables in procedure.


regards,

mohan
Re: how cani pass reference varibles in a procedure [message #231135 is a reply to message #223777] Sat, 14 April 2007 22:23 Go to previous messageGo to next message
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
Senior Member
#include

main()
{
int var1;
int &var2 = var1; // var2 is a reference variable.

var1 = 10;

cout << "var1 = " << var1 << endl;
cout << "var2 = " << var2 << endl;
}
Re: how cani pass reference varibles in a procedure [message #231141 is a reply to message #231135] Sun, 15 April 2007 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Laughing Good one!
Re: how cani pass reference varibles in a procedure [message #231153 is a reply to message #223777] Sun, 15 April 2007 03:02 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Passing by reference is the default behaviour for parameters in PL/SQL.
Re: how cani pass reference varibles in a procedure [message #231198 is a reply to message #231153] Sun, 15 April 2007 22:33 Go to previous messageGo to next message
rleishman
Messages: 3724
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 #231211 is a reply to message #231198] Mon, 16 April 2007 00:43 Go to previous messageGo to next message
William Robertson
Messages: 1640
Registered: August 2003
Location: London, UK
Senior Member
Well yes IN parameters are read-only. They are still passed by reference and that is still the default.
Re: how cani pass reference varibles in a procedure [message #238255 is a reply to message #231153] Thu, 17 May 2007 04:00 Go to previous messageGo to next message
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 Go to previous message
William Robertson
Messages: 1640
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).
Previous Topic: BulkCollect
Next Topic: Unix command through external procedure in C running successfully but showing no output.
Goto Forum:
  


Current Time: Sat Dec 10 12:51:04 CST 2016

Total time taken to generate the page: 0.08493 seconds