##  Examples of how to exploit package variables


    FUNCTION get_osuser return varchar2 AS
      Xosuser varchar2(30);
    BEGIN
      select  v.osuser
      into    Xosuser
      from    v$session v
      where   v.audsid = userenv(‘SESSIONID’);
      return  (Xosuser);
    END;

    sql >> :osuser := get_osuser;



##  This function should be coded as follows :
	 
    PACKAGE get_user_details
    AS
      Function osuser return varchar2;
    END;


    PACKAGE body get_user_details
    AS
     Xosuser  varchar2(30);
      Function osuser return varchar2 AS
        Cursor C1 is 
        select v.osuser
        from   v$session v
        where  v.audsid = userenv(‘SESSIONID’);
      BEGIN
        if Xosuser is not null then
           Open  C1;
           Fetch C1 into Xosuser;
           Close C1;
        end if;
       return  (Xosuser);
      END;
    END;

    sql >> :osuser := get_user_details.osuser;


##  Package variables can be used to improve PL/SQL performance

    PACKAGE validate_code
    AS
      Function dept (dpt IN number);
      Xdept  number := -9999;
      Xdesc  varchar2(30);
    END;


    PACKAGE body validate_code
    AS
      FUNCTION dept (dpt IN number) return varchar2 AS
        Cursor C1 is 
        select dept_desc
        from   dept
        where  dept_no = dpt;
      BEGIN
        if Xdept != dpt then
           Xdesc := null;
           Open  C1;
           Fetch C1 into Xdesc;
           Close C1;
           Xdept := dpt;
        end if;
        if Xdesc is null then
           raise_application_error (-20101, ‘Invalid Dept Entered’);
        else
           return  (Xdesc);
        end if;
      END;
    END;

    sql >> :dept_desc := validate_code.dept (:dept_no);