GRANT EXECUTE ON DBMS_RLS TO RLS ;
GRANT EXECUTE ON DBMS_SESSION TO RLS ;

CREATE OR REPLACE CONTEXT USEA_CONTEXT USING SET_USEA_CONTEXT ;

CREATE OR REPLACE PACKAGE SET_USEA_CONTEXT
IS
	PROCEDURE SET_MANAGER ;
	PROCEDURE SET_ISLAMABAD ;
	PROCEDURE SET_KARACHI ;
	PROCEDURE SET_PESHAWAR ;
	PROCEDURE SET_LAHORE ;
END ;
/

CREATE OR REPLACE PACKAGE BODY SET_USEA_CONTEXT
AS
	PROCEDURE SET_MANAGER
	IS
	BEGIN
		DBMS_SESSION.SET_CONTEXT('USEA_TEST', 'APP_ROLE', 'MANAGER');
	END ;
	PROCEDURE SET_ISLAMABAD
	IS
	BEGIN
		DBMS_SESSION.SET_CONTEXT('USEA_TEST', 'APP_ROLE', 'ISLAMABAD') ;
	END ;
	PROCEDURE SET_KARACHI
	IS
	BEGIN
		DBMS_SESSION.SET_CONTEXT('USEA_TEST', 'APP_ROLE', 'KARACHI') ;
	END ;
	PROCEDURE SET_PESHAWAR
	IS
	BEGIN
		DBMS_SESSION.SET_CONTEXT('USEA_TEST', 'APP_ROLE', 'PESHAWAR') ;
	END ;
	PROCEDURE SET_LAHORE
	IS
	BEGIN
		DBMS_SESSION.SET_CONTEXT('USEA_TEST', 'APP_ROLE', 'LAHORE') ;
	END ;
END ;
/

CREATE OR REPLACE PACKAGE USEA_POLICY
AS
	FUNCTION USEA_PREDICATE(SCHEMA_NAME IN VARCHAR2, OBJECT_NAME IN VARCHAR2) RETURN VARCHAR2 ;
END ;
/

PACKAGE BODY USEA_POLICY
AS
	FUNCTION USEA_PREDICATE(SCHEMA_NAME IN VARCHAR2, OBJECT_NAME IN VARCHAR2) RETURN VARCHAR2
	IS
	   LV_PREDICATE VARCHAR2(1000) := '' ;
	BEGIN
		IF SYS_CONTEXT('USEA_CONTEXT', 'APP_ROLE') = 'MANAGER' THEN
		  LV_PREDICATE := 'SALEPOINT IN (SELECT SALEPOINT FROM BILLING.USER_RIGHTS WHERE USERNAME LIKE '''||USER||'''' ;
/*		ELSIF SYS_CONTEXT('USEA_CONTEXT', 'APP_ROLE') = 'ISLAMABAD' THEN
		  LV_PREDICATE := 'SALEPOINT LIKE ''%ISLAMABAD%''' ;
		ELSIF SYS_CONTEXT('USEA_CONTEXT', 'APP_ROLE') = 'KARACHI' THEN
		 	LV_PREDICATE := 'SALEPOINT LIKE ''%KARACHI%''' ;
		ELSIF SYS_CONTEXT('USEA_CONTEXT', 'APP_ROLE') = 'PESHAWAR' THEN
			LV_PREDICATE := 'SALEPOINT LIKE ''%PESHAWAR%''' ;
		ELSIF SYS_CONTEXT('USEA_CONTEXT', 'APP_ROLE') = 'LAHORE' THEN
			LV_PREDICATE := 'SALEPOINT LIKE ''%LAHORE%''' ;*/
		ELSE
		LV_PREDICATE := '1=2' ;
		END IF ;
		RETURN LV_PREDICATE ;
	END ;
END ;
/

BEGIN
	DBMS_RLS.ADD_POLICY(
		OBJECT_SCHEMA 	=> 'BILLING',
		OBJECT_NAME 	=> 'BILLMASTER', -- TABLE NAME
		POLICY_NAME 	=> 'USEA_TEST_POLICY', 
		FUNCTION_SCHEMA => 'BILLING',
		POLICY_FUNCTION => 'USEA_POLICY.USEA_PREDICATE',
		STATEMENT_TYPES => 'SELECT, INSERT, UPDATE, DELETE',
		UPDATE_CHECK 	=> TRUE,
		ENABLE 		=> TRUE,
		STATIC_POLICY 	=> FALSE) ;
END ;
/

CREATE OR REPLACE TRIGGER USEA_LOGON_TRIGGER
AFTER LOGON ON DATABASE
BEGIN
  SET_USEA_CONTEXT.SET_MANAGER ;
END ;
/

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SESSION", line 78
ORA-06512: at "BILLING.SET_USEA_CONTEXT", line 6
ORA-06512: at line 2