## Syntax
```mermaid
%%{init: {
'theme': 'base',
'flowchart': { 'padding': '7', 'nodeSpacing': '20', 'rankSpacing': '20' },
'themeVariables': {
'fontSize': '11px',
'fontFamily': 'Arial'
}
}}%%
flowchart LR
pSqlParallelStatement_start((START)) --> pSqlParallelStatement_0_0[PARALLEL]:::quoted
pSqlParallelStatement_0_0 --> pSqlParallelStatement_0_1[maxDegree]
pSqlParallelStatement_0_0 --> pSqlParallelStatement_0_2
pSqlParallelStatement_0_0 --> pSqlParallelStatement_0_4
pSqlParallelStatement_0_1 --> pSqlParallelStatement_0_2[SAVE]:::quoted
pSqlParallelStatement_0_1 --> pSqlParallelStatement_0_4
pSqlParallelStatement_0_2 --> pSqlParallelStatement_0_3[EXCEPTIONS]:::quoted
pSqlParallelStatement_0_3 --> pSqlParallelStatement_0_4[BRANCH]:::quoted
pSqlParallelStatement_0_4 --> pSqlParallelStatement_0_5[label]
pSqlParallelStatement_0_4 --> pSqlParallelStatement_0_6
pSqlParallelStatement_0_5 --> pSqlParallelStatement_0_6[<a href="Invantive UniversalSQL/Grammar/Statement Types/PSQL Block" class="internal-link">pSqlBlockNoBatchSeparator</a>]
pSqlParallelStatement_0_6 --> pSqlParallelStatement_0_4
pSqlParallelStatement_0_6 --> pSqlParallelStatement_0_7[END]:::quoted
pSqlParallelStatement_0_7 --> pSqlParallelStatement_0_8[PARALLEL]:::quoted
pSqlParallelStatement_0_8 --> pSqlParallelStatement_0_9[;]:::quoted
pSqlParallelStatement_0_9 --> pSqlParallelStatement_end((END))
```
## Purpose
The `parallel` statement runs one or more blocks concurrently on one session and completes when all of them have completed: structured concurrency, in which no work outlives the statement. Every block is announced by the keyword `branch`, is a full PSQL block with its own declare and exception sections, and may carry a `<<label>>` which names it in error messages and diagnostics. Available from release 27.0; using a parallel construct without the parallel licence option raises `itgenpsp004`.
The optional `maxDegree` is a whole-number literal bounding the concurrency of the statement. The effective degree is the minimum of `maxDegree`, the `invantive-sql-psql-max-parallel` setting (default twice the number of logical processors on single-user products and 4 on multi-user and web-based hosts) and twice the number of logical processors, and is clamped to 1 while a session transaction is open. The setting value 1 runs every parallel construct sequentially with unchanged semantics, which is the first debugging step.
Without `save exceptions` a failing branch stops the dispatch of branches not yet started, running branches complete normally, and the original exception is re-raised with its original message code, enriched with the branch name; enclosing exception handlers keep matching. With `save exceptions` every branch is attempted; when at least one failed, the single collecting exception `parallel_error` (`itgenpsp002`) is raised, and [[dbms_parallel]].`saved_exceptions` enumerates the individual failures inside the handler which caught it.
Static rules keep the construct free of data races by construction, verified at compile time:
- A variable declared outside the statement may be written by at most one branch, and a written variable may not be read or written by any other branch (`itgenpsp007`). The writing branch's result is copied back to the enclosing frame on success. Aggregation across branches goes through a table, typically on `InMemoryStorage`.
- Handle-typed variables - a cursor, `excel_workbook`, `excel_worksheet`, `aws_s3_client`, `aws_ses_client` - may not be shared across branches at all (`itgenpsp009`): reading a cursor advances it and the objects behind these handles are not thread-safe.
- `return`, `goto` to an outside label and `exit`/`continue` of an enclosing loop are not allowed inside a branch (`itgenpsp015`).
- Statements which mutate session-global state - `set`, `use`, transaction statements, `alter session`, `execute file` and create or drop of views - are not allowed inside a branch (`itgenpsp003`); dynamically submitted statements are guarded at execution (`itgenpsp001`).
The `dbms_output` lines of one branch stay contiguous: they are buffered per branch and flushed as a whole the moment the branch completes. Cross-branch critical sections use the named locks of [[dbms_lock]].
## Parallel For Loops
Both for-loop forms accept the same parallelism after the iterand: `parallel` with an optional whole-number degree, then optionally `save exceptions`, then `loop`. Iterations are dispatched to the workers as they free up; the loop variable and every variable declared inside the body are private per iteration, and the body may not write any variable declared outside the loop (`itgenpsp008`). `reverse` can not be combined with `parallel` (`itgenpsp005`), since iteration order is unspecified under parallelism. `exit` stops the dispatch of new iterations; running iterations complete and the loop ends normally. The `dbms_output` lines of the iterations are flushed in driving-set order at the end of the loop, so a parallel run and a sequential run produce the same transcript.
## Examples
The following example runs two branches concurrently and reports through `dbms_output`, each branch's lines arriving contiguously:
```sql
begin
parallel 2
branch <<first>>
begin
dbms_output.put_line('first done');
end
branch <<second>>
begin
dbms_output.put_line('second done');
end
end parallel;
end;
-----------
first done
second done
```
The following example processes rows in parallel with eight workers, attempts every row despite failures and enumerates the failures afterwards:
```sql
declare
l_cnt number;
begin
begin
for r in
( select ivp.payment_id
from invoice_payments@InMemoryStorage ivp
)
parallel 8
save exceptions
loop
process_payment(r.payment_id);
end loop;
exception
when parallel_error
then
l_cnt := 0;
for f in
( select instance
from table(dbms_parallel.saved_exceptions())
)
loop
l_cnt := l_cnt + 1;
end loop;
dbms_output.put_line(l_cnt || ' payments failed.');
end;
end;
```