Cómo crear una vista parametrizada

En Oracle, podemos crear vistas que retornen resultados dependientes de parámetros previamente seteados. La forma de lograr esto es usando un feature llamado Application Contexts.

¿Qué es un contexto de aplicación?
El contexto de aplicación es simplemente un espacio en memoria que nos permite almacenar valores para luego utilizarlos en SQL o PL/SQL, como cualquier otra variable definida en el entorno. De forma transparente, mis objetos pueden ser 'manipulados' externamente sin necesidad que mis aplicaciones o procesos batch se enteren.
El contexto puede ser definido tanto localmente (privado para cada sesión) como globalmente, compartiendo sus valores para todas las sesiones de la instancia.
Para poder alterar los valores del contexto, debemos crear un paquete especialmente autorizado para ese fin. Esto es un requerimiento por razones de seguridad.
También necesitamos tener el permiso especial de sistema CREATE ANY CONTEXT.

Ejemplo (con sqlplus)

Vamos a ver un sencillo ejemplo de cómo implementar una vista parametrizada con contextos de aplicación, usando el popular usuario SCOTT. El parámetro para la vista en este caso será el número de departamento.

Paso 1: Crear el contexto de aplicación

CREATE CONTEXT app_ctx_scott USING pk_scott_app_context
/

Paso 2: Crear el paquete para manipular el contexto

CREATE OR REPLACE PACKAGE pk_scott_app_context AS
  -- El contexto tendra un unico valor deptno
  PROCEDURE set_dept (p_deptno IN NUMBER);
END;
/

CREATE OR REPLACE PACKAGE BODY pk_scott_app_context AS
  PROCEDURE set_dept (p_deptno IN NUMBER) IS
  BEGIN
    DBMS_SESSION.SET_CONTEXT('app_ctx_scott', 'deptno', p_deptno);
  END;
END;
/

Paso 3: Setear el parámetro de contexto deptno para el departamento de ventas

BEGIN
pk_scott_app_context.set_dept(30);
END;
/

Paso 4: Crear la vista parametrizada

CREATE VIEW empleados AS
SELECT e.empno, e.ename, e.job, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno
AND d.deptno = sys_context('app_ctx_scott','deptno');

Paso 5: Obtener los resultados consultando la vista

SELECT * FROM empleados;

     EMPNO ENAME      JOB       DNAME
---------- ---------- --------- --------------
      7499 ALLEN      SALESMAN  SALES
      7521 WARD       SALESMAN  SALES
      7654 MARTIN     SALESMAN  SALES
      7698 BLAKE      MANAGER   SALES
      7844 TURNER     SALESMAN  SALES
      7900 JAMES      CLERK     SALES

6 rows selected.

Actualmente la vista retorna los empleados del departamento de ventas, ya que así está definida la variable en el contexto. Ahora cambiaré el valor del parámetro para que la vista retorne resultados únicamente del departamento contable:

BEGIN
  pk_scott_app_context.set_dept(10);
END;
/

PL/SQL procedure successfully completed.

SELECT * FROM empleados;

     EMPNO ENAME      JOB       DNAME
---------- ---------- --------- --------------
      7782 CLARK      MANAGER   ACCOUNTING
      7839 KING       PRESIDENT ACCOUNTING
      7934 MILLER     CLERK     ACCOUNTING

3 rows selected.

De la misma forma podemos aplicar esta técnica a procedimientos y funciones, pudiendo manipular valores utilizados internamente o inclusive introduciendo fragmentos de código como sql dinámico.