2 Replies Latest reply: Feb 12, 2013 7:38 AM by Verdi RSS

    Deterministic clause

    Verdi
      NLSRTL      10.2.0.5.0     Production
      Oracle Database 10g Enterprise Edition      10.2.0.5.0     64bi
      PL/SQL      10.2.0.5.0     Production
      TNS for IBM/AIX RISC System/6000:      10.2.0.5.0     Productio

      Hello,

      I have a context created - CONTEXT1. The parameters in the context are set through a package called PACKAGE1. This package is also used to get parameters from the context.
      Upon login in the database, a set of parameters are set in the context for the logged user.
      For example, to get the username of the logged user, I can invoke:
      select package1.getusername() from dual;
      It is known that it will return the same value any time I call it within the same session and that if it is called by another user, with different session, it will give different value.

      Therefrom, my question is, can this function - package1.getusername - be made deterministic as it returns the same value every time it is invoked in the context of the current user?
      Or the function must return same values across all sessions in order to be a good candidate for deterministic function?

      One place where I use package1.getusername() is at the right side of a predicate:
      WHERE UPPER(tab1.column1) = package1.getusername()
      Thank you
        • 1. Re: Deterministic clause
          Dom Brooks
          A deterministic function will return the same value given the same parameters, regardless of user, so your case does not really fit.

          However, the current implementation of deterministic is such that this shouldn't make a difference - but that may not be always true going forward.

          Then again, it is only when used in SQL that this declaration of deterministic should be relevant anyway

          When using the a plsql function in SQL, where the function is expected to return the same values for the same parameters and you want to minimise the number of calls to the function, the benefits of scalar subquery caching are more consistent across versions and more predictable, i.e.
          WHERE UPPER(tab1.column1) =(SELECT  package1.getusername() FROM DUAL)
          This should result in one execution of the function regardless of rows returned by the SQL.
          This package is also used to get parameters from the context.
          But in SQL statements it would be better to reference the CONTEXT directly using SYS_CONTEXT.
          Using a package only introduces more unnecessary context switches between SQL and PLSQL engines.
          i.e.
          WHERE UPPER(tab1.column1) = SYS_CONTEXT(<your_context>,<your attribute>)
          • 2. Re: Deterministic clause
            Verdi
            Thank you!