RESULT_CACHE RELIES_ON(NLS_SESSION_PARAMETERS) [英] RESULT_CACHE RELIES_ON (NLS_SESSION_PARAMETERS)

问题描述

为什么每次我更改会话以设置新的NLS_DATE_FORMAT时，以下函数都不返回新的参数值

Why below function is not returning fresh param value every time I am altering session to set new NLS_DATE_FORMAT

FUNCTION get_param(p_parameter IN VARCHAR2)
   RETURN VARCHAR2 RESULT_CACHE relies_on(nls_session_parameters) IS
   l_value nls_session_parameters.value%TYPE;
BEGIN
   dbg('Entered  Fn_Get_nls_session_Parameter_frc to cache details for .. ' || p_parameter);
   SELECT SYS_CONTEXT('USERENV', p_parameter) INTO l_value FROM dual;
   RETURN l_value;
EXCEPTION
   WHEN NO_DATA_FOUND THEN
      dbg('In NDF : Gng to return value as null.. ');
      l_value := NULL;
      RETURN l_value;
END get_param;

推荐答案

好吧……我想答案就是问题！如果您仔细阅读有关的Oracle文档跨会话功能 ，那么您就会知道.

Well... I would say the answer stands in the question! If you carefully read Oracle documentation about Cross Session Functions, then you'd know.

跨会话PL/SQL函数结果缓存提供了一种简单的方法，可以通过为SGA中的输入参数的特定组合保存函数调用的结果来提高PL/SQL函数的性能.任何使用 相同参数 的相同功能的会话都可以重用这些结果.

The cross-session PL/SQL function result cache provides a simple way to boost the performance of PL/SQL functions by saving the results of function calls for specific combinations of input parameters in the SGA. These results can be reused by any session calling the same function with the same parameters.

这正是创建函数时所使用的:

This is exactly what you're using when creating your function:

FUNCTION get_param(p_parameter IN VARCHAR2)
   RETURN VARCHAR2 
   RESULT_CACHE relies_on(nls_session_parameters) 
IS

在通话之间

确实 nls_session_parameters视图不变 ！它是固定的系统视图.什么会改变它，您的用户会从中看到什么.

Indeed nls_session_parameters View doesn't change between your calls! it is a fixed system view. What changes it what your user sees from it.

所以您有解决方案:

• 简单而低效(抱歉):从函数声明中删除RESULT_CACHE语句，或找到一种在两次调用之间刷新缓存的方法

• 添加一个将在您的调用之间更改的参数:

• simpler and inefficient(sorry): remove RESULT_CACHE statement from your function declaration or find a way to refresh the cache between the calls

• add a parameter that will change between your calls:

FUNCTION get_param(p_parameter IN VARCHAR2, p_dummy_session_id IN NUMBER)
   RETURN VARCHAR2 RESULT_CACHE relies_on(nls_session_parameters) IS
...

(您可能需要对"dummy"参数进行实际处理才能将其考虑在内)

(you might need to actually do something with the "dummy" parameter for it to be taken into account)

这篇关于RESULT_CACHE RELIES_ON(NLS_SESSION_PARAMETERS)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！