Hi all,
I was struggling with SQL Developer for VS Code and I think I have bumped into a bug.
After creating a user context the context is not accessible in SQL Developer for VS Code.
Example: create the context with the statement below (copied from Morgan Library). Execute all statement normally (CTRL+Enter), not as a script.
CREATE OR REPLACE CONTEXT App_Ctx using My_pkg
ACCESSED GLOBALLY;
CREATE OR REPLACE PACKAGE my_pkg AUTHID DEFINER IS
PROCEDURE set_session_id(p_session_id NUMBER);
PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2);
PROCEDURE close_session(p_session_id NUMBER);
END my_pkg;
/
CREATE OR REPLACE PACKAGE BODY my_pkg IS
g_session_id NUMBER;
PROCEDURE set_session_id(p_session_id NUMBER) IS
BEGIN
g_session_id := p_session_id;
dbms_session.set_identifier(p_session_id);
END set_session_id;
--===============================================
PROCEDURE set_ctx(p_name VARCHAR2, p_value VARCHAR2) IS
BEGIN
dbms_session.set_context('App_Ctx',p_name,p_value,USER,g_session_id);
END set_ctx;
--===============================================
PROCEDURE close_session(p_session_id NUMBER) IS
BEGIN
dbms_session.set_identifier(p_session_id);
dbms_session.clear_identifier;
END close_session;
--===============================================
END my_pkg;
/
exec my_pkg.set_session_id(1234);
exec my_pkg.set_ctx('Var1', 'Val1');
exec my_pkg.set_ctx('Var2', 'Val2');
SELECT sys_context('app_ctx', 'var1') var1, sys_context('app_ctx', 'var2') var2
FROM DUAL;
The last select statement from SYS_CONTEXT does not show any value (null).
If I connect to traditional SQL Developer and I run the following:
exec my_pkg.set_session_id(1234);
SELECT sys_context('app_ctx', 'var1') var1, sys_context('app_ctx', 'var2') var2
FROM DUAL;
I can see the values set in the SQL Developer for VS code session:
So, for some reason it seems that they cannot be retrieved only within SQL Developer for VS Code.