I execute the following in Scott and HR Schema
###### Scott Schema
- Code: Select all
create table uom_master
(
uom_code varchar2(240),
uom_code_desc varchar2(2400),
uom_client_id varchar2(3)
);
Insert into uom_master Values
('U001','Unit of Measurement 1','001');
Insert into uom_master Values
('U002','Unit of Measurement 1','001');
Insert into uom_master Values
('U001','Unit of Measurement 2','002');
Insert into uom_master Values
('U002','Unit of Measurement 2','002');
grant select, insert, update, delete, references, alter, index on uom_master to hr;
CREATE OR REPLACE TRIGGER RND_TRG_UOM_MASTER
BEFORE INSERT ON UOM_MASTER
FOR EACH ROW
DECLARE
M_SYS_CTX_VALUE VARCHAR2(240);
BEGIN
SELECT VALUE INTO M_SYS_CTX_VALUE
FROM SESSION_CONTEXT
WHERE NAMESPACE LIKE 'RND_CLIENT%';
:NEW.uom_CLIENT_ID := M_SYS_CTX_VALUE;
END;
################ HR Schema
- Code: Select all
create or replace procedure p_set_clid_context_001(p_client_id in varchar2 default '001')
--authid current_user
is
begin
sys.dbms_session.set_context('RND_CLIENT_ID_001','CLIENT_ID_001',P_CLIENT_ID);
exception
when others then
dbms_output.put_line('code '|| sqlcode || 'message '|| sqlerrm);
dbms_output.put_line('Exception '||dbms_utility.format_error_backtrace);
end p_set_clid_context_001;
/
create or replace context RND_CLIENT_ID_001 using p_set_clid_context_001;
CREATE OR REPLACE TRIGGER hr.after_logon_trg
AFTER LOGON ON hr.SCHEMA
BEGIN
DBMS_APPLICATION_INFO.set_module(USER, ' Logon Context Initialized');
p_set_clid_context_001(); -- Default value will be Set
END;
create or replace view uom_master as
select * from
SCOTT.uom_master
where UOM_CLIENT_ID = sys_context('RND_CLIENT_ID_001','CLIENT_ID_001');
Insert into uom_master(UOM_CODE,UOM_CODE_DESC) Values
('U005','Unit of Measurement 2');
During this Insert the Trigger of the Table Fires and Fetches the Sys context Value from HR Schema. There is no Context defined in Scott Schema, the following select Statement
- Code: Select all
SELECT *
FROM
SESSION_CONTEXT
will return No Rows when run from Scott. But the context is picked from HR Schema where its defined. Though there is No Context defined in Scott Schema. The trigger fired for the view UOM_MASTER in HR is through the trigger RND_TRG_UOM_MASTER defined in Scott. Is the behaviour correct and kindly explain and clarify.
Thanks in advance
