Hi.
I see the same issue with the context approach:
Code:
CREATE OR REPLACE CONTEXT app_ctx USING set_ctx;
CREATE OR REPLACE PACKAGE set_ctx AS
FUNCTION set_attr (p_attr1 IN VARCHAR2,
p_attr2 IN VARCHAR2)
RETURN VARCHAR2;
END set_ctx;
/
CREATE OR REPLACE PACKAGE BODY set_ctx IS
FUNCTION set_attr (p_attr1 IN VARCHAR2,
p_attr2 IN VARCHAR2)
RETURN VARCHAR2 IS
BEGIN
DBMS_SESSION.set_context('app_ctx', 'attr1', p_attr1);
DBMS_SESSION.set_context('app_ctx', 'attr2', p_attr2);
RETURN 'COMPLETE';
END;
END set_ctx;
/
SHOW ERRORS
SELECT SYS_CONTEXT( 'app_ctx', 'attr1' ) AS get_1
, SYS_CONTEXT( 'app_ctx', 'attr2' ) AS get_2
FROM (SELECT CASE
WHEN 1 = 1 THEN
set_ctx.set_attr ('definitely', 'not')
END
FROM dual
ORDER BY 1);
GET_1
-----------------------------------------------
GET_2
-----------------------------------------------
1 row selected.
SELECT CASE
WHEN 1 = 1 THEN
set_ctx.set_attr ('definitely', 'not')
END
FROM dual
ORDER BY 1;
CASEWHEN1=1THENSET_CTX.SET_ATT
--------------------------------
COMPLETE
1 row selected.
SELECT SYS_CONTEXT( 'app_ctx', 'attr1' ) AS get_1
, SYS_CONTEXT( 'app_ctx', 'attr2' ) AS get_2
FROM dual;
GET_1
----------------------------------------------
GET_2
----------------------------------------------
definitely
not
1 row selected.
Am I going mad?
Cheers
Tim...