Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    1,074

    Unanswered: Body Variables retain value?

    So, I've been operating under the assumption that only PACKAGE SPEC variables retain their value across calls to a PACKAGE. I thought that PACKAGE BODY variables were reset for every call to the PACKAGE. That doesn't look like it's the case. I don't mind being wrong, but I want to make sure that this isn't 10.1.0.3.0 specific. In training, I was told to always use PACKAGE SPEC variables if I wanted to achieve the equivalent of a global variable.

    In the 3rd call to show_val(), I was expecting the_val to have been set back to 'val1'. It however, is never dimensioned again, retaining the value 'upd8'.

    Code:
    create package show_body_variable_values as
      function show_val (new_val varchar2 default null) return varchar2;
    end; 
    /
    Package created.
    
    CREATE OR REPLACE package body FORBESC.show_body_variable_values as
      the_val varchar2(30) := 'val1';
      
      function show_val (new_val varchar2 default null)  return varchar2 as
      begin
        the_val := NVL(new_val,the_val);
        return the_val;
      end;
    end;
    /
    Package body created.
    
    declare
     my_var varchar2(30);
    begin
     my_var := show_body_variable_values.show_val(null);
     dbms_output.put_line (my_var);
    end; 
    /
    val1
    
    declare
     my_var varchar2(30);
    begin
     my_var := show_body_variable_values.show_val('upd8');
     dbms_output.put_line (my_var);
    end; 
    /
    upd8
    
    declare
     my_var varchar2(30);
    begin
     my_var := show_body_variable_values.show_val(null);
     dbms_output.put_line (my_var);
    end; 
    /
    upd8
    -cf

  2. #2
    Join Date
    Nov 2006
    Location
    Indianapolis
    Posts
    90
    I'm not a pl/sql guru, but it looks to me like your example provides the answer to your question... your global variable has scope for each database session that uses it

    search asktom for "package variables" or "global variables" for discussions on using (or not using) globals

  3. #3
    Join Date
    Dec 2003
    Posts
    1,074
    My example proves it to me, I agree. I'm just stuck in that spot of second-guessing myself. I've been working under the wrong impression for about 2 years now, so I was just getting some input from people who are using another version.

    We have several programs which 'initialize' variable values in the DECLARE section, and it's going to bite us, if so.

    -cf

Posting Permissions

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