The `dbms_atomic` package provides named values which several branches of a parallel PSQL construct may safely change at the same time. A parallel loop body may not write a variable declared outside the loop, since several branches raising one counter would be a race; a named atomic value is the shared counterpart of such a variable. Values live as long as the session, are created by their first use and carry a kind — `NUMBER`, `TEXT` or `LIST OF TEXT` — which is fixed on creation and enforced on every later operation. Every procedure and function ends in the datatype of the value it operates on, `remove` excepted, which forgets a name whatever its kind. The values of a session are listed in the [Data Dictionary view](https://documentation.invantive.com/2017R2/data-dictionary-data-model/webhelp/data-dictionary-connector-systempsqlatomics.html) `SystemPSqlAtomics`. ## `increment_number`: Raise a named number and return the new value Adds the delta to the named numeric value and returns the value after adding, as one indivisible operation. Creates the value at zero on first use. The delta defaults to one and may be negative or fractional. The returned value is a position in time and not in loop order: a branch receiving three was the third to reach the call, so the value is safe to compare against a known total and unsafe to treat as an ordering. Calling this as a bare statement discards the result, which is the normal way to count when the position is of no interest. Parameters: - `name` (varchar2, required): Name of the value. - `delta` (numeric, optional): Amount to add; one when not given. Negative subtracts: that is how a decrement is spelled. ## `get_number`: Read a named number Returns the current value of the named number, or null when the name was never used. Null rather than zero on purpose: null means there is no such value where zero means a counter which has counted nothing, and the difference is what shows a mistyped name. Write `nvl(dbms_atomic.get_number('x'), 0)` for the other behaviour. Reading is diagnostic by nature: acting on a value read while other branches are still running is a race by definition. Parameters: - `name` (varchar2, required): Name of the value. ## `set_number`: Set a named number unconditionally Sets the named numeric value, creating it when it does not exist. Meant for initialising a counter where a script starts: values live as long as the session, so a second run within one session accumulates on top of the first unless the script resets. An unconditional set from inside a parallel construct is a lost update by design; use `compare_and_set_number` for the guarded form. Parameters: - `name` (varchar2, required): Name of the value. - `value` (numeric, required): The new value. ## `compare_and_set_number`: Set a named number when it holds the expected value Replaces the named numeric value by the new value when it currently equals the expected value, and returns whether it did, as one indivisible operation. An omitted or null expected value matches a name which does not exist yet, so exactly one of several branches calling `dbms_atomic.compare_and_set_number('finalised', null, 1)` receives true: that is how a wave elects one branch to do the finalisation, without a lock and without waiting. Parameters: - `name` (varchar2, required): Name of the value. - `expected` (numeric, optional): The value the caller expects; omitted or null matches a name which does not exist yet. - `value` (numeric, required): The new value. ## `append_text`: Append to a named text and return the number of appends Appends the text to the named text value and returns how many appends the value has had, as one indivisible operation. Creates the value empty on first use. A separator is inserted between appends and never in front of the first, so a value appended to once holds exactly what was appended. The order in which branches append is scheduling-dependent and must not be relied upon. Parameters: - `name` (varchar2, required): Name of the value. - `text` (varchar2, required): Text to append. - `separator` (varchar2, optional): Optional separator, inserted between appends and never before the first one. ## `get_text`: Read a named text Returns the accumulated text of the named value, or null when the name was never used. The Data Dictionary view deliberately reports the length of a text and not its content, since a value built by a script can hold anything the script could compute; this function is where the content is read, in the script which already had it. Parameters: - `name` (varchar2, required): Name of the value. ## `set_text`: Set a named text unconditionally Replaces the named text value wholesale, discarding whatever was accumulated, and creates it when it does not exist. The counterpart of `set_number` for the text kind; use `compare_and_set_text` for the guarded form. Parameters: - `name` (varchar2, required): Name of the value. - `value` (varchar2, required): The new text. ## `compare_and_set_text`: Set a named text when it holds the expected text Replaces the named text value by the new text when it currently equals the expected text, and returns whether it did, as one indivisible operation. The better one-shot flag of the two: where the numeric form records that a branch won, this one records which branch won, because the winner writes its own identity as the value it claims the name with. Parameters: - `name` (varchar2, required): Name of the value. - `expected` (varchar2, optional): The text the caller expects; omitted or null matches a name which does not exist yet. - `value` (varchar2, required): The new text. ## `collect_text`: Append an element to a named list and return the number of elements Appends the element to the named list of text and returns how many elements the list holds, as one indivisible operation. Creates the list empty on first use. Which branch contributed which element is recorded with the element and is visible through `elements_text`. The order across branches is scheduling-dependent and must not be relied upon. Parameters: - `name` (varchar2, required): Name of the value. - `value` (varchar2, required): The element to append. ## `elements_text`: The elements of a named list Enumerates the elements of the named list of text in append order: one row per element with `ORDINAL`, `VALUE`, `APPENDED_UTC` and `INSTANCE`. Yields no rows when the name was never used. A snapshot rather than a live enumeration, so a branch appending while the list is read can not disturb the reader. This is a table function and is used in a `select` through `table(...)`. Parameters: - `name` (varchar2, required): Name of the value. ## `remove`: Forget a named value Removes the named value whatever its kind, and does nothing when the name does not exist. Unlike `dbms_lock.release`, which raises on a lock the caller does not hold, this is deliberately idempotent: a release is one half of a balance obligation whereas `remove` is cleanup, and a script resetting its own names at the start should not have to know whether it ran before. It is the only reset available for the text and list kinds, and the only way to reuse a name for a different kind. Parameters: - `name` (varchar2, required): Name of the value.