Script categories

Thursday, 3 November 2011

Using SYS_CONTEXT in a view

create or replace context App_Context using Set_Global_Var;

create or replace package Set_Global_Var as

  procedure set_ctx (p_Name       in varchar2
                    ,p_Value      in varchar2
                    ,p_ID         in number default null);

  -- Only use this procedure if you access attributes against a
  -- namespace globally across different schema sessions
  procedure Set_Id  (p_ID         in number);

end Set_Global_Var;
/

create or replace package body Set_Global_Var as

  -- object name represented by literal string is defined
  -- by the CREATE OR REPLACE CONTEXT DDL COMMAND
  v_CTX_NAME constant All_Context.Namespace%type := 'APP_CONTEXT';

  procedure Set_Id  (p_ID in number) as
    begin
      dbms_session.set_identifier(p_ID);
  end Set_ID;

  -- p_ID will only be offered when an identifier has
  -- been set using dbms_session.set_identifier so as to
  -- read an attribute from a namespace using the identifier
  procedure set_ctx (p_Name       in varchar2
                    ,p_Value      in varchar2
                    ,p_ID         in number default null) as
    begin

      dbms_session.set_context (namespace => v_CTX_NAME
                               ,attribute => p_Name
                               ,value     => p_Value
                           --,client_ID  => p_ID -- only set this
                           -- when .set_identifier is used
                               );

  end set_ctx;

end Set_Global_Var;
/

-- Test, which adds 2 years to the existing date
declare
  v_Value varchar2(10) := to_char(add_months(sysdate, 24), 'YYYY');
begin

  Set_Global_Var.set_ctx('DEFAULT_YEAR', v_Value);

  exception
    when others then
      dbms_output.put_line('Error : ' || sqlerrm);
end;
/

create or replace view Year_View as
-- The sys_context variable can occur anywhere in the view
select sys_context('APP_CONTEXT', 'DEFAULT_YEAR')  as Default_Year
      ,to_char(sysdate, 'YYYY')                    as Actual_Year
from dual
/

col Actual_year format 9999
col Default_year format 9999
-- Now test if the current year and the context year differ
select *
from Year_View
/

No comments:

Post a Comment