ISNULL and WAS DEFAULTED in Oracle Fast Formula — Concepts
Open the Oracle Cloud HCM Formula Execution Errors page and you'll find four separate error conditions for missing data. They are not synonyms — the formula engine treats them as four different things, raised by four different runtime conditions:
| Engine error | What it actually means |
|---|---|
| Uninitialized Variable | Variable referenced before being assigned. Engine throws on read. |
| No Data Found | Non-array DBI returned zero rows. Suppressed if you declared DEFAULT FOR. |
| NULL Data Found | DBI returned a row, but the column value was NULL. Distinct from no-data. |
| Function Returned NULL | Formula function or value set returned a NULL value. |
Variable was declared in the formula's scope but never assigned. The engine carries an internal "uninitialized" flag on it. Reading it raises Uninitialized Variable.
ENGINE FLAGVariable contains a real value — including the empty string, zero, a sentinel like 'UNKNOWN', or a default substituted by DEFAULT FOR. From the variable's perspective, all of these are "has a value".
Variable carries an internal "null" flag. There is no NULL literal in the language — you cannot write l_var = NULL. NULL only enters when a function, value set, or nullable DBI passes one in from outside.
Mental model that actually works
NULL in Fast Formula behaves like a flag, not a value. So does "uninitialized". They are distinct internal states the engine tracks separately. A variable that was never assigned is not the same as a variable that holds an empty string, which is not the same as a variable carrying a real NULL handed to it from outside. Each state has its own detection mechanism.
WAS DEFAULTED on the DBI itself. Always declare DEFAULT FOR.WAS DEFAULTED on the input. Same mechanism as DBIs.ISNULL(). Only place a real NULL can land in your variable.Every DBI or input value that could return no data must declare a fallback via DEFAULT FOR. When the engine fetches and finds nothing, it silently substitutes the declared default. WAS DEFAULTED lets you detect that substitution after the fact.
/* Form 1: DBI used the default */ IF (DBI_NAME WAS DEFAULTED) THEN /* engine fell back — no real data */ /* Form 2: DBI had real data */ IF (DBI_NAME WAS NOT DEFAULTED) THEN /* fetched from the database */
DEFAULT FOR PER_PERSON_ENTERPRISE_HIRE_DATE IS '0001/01/01 00:00:00' (date) DEFAULT FOR PER_REL_ORIGINAL_DATE_OF_HIRE IS '0001/01/01 00:00:00' (date) IF (PER_REL_ORIGINAL_DATE_OF_HIRE WAS DEFAULTED) THEN (L_Hire_Date = PER_PERSON_ENTERPRISE_HIRE_DATE) ELSE (L_Hire_Date = PER_REL_ORIGINAL_DATE_OF_HIRE) L_Eff_Date = GET_CONTEXT(EFFECTIVE_DATE, '4712/12/31 00:00:00' (date)) Length_of_service = DAYS_BETWEEN(L_Eff_Date, L_Hire_Date) / 365
Why the check matters: if PER_REL_ORIGINAL_DATE_OF_HIRE has no value, the formula would otherwise fall back to 0001/01/01, producing an artificially long length of service and pushing the employee into a higher accrual band than intended.
This is the part of the story my previous blog didn't capture fully. ISNULL() is not a general "missing data" check — it's much narrower than that, and a careful reader helped me see why.
From what I can tell, uninitialized variables are not null — they have some special flag that marks them as uninitialized. My understanding is that null is similar; it isn't a value as such, the variable is flagged as holding a null. There is no direct way to set a fast formula variable as null, but it can happen if it is a return value from a function. Where a function or value set doesn't return a value at all, that doesn't result in a null — it is uninitialized, and should give you the default value specified in the function call. If there is no default specified, I'd expect that to error out, not generate a null.
— Bryan, reader feedback
Bryan's point reframes the whole ISNULL question. The only way a NULL value lands in a Fast Formula variable is when a function, value set, or nullable DBI fetch passes one in from outside. In practice that means a small number of cases:
GET_VALUE_SETreturns where the underlying DB column allows NULL- Called-formula outputs that pass through a NULL received from a value set or nullable DBI fetch
Notice what's not on this list: a function or value set that returns nothing at all. Per Bryan's reading, that path leaves the receiving variable uninitialized, and the engine should fall back to the default parameter you passed in the function call. If you didn't pass a default, expect a runtime error, not a silent NULL.
Defensive practice
NULLs are rarer than developers assume. If you only deal with standard HCM DBIs and you always declare DEFAULT FOR, you may go entire projects without encountering one. But when you call value sets against DFFs, custom tables, or nullable EIT columns, NULL becomes a genuine possibility. Bryan's habit — and a sensible one to adopt — is to wrap every GET_VALUE_SET call in an ISNULL() check by default, even when a null isn't expected. It adds two lines, and it makes the formula more robust when underlying data shifts over time.
⚠ The Y/N Question — Verify Empirically
Oracle's Cloud HCM documentation does not specify the return values of ISNULL(), and community sources offer differing readings. Bryan reads the convention as ISNULL(x) = 'N' meaning "x is null", with 'Y' meaning "x is not null" (and in his reading, 'Y' on its own doesn't indicate whether the variable is uninitialized or holds a real value). Verify it directly in your dev pod before relying on it.
/* Value set returns go into a local variable */ L_PersonNumber = GET_VALUE_SET('AON_GET_PERSON_NUMBER', '|=P_SSN=''' || TRIM(POSITION1) || '''') /* Verify the Y/N convention in your pod before shipping */ IF ISNULL(L_PersonNumber) = 'Y' THEN ( ESS_LOG_WRITE('WARNING: No person for SSN ' || TRIM(POSITION1)) RETURN ) ESS_LOG_WRITE('Person Number: ' || L_PersonNumber)
Developers sometimes try to detect missing DBI data by reading the DBI into a local variable and then calling ISNULL() on it. The syntax looks familiar — it mirrors the IS NULL pattern from PL/SQL — but it can't work, and understanding why is the cleanest way to internalise the difference between the two mechanisms.
DEFAULT FOR PER_PERSON_NUMBER IS ' ' l_person = PER_PERSON_NUMBER /* WRONG — l_person holds the default ' ', not a NULL */ IF ISNULL(l_person) = 'Y' THEN ...
By the time l_person is assigned, the engine has already done its work. If PER_PERSON_NUMBER returned no data, the engine substituted the DEFAULT FOR value (' ') and set its internal "defaulted" flag on the DBI. l_person now holds a real one-character string — not a NULL. ISNULL() sees a real value and reports accordingly. Whichever way the Y/N convention resolves, the check never detects the default substitution.
The only thing that knows about the substitution is the engine's flag on the DBI itself, and the only way to read that flag is WAS DEFAULTED on the DBI directly:
DEFAULT FOR PER_PERSON_NUMBER IS 'UNKNOWN' /* CORRECT — reads the engine's flag on the DBI itself */ IF (PER_PERSON_NUMBER WAS DEFAULTED) THEN /* no person data — handle gracefully */ ELSE l_person = PER_PERSON_NUMBER
Notice that the CORRECT version uses 'UNKNOWN' as the default rather than a blank — and it doesn't matter. WAS DEFAULTED reads the engine's flag, not the value, so the actual default text is irrelevant. You could use 'X', 'NO_DATA', or '12345' — the check still works.
With thanks to Bryan for proofreading this post and helping surface facts that aren't documented anywhere obvious.
Comments
Post a Comment