Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2012
    Posts
    7

    Unanswered: 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...

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    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.

  3. #3
    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

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    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.

  5. #5
    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 !!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •