Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DBMS_Session.List_Context() for GLOBALLY ACCESSED Context

JeneanDec 7 2010 — edited Dec 7 2010
I created

CREATE CONTEXT myContext USING PACKAGE myPackage ACCESSED GLOBALLY ;

CREATE OR REPLACE PACKAGE myPackage

PROCEDURE Set_My_Context(
piId IN INTEGER,
piValue IN INTEGER) ;
END myPackage ;

CREATE OR REPLACE PACKAGE BODY myPackage

PROCEDURE Set_My_Context(
piId IN INTEGER,
piValue IN INTEGER)
IS

BEGIN

DBMS_Session.Set_Context(
'myContext',
'myValue',
piValue,
SYS_Context('USERENV', 'SESSION_USER'),
piId) ;

END Set_My_Context ;
END My_Package ;

Test Script
DECLARE

iSize INTEGER ;

tblAppCtx DBMS_Session.AppCtxTabTyp ;

BEGIN
DBMS_Session.Set_Identifier(piId) ;

My_Package.Set_My_Context(
1,
9999) ;

DBMS_Session.List_Context(
tblAppCtx
iSize) ;

DBMS_Output.Put_Line('iSize = ' || iSize) ;

DBMS_Output.Put_Line('SYS_Context(''myContext', ''myValue'') = '
|| SYS_Context('myContext', 'myValue') ) ;
END ;
/

Output
iSize = 0
SYS_Context('myContext', 'myValue') = 9999

Questions
1) Why isn't the global application variable included in List_Context()? It obviously exists based on the value returned by SYS_Context().
2) Is it because the context is accessed globally and the variable is stored in the SGA not the UGA?
(My theory because List_Context() works as accepted if CREATE CONTEXT myContext USING PACKAGE myPackage does not include ACCESSED GLOBALLY. However I can find do such an exclusion within Oracles documentation for DBMS_Session or CREATE CONTEXT.

Any insight would be appreciated.

Jenean Spencer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 4 2011
Added on Dec 7 2010
1 comment
1,226 views