Results 1 to 5 of 5

Thread: OUT parameters

  1. #1
    Join Date
    Oct 2003
    Location
    Brazil
    Posts
    30

    Question Unanswered: OUT parameters

    Hi buddies,

    I'm a bit confused with the definitions I read about OUT parameters, in Oracle PL/SQL references.

    I read that the actual parameter is passed by value, which means a copy of the value is passed out - but in my head this confronts the affirmative of having an out formal parameter being initialized to NULL. Is the actual value passed to the procedure or an out parameter will always start as NULL ?

    Let's consider a very simple code, like the following:

    CREATE or replace procedure test2 (var1 OUT varchar2) as
    BEGIN
    var1 := var1 || ' additional text';
    dbms_output.put_line(var1);
    END test2;

    DECLARE
    var1 varchar2(50) :='[test]';
    BEGIN
    dbms_output.put_line(var1);
    test2(var1);
    dbms_output.put_line(var1);
    END;


    The result I get is that var1, even though previously initialized on the calling program to "[test]" , enters test2 procedure with a NULL value, and the last dbms_output statement within the caller block prints only ' additional text'.

    So I guess every OUT variable is indeed initialized to NULL and we cannot receive any values from the caller if we are working with OUT parameters. Correct ?


    Thanks a lot,
    Abrahao, Brazil.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    Yes, an OUT parameter may ONLY return a value, an IN may only initilize a variable, and an IN OUT may do both.

    Code:
    CREATE or replace procedure test2 (var1 OUT varchar2,
                                                     var2 IN varchar2,
                                                     var3 IN OUT varchar2) as
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Also note that when an exception is raised from a called procedure, IN OUT and OUT parameters keep the values they had at the call of the procedure :

    Code:
    rbaraer@Ora10g> CREATE or replace procedure test (var1 IN varchar2, var2 IN OUT varchar2, var3 OUT varchar2, bException BOOLEAN) as
        iTmp Number;
      2  BEGIN
      3    4      var2 := var2 || ' additional text';
      5      var3 := var3 || ' additional text';
      6      dbms_output.put_line('var1 within procedure = '||var1);
        dbms_output.put_line('var2 within procedure = '||var2);
      7    8      dbms_output.put_line('var3 within procedure = '||var3);
      9      IF (bException) THEN
     10          iTmp := to_number('t'); -- Intentional to raise an exception
     11      END IF;
     12  END test;
     13  /
    
    Procedure created.
    
    rbaraer@Ora10g> DECLARE
    var1 varchar2(50) :='';
      2    3  var2 varchar2(50) :='';
      4  var3 varchar2(50) :='';
      5  BEGIN
      6  dbms_output.put_line('WITHOUT EXCEPTION');
      7  dbms_output.put_line('var1 before call = '||var1);
      8  dbms_output.put_line('var2 before call = '||var2);
      9  dbms_output.put_line('var3 before call = '||var3);
     10  test(var1, var2, var3, FALSE);
     11  dbms_output.put_line('var1 after call = '||var1);
     12  dbms_output.put_line('var2 after call = '||var2);
     13  dbms_output.put_line('var3 after call = '||var3);
     14
     15  var1 := '';
     16  var2 := '';
     17  var3 := '';
     18  dbms_output.put_line('WITH EXCEPTION');
     19  dbms_output.put_line('var1 before call = '||var1);
     20  dbms_output.put_line('var2 before call = '||var2);
     21  dbms_output.put_line('var3 before call = '||var3);
     22  test(var1, var2, var3, TRUE);
     23  dbms_output.put_line('var1 after call = '||var1);
     24  dbms_output.put_line('var2 after call = '||var2);
     25  dbms_output.put_line('var3 after call = '||var3);
     26  EXCEPTION
     27      WHEN OTHERS THEN
     28          dbms_output.put_line('WITHIN EXCEPTION MANAGEMENT');
     29          dbms_output.put_line('var1 after call = '||var1);
     30          dbms_output.put_line('var2 after call = '||var2);
     31          dbms_output.put_line('var3 after call = '||var3);
     32  END;
     33  /
    WITHOUT EXCEPTION
    var1 before call =
    var2 before call =
    var3 before call =
    var1 within procedure =
    var2 within procedure =  additional text
    var3 within procedure =  additional text
    var1 after call =
    var2 after call =  additional text
    var3 after call =  additional text
    WITH EXCEPTION
    var1 before call =
    var2 before call =
    var3 before call =
    var1 within procedure =
    var2 within procedure =  additional text
    var3 within procedure =  additional text
    WITHIN EXCEPTION MANAGEMENT
    var1 after call =
    var2 after call =
    var3 after call =
    
    PL/SQL procedure successfully completed.
    
    rbaraer@Ora10g>
    HTH & Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    Quote Originally Posted by RBARAER
    Also note that when an exception is raised from a called procedure, IN OUT and OUT parameters keep the values they had at the call of the procedure
    Unless you use the NOCOPY compiler hint

  5. #5
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Quote Originally Posted by WilliamR
    Unless you use the NOCOPY compiler hint
    Code:
    rbaraer@Ora10g> CREATE or replace procedure test (var1 IN varchar2, var2 IN OUT varchar2, var3 OUT varchar2, var4 IN OUT NOCOPY varchar2, var5 OUT NOCOPY varchar2, bException BOOLEAN) as
        iTmp Number;
    BEGIN
      2    3    4      var2 := var2 || ' additional text';
      5      var3 := var3 || ' additional text';
      6      var4 := var4 || ' additional text';
      7      var5 := var5 || ' additional text';
      8      dbms_output.put_line('var1 within procedure = '||var1);
      9      dbms_output.put_line('var2 within procedure = '||var2);
     10      dbms_output.put_line('var3 within procedure = '||var3);
     11      dbms_output.put_line('var4 within procedure = '||var4);
     12      dbms_output.put_line('var5 within procedure = '||var5);
     13      IF (bException) THEN
     14          iTmp := to_number('t'); -- Intentional to raise an exception
     15      END IF;
     16  END test;
     17  /
    
    Procedure created.
    
    rbaraer@Ora10g> DECLARE
    var1 varchar2(50) :='';
      2  var2 varchar2(50) :='';
      3    4  var3 varchar2(50) :='';
      5  var4 varchar2(50) :='';
      6  var5 varchar2(50) :='';
      7  BEGIN
      8  dbms_output.put_line('WITHOUT EXCEPTION');
      9  dbms_output.put_line('var1 before call = '||var1);
     10  dbms_output.put_line('var2 before call = '||var2);
     11  dbms_output.put_line('var3 before call = '||var3);
     12  dbms_output.put_line('var4 before call = '||var4);
     13  dbms_output.put_line('var5 before call = '||var5);
     14  test(var1, var2, var3, var4, var5, FALSE);
     15  dbms_output.put_line('var1 after call = '||var1);
     16  dbms_output.put_line('var2 after call = '||var2);
     17  dbms_output.put_line('var3 after call = '||var3);
     18  dbms_output.put_line('var4 after call = '||var4);
     19  dbms_output.put_line('var5 after call = '||var5);
     20
     21  var1 := '';
     22  var2 := '';
     23  var3 := '';
     24  var4 := '';
     25  var5 := '';
     26  dbms_output.put_line('WITH EXCEPTION');
     27  dbms_output.put_line('var1 before call = '||var1);
     28  dbms_output.put_line('var2 before call = '||var2);
    dbms_output.put_line('var3 before call = '||var3);
     29   30  dbms_output.put_line('var4 before call = '||var4);
     31  dbms_output.put_line('var5 before call = '||var5);
    test(var1, var2, var3, var4, var5, TRUE);
     32   33  dbms_output.put_line('var1 after call = '||var1);
     34  dbms_output.put_line('var2 after call = '||var2);
     35  dbms_output.put_line('var3 after call = '||var3);
     36  dbms_output.put_line('var4 after call = '||var4);
     37  dbms_output.put_line('var5 after call = '||var5);
     38  EXCEPTION
     39      WHEN OTHERS THEN
     40          dbms_output.put_line('WITHIN EXCEPTION MANAGEMENT');
     41          dbms_output.put_line('var1 after call = '||var1);
     42          dbms_output.put_line('var2 after call = '||var2);
     43          dbms_output.put_line('var3 after call = '||var3);
     44          dbms_output.put_line('var4 after call = '||var4);
     45          dbms_output.put_line('var5 after call = '||var5);
     46  END;
     47  /
    WITHOUT EXCEPTION
    var1 before call =
    var2 before call =
    var3 before call =
    var4 before call =
    var5 before call =
    var1 within procedure =
    var2 within procedure =  additional text
    var3 within procedure =  additional text
    var4 within procedure =  additional text
    var5 within procedure =  additional text
    var1 after call =
    var2 after call =  additional text
    var3 after call =  additional text
    var4 after call =  additional text
    var5 after call =  additional text
    WITH EXCEPTION
    var1 before call =
    var2 before call =
    var3 before call =
    var4 before call =
    var5 before call =
    var1 within procedure =
    var2 within procedure =  additional text
    var3 within procedure =  additional text
    var4 within procedure =  additional text
    var5 within procedure =  additional text
    WITHIN EXCEPTION MANAGEMENT
    var1 after call =
    var2 after call =
    var3 after call =
    var4 after call =  additional text
    var5 after call =  additional text
    
    PL/SQL procedure successfully completed.
    
    rbaraer@Ora10g>
    You win .

    Thanks for the information, I hadn't realized that. I almost never use NOCOPY.

    Regards,

    rbaraer
    ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .

Posting Permissions

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