0 Replies Latest reply on Feb 5, 2016 2:19 PM by 2680129

    Error using dbms_stats.gather_table_stats and a stored procedure with "authid current_user"

    2680129

      Hello,

       

      When I use dbms_stats.gather_table_stats with a deterministic stored function which uses authid current_user, I have the following error:

       

      [...]ORA-20001: Error when processing extension -  The function is not deterministic[...]
      

       

      Here is an example:

      CREATE OR REPLACE FUNCTION test(value varchar2) 
      return varchar2 deterministic authid current_user 
      is 
      pragma autonomous_transaction;
      begin 
          execute immediate 'select 1 from dual';
          return 'FALSE';
      END test;
      /
      
      GRANT execute ON test TO the_other_schema;
      
      BEGIN 
          dbms_stats.gather_table_stats(
              ownname => 'the_other_schema', 
              tabname => 'the_other_table', 
              method_opt => 'for columns (current_user.test(the_other_column)) size auto'
          ); 
      END;
      /
      

       

      If I remove "authid current_user" from the stored function test, there is not error anymore.

       

      I have this same error on Oracle Database 11.1.0.6, 11.1.0.7 or 12.1.0.2.

       

      How I can use this deterministic stored function with "authid current_user" without error ?

      Is it possible to use authid current_user  in a deterministic function ?

       

      Thank you in advance,