View with Parameter
1-virtual private database context
2-global package variable
3-Lookup Tables
#1 Virtual Private Database Context
I will use in where clause SYS_CONTEXT function as parameter to filter data of the query of the view.
First step is creating context that will handle session variables
CREATE OR REPLACE CONTEXT MY_CTX USING CTX_PKG
ACCESSED GLOBALLY;
Second I will create CTX_PKG package that context will use it to handle session parameters.
The package contains three procedures
a-SET_SESSION_ID to set unique id for every session I will use in my context.
b-CLEAR_SESSION to clear session from my context.
c-SET_CONTEXT to set variable value in my context.
Package Specification
CREATE OR REPLACE PACKAGE CTX_PKG
IS
PROCEDURE SET_SESSION_ID (IN_SESSION_ID VARCHAR2);
PROCEDURE CLEAR_SESSION (IN_SESSION_ID VARCHAR2);
PROCEDURE SET_CONTEXT (IN_NAME VARCHAR2, IN_VALUE VARCHAR2);
END CTX_PKG;
Package Body
CREATE OR REPLACE PACKAGE BODY CTX_PKG
IS
GC$SESSION_ID VARCHAR2 (100);
PROCEDURE SET_SESSION_ID (IN_SESSION_ID VARCHAR2)
IS
BEGIN
GC$SESSION_ID := IN_SESSION_ID;
DBMS_SESSION.SET_IDENTIFIER (IN_SESSION_ID);
END;
PROCEDURE CLEAR_SESSION (IN_SESSION_ID VARCHAR2)
IS
BEGIN
DBMS_SESSION.SET_IDENTIFIER (IN_SESSION_ID);
DBMS_SESSION.CLEAR_IDENTIFIER;
END;
PROCEDURE SET_CONTEXT (IN_NAME VARCHAR2, IN_VALUE VARCHAR2)
IS
BEGIN
DBMS_SESSION.SET_CONTEXT ('MY_CTX',
IN_NAME,
IN_VALUE,
USER,
GC$SESSION_ID);
END;
END CTX_PKG;
Now let's test context and my package
BEGIN
CTX_PKG.SET_SESSION_ID (222);
CTX_PKG.SET_CONTEXT ('my_name', 'Mahmoud A. El-Sayed');
CTX_PKG.SET_CONTEXT ('my_age', '26 YO');
END;
Now I set two context variable my_name, my_age
to query this variable I will use SYS_CONTEXT function
SELECT SYS_CONTEXT ('MY_CTX', 'my_name'), SYS_CONTEXT ('MY_CTX', 'my_age')
FROM DUAL;
The output will be
After insuring that my context and package working true let's now create view on table EMP to get employee in department at SCOTT schema
CREATE OR REPLACE VIEW EMP_IN_DEPRATMENT
AS
SELECT *
FROM EMP
WHERE DEPTNO = SYS_CONTEXT ('MY_CTX', 'deptno');
to filter view by employees in department 20 only you should execute this PLSQL block first
BEGIN
CTX_PKG.SET_SESSION_ID (222);
CTX_PKG.SET_CONTEXT ('deptno', '20');
END;
Now lets create select statement against EMP_IN_DEPARTMENT view and see the result
SELECT * FROM EMP_IN_DEPRATMENT;
The output result is like below
#2 Global Package Variables
I will use in where clause global package variables as parameter to filter data of the query of the view.
I will create package that hold every global variables which I will use it as parameters in view.
Package Specification
CREATE OR REPLACE PACKAGE GLB_VARIABLES
IS
GN$DEPTNO EMP.DEPTNO%TYPE;
PROCEDURE SET_DEPTNO (
IN_DEPTNO EMP.DEPTNO%TYPE);
FUNCTION GET_DEPTNO
RETURN EMP.DEPTNO%TYPE;
END;
Package Body
CREATE OR REPLACE PACKAGE BODY GLB_VARIABLES
IS
PROCEDURE SET_DEPTNO (
IN_DEPTNO EMP.DEPTNO%TYPE)
IS
BEGIN
GN$DEPTNO := IN_DEPTNO;
END;
FUNCTION GET_DEPTNO
RETURN EMP.DEPTNO%TYPE
IS
BEGIN
RETURN GN$DEPTNO;
END;
END;
Let's now create view filter its data by global variables in GLBL_VARIABLES package
CREATE OR REPLACE VIEW EMP_IN_DEPRATMENT2
AS
SELECT *
FROM EMP
WHERE DEPTNO =GLB_VARIABLES.GET_DEPTNO;
Now lets create select statement against EMP_IN_DEPARTMENT2 view and see the result
EXEC GLB_VARIABLES.SET_DEPTNO(20);
SELECT * FROM EMP_IN_DEPRATMENT2;
The output result is like below
3-Lookup Tables
another solution is to create lockup table for storing view parameters on it and build view based on the data stored in lockup table.
1-virtual private database context
2-global package variable
3-Lookup Tables
#1 Virtual Private Database Context
I will use in where clause SYS_CONTEXT function as parameter to filter data of the query of the view.
First step is creating context that will handle session variables
CREATE OR REPLACE CONTEXT MY_CTX USING CTX_PKG
ACCESSED GLOBALLY;
Second I will create CTX_PKG package that context will use it to handle session parameters.
The package contains three procedures
a-SET_SESSION_ID to set unique id for every session I will use in my context.
b-CLEAR_SESSION to clear session from my context.
c-SET_CONTEXT to set variable value in my context.
Package Specification
CREATE OR REPLACE PACKAGE CTX_PKG
IS
PROCEDURE SET_SESSION_ID (IN_SESSION_ID VARCHAR2);
PROCEDURE CLEAR_SESSION (IN_SESSION_ID VARCHAR2);
PROCEDURE SET_CONTEXT (IN_NAME VARCHAR2, IN_VALUE VARCHAR2);
END CTX_PKG;
Package Body
CREATE OR REPLACE PACKAGE BODY CTX_PKG
IS
GC$SESSION_ID VARCHAR2 (100);
PROCEDURE SET_SESSION_ID (IN_SESSION_ID VARCHAR2)
IS
BEGIN
GC$SESSION_ID := IN_SESSION_ID;
DBMS_SESSION.SET_IDENTIFIER (IN_SESSION_ID);
END;
PROCEDURE CLEAR_SESSION (IN_SESSION_ID VARCHAR2)
IS
BEGIN
DBMS_SESSION.SET_IDENTIFIER (IN_SESSION_ID);
DBMS_SESSION.CLEAR_IDENTIFIER;
END;
PROCEDURE SET_CONTEXT (IN_NAME VARCHAR2, IN_VALUE VARCHAR2)
IS
BEGIN
DBMS_SESSION.SET_CONTEXT ('MY_CTX',
IN_NAME,
IN_VALUE,
USER,
GC$SESSION_ID);
END;
END CTX_PKG;
Now let's test context and my package
BEGIN
CTX_PKG.SET_SESSION_ID (222);
CTX_PKG.SET_CONTEXT ('my_name', 'Mahmoud A. El-Sayed');
CTX_PKG.SET_CONTEXT ('my_age', '26 YO');
END;
Now I set two context variable my_name, my_age
to query this variable I will use SYS_CONTEXT function
SELECT SYS_CONTEXT ('MY_CTX', 'my_name'), SYS_CONTEXT ('MY_CTX', 'my_age')
FROM DUAL;
The output will be
After insuring that my context and package working true let's now create view on table EMP to get employee in department at SCOTT schema
CREATE OR REPLACE VIEW EMP_IN_DEPRATMENT
AS
SELECT *
FROM EMP
WHERE DEPTNO = SYS_CONTEXT ('MY_CTX', 'deptno');
to filter view by employees in department 20 only you should execute this PLSQL block first
BEGIN
CTX_PKG.SET_SESSION_ID (222);
CTX_PKG.SET_CONTEXT ('deptno', '20');
END;
Now lets create select statement against EMP_IN_DEPARTMENT view and see the result
SELECT * FROM EMP_IN_DEPRATMENT;
The output result is like below
#2 Global Package Variables
I will use in where clause global package variables as parameter to filter data of the query of the view.
I will create package that hold every global variables which I will use it as parameters in view.
Package Specification
CREATE OR REPLACE PACKAGE GLB_VARIABLES
IS
GN$DEPTNO EMP.DEPTNO%TYPE;
PROCEDURE SET_DEPTNO (
IN_DEPTNO EMP.DEPTNO%TYPE);
FUNCTION GET_DEPTNO
RETURN EMP.DEPTNO%TYPE;
END;
Package Body
CREATE OR REPLACE PACKAGE BODY GLB_VARIABLES
IS
PROCEDURE SET_DEPTNO (
IN_DEPTNO EMP.DEPTNO%TYPE)
IS
BEGIN
GN$DEPTNO := IN_DEPTNO;
END;
FUNCTION GET_DEPTNO
RETURN EMP.DEPTNO%TYPE
IS
BEGIN
RETURN GN$DEPTNO;
END;
END;
Let's now create view filter its data by global variables in GLBL_VARIABLES package
CREATE OR REPLACE VIEW EMP_IN_DEPRATMENT2
AS
SELECT *
FROM EMP
WHERE DEPTNO =GLB_VARIABLES.GET_DEPTNO;
Now lets create select statement against EMP_IN_DEPARTMENT2 view and see the result
EXEC GLB_VARIABLES.SET_DEPTNO(20);
SELECT * FROM EMP_IN_DEPRATMENT2;
The output result is like below
3-Lookup Tables
another solution is to create lockup table for storing view parameters on it and build view based on the data stored in lockup table.
