If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > OUT parameter in a procedure

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 01-30-12, 17:50
kalp kalp is offline
Registered User
 
Join Date: Jan 2012
Posts: 7
OUT parameter in a procedure

Hi guys, am confused about using OUT parameter in pl/sql..as seen on this oracle docs page :-

http://http://docs.oracle.com/cd/B19...s_packages.htm

It says while using OUT parameter, Formal parameter cannot be used in an expression..we know that IN parameter cannot be used as assignment target, but can be used to assign a value, here what is mentioned about OUT parameter is that it cannot be used to assign a value, but please see this code , here y variable is assigned a value using x formal parameter....

Code:
CREATE OR REPLACE procedure proccer1 (n in varchar2, p in number, x out number)
 as
 Y NUMBER;
 cursor firstcur is
  select * from temp;
  cur_val firstcur%rowtype;
 cur_val_point  cur_val.point%type;
  begin
 insert into temp values (n, p);
 
 open firstcur;
 loop
 fetch firstcur into cur_val;
 exit when firstcur%NOTFOUND;
 insert into t values (cur_val.point);
 end loop;
 X := N + 10;
 dbms_output.put_line ('the output value : ' || x);
 Y := x + 10;
 dbms_output.put_line ('now new output value : ' || Y);
 close firstcur;
end;
so what is it which is mentioned on that oracle docs page whose link i have given above..?

thanks for the help...
Reply With Quote
  #2 (permalink)  
Old 01-31-12, 09:50
beilstwh beilstwh is offline
Lead Application Develope
 
Join Date: Jun 2004
Location: Liverpool, NY USA
Posts: 2,222
What they are talking about is the following. If your OUT parameter is X then

X := 3;

Is legal.

Y := X + 3;

Is not legal. You can assign a value to an out parameter, but you can't use it to the right of the equal sign.
__________________
Bill
You do not need a parachute to skydive. You only need a parachute to skydive twice.
Reply With Quote
  #3 (permalink)  
Old 01-31-12, 13:46
kalp kalp is offline
Registered User
 
Join Date: Jan 2012
Posts: 7
Quote:
Originally Posted by beilstwh View Post
What they are talking about is the following. If your OUT parameter is X then

X := 3;

Is legal.

Y := X + 3;

Is not legal. You can assign a value to an out parameter, but you can't use it to the right of the equal sign.

Hi Beilstwh, thanks for replying! here, i also understood the same.. but have a look at the code i mentioned above, 'y' variable is outputted there after adding 10 to 'x' formal parameter....so it means once we assign a value to formal parameter,after that it can be used in an expression? thanks again
Reply With Quote
  #4 (permalink)  
Old 01-31-12, 15:13
Littlefoot Littlefoot is offline
Lost Boy
 
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,629
OK, so here's a small test case. I removed cursors and other unnecessary stuff (besides, procedure you posted is kind of strange - why do you add CHAR (N) to a number (10)?

Code:
SQL> create or replace procedure prc_1 (n in number, x out number) as
  2    y number;
  3  begin
  4    x := n + 10;
  5    dbms_output.put_line ('the output value : ' || x);
  6
  7    Y := x + 10;
  8    dbms_output.put_line ('now new output value : ' || Y);
  9  end;
 10  /

Procedure created.

SQL>
SQL> declare
  2    l_out number;
  3  begin
  4    prc_1 (100, l_out);
  5    dbms_output.put_line('Value returned from a procedure : ' || l_out);
  6  end;
  7  /
the output value : 110
now new output value : 120
Value returned from a procedure : 110

PL/SQL procedure successfully completed.

SQL>
It appears that everything is OK.
Reply With Quote
  #5 (permalink)  
Old 02-02-12, 17:31
kalp kalp is offline
Registered User
 
Join Date: Jan 2012
Posts: 7
Hi Littlefoot, thanks for replying and explaining..yes everything is okay in that code & the code which i wrote is also executing fine. I was just confused about what is mentioned in the link i gave in my question..in that link if you see table 7-2, there it's mentioned that 'Formal parameter cannot be used in an expression'.... As to what is mentioned in that table about 'IN' parameter & 'OUT' parameter, this above line('formal parameter.....) got me confused that an 'OUT' parameter would not be used to assign a value, but now am confirmed that it can be used to assign a value as well as can be assigned a value..okay dont wanna confuse anybody else ..... so anyways thanks for the help !!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On