Can't set Logger Level when not using contexts

[martindsouza]

Change:

procedure null_global_contexts 
  is 
    pragma autonomous_transaction; 
    l_pref_value logger_prefs.pref_value%type;
  begin 
    $if $$no_op or $$rac_lt_11_2 or not $$logger_context $then 
      null; 
    $else 
     -- TODO THIS IS THE FIX
      select lp.pref_value
      into l_pref_value
      from logger_prefs lp
      where 1=1
        and lp.pref_type = 'LOGGER'
        and lp.pref_name = 'GLOBAL_CONTEXT_NAME'
      ;

      if l_pref_value != 'NONE' then
        dbms_session.clear_all_context(namespace => g_context_name); 
      end if;
    $end 
 
    commit; 
  end null_global_contexts; 

Same logic is needed in: save_global_context

procedure save_global_context(
    p_attribute in varchar2,
    p_value in varchar2,
    p_client_id in varchar2 default null)
  is
    pragma autonomous_transaction;
    l_pref_value logger_prefs.pref_value%type;
  begin
    $if $$no_op $then
      null;
    $else
      select lp.pref_value
      into l_pref_value
      from logger_prefs lp
      where 1=1
        and lp.pref_type = 'LOGGER'
        and lp.pref_name = 'GLOBAL_CONTEXT_NAME'
      ;
      
      if l_pref_value != 'NONE' then
        dbms_session.set_context(
          namespace => g_context_name,
          attribute => p_attribute,
          value => p_value,
          client_id => p_client_id);
      end if;
    $end

    commit; -- MD: moved commit to outside of the NO_OP check since commit or rollback must occur in this procedure
  exception

Should just have a function of is_context_enabled_yn or something that these procedures can use

[prinznamor]

Hello Mr. D'Souza,

a function of is_context_enabled_yn is a good idea. We are using our database with ORDS and got thousands of requests each hour. For each request we are setting the database session identifier to the X-Correlation-ID by using dbms_session.set_identifier. Although for our production database we have set LOGGER-Level to OFF, the global context was saved by Logger for each request within the function ok_to_log which was for example called by a logger.log. Our problem is, that the procedure unset_client_level, called by the scheduler job LOGGER_UNSET_PREFS_BY_CLIENT, will not clear the context for this client identifiers, because there exist no entries for this within the table logger_prefs_by_client_id. This causes millions of entries within the global context and after a week the database reported problems with the shared pool (ORA-04031). Sure, we could add a call to the procedure LOGGER.set_level after the call of dbms_session.set_identifier to ensure that the client identifier will be added into the table logger_prefs_by_client_id. But I think it would be useful to check if the context is enabled before adding the values to the global context, if anybody uses dbms_session.set_identifier who do not know the consequences in context with the package LOGGER. Currently we disabled the package LOGGER within the production database by recompiling with the flag no_op.

Best regards

Roman Vogt