SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

5 rows selected.


SQL> CREATE OR REPLACE PACKAGE SCOTT.GLOBAL_VARIABLES IS
  2  
  3    DATE_A DATE   := To_Date (Sysdate);
  4    DATE_B DATE   := To_Date (Sysdate);
  5    DEPT_A NUMBER := 10;
  6    -- 
  7    Procedure SET_DATES (A_DATE DATE, B_DATE DATE);
  8    Procedure SET_DEPT (A_DEPT NUMBER);
  9    -- 
 10    Function RET_DATE_A RETURN DATE;
 11    Function RET_DATE_B RETURN DATE;
 12    Function RET_DEPT RETURN NUMBER;
 13  END;
 14  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY SCOTT.GLOBAL_VARIABLES AS
  2  
  3   PROCEDURE SET_DATES (a_date IN DATE,
  4                        b_date IN DATE)
  5    IS
  6   BEGIN
  7     DATE_A := a_date;
  8     DATE_B := b_date;
  9   END;
 10  
 11   PROCEDURE SET_DEPT (a_dept IN NUMBER)
 12    IS
 13   BEGIN
 14     DEPT_A := a_dept;
 15   END;
 16  
 17   FUNCTION RET_DATE_A RETURN DATE
 18    IS
 19   BEGIN
 20    RETURN DATE_A;
 21   END;
 22  
 23   FUNCTION RET_DATE_B RETURN DATE
 24    IS
 25   BEGIN
 26    RETURN DATE_B;
 27   END;
 28  
 29   FUNCTION RET_DEPT RETURN NUMBER
 30    IS
 31   BEGIN
 32    RETURN DEPT_A;
 33   END;
 34  
 35  END GLOBAL_VARIABLES;
 36  /

Package body created.

SQL> ed
Wrote file afiedt.buf

SQL> CREATE VIEW SCOTT.Get_Employees as
  2  Select b.dname department,
  3         a.empno "Emp #",
  4         a.ename "Emp Name",
  5         a.job   "Job",
  6         a.hiredate "Hire Date",
  7         a.sal "Salary"
  8    From emp a, dept b
  9   Where a.deptno = b.deptno
 10     and b.deptno = GLOBAL_VARIABLES.RET_DEPT 
 11     and a.hiredate between GLOBAL_VARIABLES.RET_DATE_A
 12                        and GLOBAL_VARIABLES.RET_DATE_B
 13  /

View created.

SQL> select * from get_employees;

no rows selected

SQL> ed
Wrote file afiedt.buf

  1  BEGIN
  2    GLOBAL_VARIABLES.SET_DATES ( to_date('01-jan-1981'), to_date('31-dec-1981') );
  3    GLOBAL_VARIABLES.SET_DEPT ( 10 );
  4* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> select * from get_employees;

DEPARTMENT         Emp # Emp Name   Job       Hire Date    Salary
-------------- --------- ---------- --------- --------- ---------
ACCOUNTING          7782 CLARK      MANAGER   09-JUN-81      2450
ACCOUNTING          7839 KING       PRESIDENT 17-NOV-81      5000

2 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  BEGIN
  2    GLOBAL_VARIABLES.SET_DATES ( to_date('01-jan-1981'), to_date('31-dec-1981') );
  3    GLOBAL_VARIABLES.SET_DEPT ( 20 );
  4* END;
SQL> /

PL/SQL procedure successfully completed.

SQL> select * from get_employees;

DEPARTMENT         Emp # Emp Name   Job       Hire Date    Salary
-------------- --------- ---------- --------- --------- ---------
RESEARCH            7566 JONES      MANAGER   02-APR-81      2975
RESEARCH            7902 FORD       ANALYST   03-DEC-81      3000

2 rows selected.

SQL>