Oracle determinism requirements and idiosyncrasies

Question

I've been troubled by my lack of understanding about an issue that periodically emerges: Function-Determinicity.

From the docs, it seems fairly clear:

A DETERMINISTIC function may not have side effects.

A DETERMINISTIC function may not raise an unhandled exception.

As these are important core concepts with robust, central implementations in standard packages, I don't think there is a bug or anything (the fault lies in my assumptions and understanding, not Oracle). That being said, both of these requirements sometimes appear to have some idiosyncratic uses within the standard package and the DBMS_ and UTL_ packages.

I hoped to post a couple of examples of Oracle functions that raise some doubts for me in my use of DETERMINISTIC and the nuances in these restrictions, and see if anyone can explain how things fit together. I apologize this is something of a "why" question and it can be migrated if needed, but the response to this question: (Is it ok to ask a question where you've found a solution but don't know why something was behaving the way it was?) made me think it might be appropriate for SO.

Periodically in my coding, I face uncertainty whether my own UDFs qualify as pure, and at other times, I use Oracle functions that surprise me greatly to learn they are impure. If anyone can take a look and advise, I would be grateful.

As a first example, TO_NUMBER. This function seems pure, but it also throws exceptions. In this example I'll use TO_NUMBER in a virtual column (DETERMINISTIC should be required here)

CREATE TABLE TO_NUMBER_IS_PURE_BUT_THROWS (
  SOURCE_TEXT    CHARACTER VARYING(5 CHAR) ,
  NUMERICIZATION NUMBER(5 , 0) GENERATED ALWAYS AS (TO_NUMBER(SOURCE_TEXT , '99999')) ,
  CONSTRAINT POSITIVE_NUMBER CHECK (NUMERICIZATION >= 0)
);

Table TO_NUMBER_IS_PURE_BUT_THROWS created.

INSERT INTO TO_NUMBER_IS_PURE_BUT_THROWS VALUES ('0',DEFAULT);
INSERT INTO TO_NUMBER_IS_PURE_BUT_THROWS VALUES ('88088',DEFAULT);
INSERT INTO TO_NUMBER_IS_PURE_BUT_THROWS VALUES ('UH-OH',DEFAULT);

1 row inserted.
1 row inserted.
ORA-01722: invalid number

The ORA-01722 would seem to violate the unhandled-exception requirement. Presumably any function I create that casts via TO_NUMBER should handle this possibility to remain pure. But throwing the exception here seems appropriate, and reliable. It seems there is some debate about whether exceptions violate referential-transparency (Why is the raising of an exception a side effect?)

The second situation I encounter is System functions that seem like they should-be DETERMINISTIC but arent't. There must be some reason they are considered impure. In some cases, it seems unfathomable that the internals would be generating side-effects.

An extreme example of this could be DBMS_ASSERT.NOOP though there are many others. The function returns its input unmodified. How can it be nondeterministic?

CREATE TABLE HOW_IS_NOOP_IMPURE (
  SOURCE_TEXT VARCHAR2(256 BYTE),
  COPY_TEXT VARCHAR2(256 BYTE) GENERATED ALWAYS AS (DBMS_ASSERT.NOOP(SOURCE_TEXT)),
  CONSTRAINT COPY_IS_NOT_NULL CHECK(COPY_TEXT IS NOT NULL)
);

Yields:

ORA-30553: The function is not deterministic

Presumably it violates the requirements for determinicity, but that is hard to imagine. I wondered what I'm missing in my presumption that functions like this would be deterministic.

EDIT In response to Lukasz's comment about session settings:
I can accept it if cross-session repeatability is the root cause of functions like NOOPnot being DETERMINISTIC, but TO_CHAR is deterministic/eligible for use in virtual columns et al. but appears to have sensitivity to session settings in its format masks:

ALTER SESSION SET NLS_NUMERIC_CHARACTERS = '._';
Session altered.

CREATE TABLE TO_CHAR_NLS(
  INPUT_NUMBER NUMBER(6,0),
  OUTPUT_TEXT CHARACTER VARYING(64 CHAR) GENERATED ALWAYS AS (TO_CHAR(INPUT_NUMBER,'999G999'))
);
Table TO_CHAR_NLS created.

INSERT INTO TO_CHAR_NLS VALUES (123456,DEFAULT);
INSERT INTO TO_CHAR_NLS VALUES (111222,DEFAULT);
SELECT INPUT_NUMBER, OUTPUT_TEXT FROM TO_CHAR_NLS ORDER BY 1 ASC;

1 row inserted.
1 row inserted.
  INPUT_NUMBER OUTPUT_TEXT
        111222  111_222
        123456  123_456

Answer 1

The ORA-01722 would seem to violate the unhandled-exception requirement. Presumably any function I create that casts via TO_NUMBER should handle this possibility to remain pure.

Firstly, i must appreciate you for asking such a good question. Now, when you say you used TO_NUMBER, it should convert all the text inputted to the function but you should know that TO_NUMBER has some restrictions. As per TO_NUMBER definition:

The TO_NUMBER function converts a formatted TEXT or NTEXT expression to a number. This function is typically used to convert the formatted numerical output of one application (which includes currency symbols, decimal markers, thousands group markers, and so forth) so that it can be used as input to another application.

It clearly says,it used to cast the formatted numerical output of one application, that means TO_NUMBER itself expect a numerical input and when you write as below:

INSERT INTO TO_NUMBER_IS_PURE_BUT_THROWS VALUES ('UH-OH',DEFAULT);

You completely passed the unexpected input to TO_NUMBER function and hence it throws the error ORA-01722: invalid number as expected behavior.

Read more about TO_NUMBER.

Secondly,

An extreme example of this could be DBMS_ASSERT.NOOP though there are many others. The function returns its input unmodified. How can it be nondeterministic?

DBMS_ASSERT.NOOP function is can be used where someone passing actual piece of code through a variable and don't want it to be checked for SQL injection attacks. This has to be nondeterministic as it just return what we input to the function.

I show you a example to demonstrate why this has to be non-deterministic.

Let's say i create a function years_from_today as deterministic.

CREATE OR REPLACE FUNCTION years_from_today
 ( p_date   IN DATE )
RETURN NUMBER DETERMINISTIC IS
BEGIN
  RETURN ABS(MONTHS_BETWEEN(SYSDATE, p_date) / 12);
END years_from_today;
/

Now i create a table and use this function in a query as below:

CREATE TABLE det_test AS
SELECT TO_DATE('01-JUL-2009', 'DD-MON-YYYY') AS date_value 
FROM   dual;

SELECT date_value, SYSDATE, years_from_today(date_value)
FROM   det_test
WHERE  years_from_today(date_value) < 2;

Output

DATE_VALU SYSDATE   YEARS_FROM_TODAY(DATE_VALUE)
--------- --------- ----------------------------
01-JUL-09 20-SEP-10                   1.21861774

Then i create a function-based index on the new table.

CREATE INDEX det_test_fbi ON det_test (years_from_today(date_value));

Now, to see the implications of our DETERMINISTIC choice, change the date on the server (in a test environment of course) to move ahead a full year. Even though the date has changed, running the query again will still return the same value as before from YEARS_FROM_TODAY, along with the same row, because the index is used instead of executing the function.

SELECT date_value, SYSDATE, years_from_today(date_value)
FROM   det_test
WHERE  years_from_today(date_value) < 2;

Output:

DATE_VALU SYSDATE   YEARS_FROM_TODAY(DATE_VALUE)
--------- --------- ----------------------------
01-JUL-09 20-SEP-11                    1.2186201 

Without the WHERE clause, the query should return the following:

DATE_VALU SYSDATE   YEARS_FROM_TODAY(DATE_VALUE)
--------- --------- ----------------------------
01-JUL-09 20-SEP-11                   2.21867063

As is evident from the erroneous output, a function should never be created as deterministic unless it will ALWAYS return the same value given the same parameters.

And hence your assumption to make DBMS_ASSERT.NOOP doesnot stands true in all the cases.