Metadata-Version: 2.4
Name: oratapi
Version: 3.0.1
Summary: Generate Oracle PL/SQL table APIs, views, triggers, and utPLSQL scaffolds or executable tests from database metadata.
License-Expression: MIT
License-File: LICENSE
Keywords: python,oracle,database,plsql,table api,stored procedures,views,database triggers,code generator,automation
Author: Clive Bostock
Requires-Python: >=3.10,<3.14
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Development Status :: 4 - Beta
Classifier: Intended Audience :: Developers
Classifier: Topic :: Database
Classifier: Topic :: Software Development :: Code Generators
Classifier: Operating System :: OS Independent
Requires-Dist: Markdown (==3.7)
Requires-Dist: Pygments (>=2.18.0)
Requires-Dist: beautifulsoup4 (==4.12.3)
Requires-Dist: bs4 (==0.0.2)
Requires-Dist: cffi (>=1.17.1)
Requires-Dist: charset-normalizer (>=3.4.1)
Requires-Dist: configobj (>=5.0.9)
Requires-Dist: cryptography (>=43.0.3)
Requires-Dist: idna (>=3.10)
Requires-Dist: markdown-it-py (>=3.0.0)
Requires-Dist: mdurl (>=0.1.2)
Requires-Dist: oracledb (>=2.5.0)
Requires-Dist: packaging (>=24.2)
Requires-Dist: pycparser (>=2.22)
Requires-Dist: requests (>=2.32.3)
Requires-Dist: rich (>=13.9.4)
Requires-Dist: soupsieve (==2.6)
Requires-Dist: tomli (>=2.2.1)
Requires-Dist: typing_extensions (>=4.12.2)
Requires-Dist: urllib3 (==2.5.0)
Project-URL: Documentation, https://github.com/avalon60/OraTAPI#readme
Project-URL: Homepage, https://github.com/avalon60/OraTAPI
Project-URL: Issues, https://github.com/avalon60/OraTAPI/issues
Project-URL: Repository, https://github.com/avalon60/OraTAPI
Description-Content-Type: text/markdown

# OraTAPI - Oracle Table API Generator 

![OraTAPI 1.0](./assets/images/OraTAPILogo-readme.png)

Version 3.0.1

- [OraTAPI - Oracle Table API Generator](#oratapi---oracle-table-api-generator)
  - [About OraTAPI](#about-oratapi)
  - [Features & Limitations](#features--limitations)
    - [Features](#features)
    - [Limitations](#limitations)
    - [Documentation](#documentation)
  - [Preinstallation](#preinstallation)
    - [Preparing the Environment](#preparing-the-environment)
    - [Development Environment](#development-environment)
    - [Familiarisation with the Layout](#familiarisation-with-the-layout)
  - [Installation](#installation)
    - [Preferred: install from PyPI or wheel](#preferred-install-from-pypi-or-wheel)
    - [Legacy: extracted source distribution install](#legacy-extracted-source-distribution-install)
    - [Optional: Create A Personal Launcher Script](#optional-create-a-personal-launcher-script)
    - [Optional: Use Shell Aliases On macOS Or Linux](#optional-use-shell-aliases-on-macos-or-linux)
    - [Development Checkout](#development-checkout)
  - [Post Installation](#post-installation)
    - [Using Oracle Instant Client](#using-oracle-instant-client)
  - [Performing Upgrades](#performing-upgrades)
    - [Migrations](#migrations)
    - [In-situ upgrades](#in-situ-upgrades)
      - [The check_pypi_for_updates Property](#the-check_pypi_for_updates-property)
      - [The check_github_for_updates Property (Deprecated)](#the-check_github_for_updates-property-deprecated)
  - [The Primary Components](#the-primary-components)
  - [Modifying Templates](#modifying-templates)
    - [Command-line Tools](#command-line-tools)
  - [Usage](#usage)
    - [Repository-managed profiles](#repository-managed-profiles)
    - [Profile bundles with oratapi-orc](#profile-bundles-with-oratapi-orc)
      - [Suite settings and substitution formats](#suite-settings-and-substitution-formats)
      - [Worked example: cfg_core.cfg_json_mappings](#worked-example-cfg_corecfg_json_mappings)
      - [Executable test configuration](#executable-test-configuration)
    - [Template overrides for one run](#template-overrides-for-one-run)
    - [Examples](#examples)
      - [Basic Example](#basic-example)
      - [More Advanced Example](#more-advanced-example)
      - [Explicitly Specifying Credentials](#explicitly-specifying-credentials)
    - [Full Command-Line Arguments:](#full-command-line-arguments)
  - [Output Structure](#output-structure)
  - [Configuration Settings](#configuration-settings)
    - [Control Files](#control-files)
    - [The OraTAPI.ini File](#the-oratapiini-file)
      - [\[project\]](#project)
      - [\[copyright\]](#copyright)
      - [\[behaviour\]](#behaviour)
      - [\[formatting\]](#formatting)
      - [\[file_controls\]](#file_controls)
      - [\[api_controls\]](#api_controls)
      - [\[logger\]](#logger)
      - [\[schemas\]](#schemas)
      - [\[misc\]](#misc)
      - [\[suite\]](#suite)
      - [\[ut_controls\]](#ut_controls)
      - [\[console\]](#console)
      - [Example configuration file:](#example-configuration-file)
    - [Fine-Grained File Controls](#fine-grained-file-controls)
      - [Controlling File Updates](#controlling-file-updates)
      - [PI (Personal Information) Columns & Logging](#pi-personal-information-columns--logging)
  - [Auto Column Management](#auto-column-management)
    - [What are Auto-managed Columns?](#what-are-auto-managed-columns)
    - [Configuring the Column Management Method](#configuring-the-column-management-method)
    - [The col_auto_maintain_method Property](#the-col_auto_maintain_method-property)
      - [Maintained by Trigger](#maintained-by-trigger)
      - [Maintained by Column Expression](#maintained-by-column-expression)
    - [The auto_maintained_cols Property](#the-auto_maintained_cols-property)
    - [The row_vers_column_name Property](#the-row_vers_column_name-property)
  - [utPLSQL Support](#utplsql-support)
    - [One command for production code and executable tests](#one-command-for-production-code-and-executable-tests)
      - [Understanding UT scenario counts](#understanding-ut-scenario-counts)
      - [Reading a FIXTURE REQUIRED section](#reading-a-fixture-required-section)
    - [Generate TAPIs now and executable tests later](#generate-tapis-now-and-executable-tests-later)
    - [Overview](#overview)
    - [Controls](#controls)
  - [Template Substitution Strings](#template-substitution-strings)
  - [Connection Manager](#connection-manager)
    - [OCI IAM token connections](#oci-iam-token-connections)
  - [Sample Generated Table API Packages:](#sample-generated-table-api-packages)
    - [Basic Templates Example](#basic-templates-example)
    - [Logger Templates Example](#logger-templates-example)
  - [Licensing](#licensing)


## About OraTAPI
OraTAPI is a Python-based tool that generates PL/SQL APIs for Oracle database tables. This tool simplifies the process of interacting with Oracle database tables by creating customisable and standardised APIs for common database operations like `insert`, `update`, `delete`, `select`, operations and more.  

OraTAPI connects to an Oracle database, retrieves table and column metadata, and generates the API package files in a staging area. These files can then be deployed to an Oracle database.

---

## Features & Limitations
### Features
OraTAPI is a versatile tool that offers the following configurable options:

- **Metadata-Driven**: Automatically generates PL/SQL APIs based on Oracle database metadata.  
- **Customisable APIs**: Allows you to define API names, signatures, and behaviours through a configuration file.  
- **Table Triggers**: Generates customisable table-level trigger code.  
- **View Generation**: Automatically generates view DDL scripts.  
- **utPLSQL Support**: Generate utPLSQL package spec and starter body for utPLSQL tests 
- **Template-Based Customisation**: Code generation is largely template-driven, with example templates provided, offering extensive customisation capabilities.  
- **Optimistic Locking Support**: Supports concurrency control with "row version" columns for implementing optimistic locking.  
- **PL/SQL Logger Integration**: Easily integrates with the [PLSQL logging utility](https://github.com/OraOpenSource/Logger), including mechanisms to blocklist specific columns from being logged.  
- **Liquibase** Liquibase templates are provided as an option.
- **Fine-Grained Control**: Provides detailed control over which components are generated for specific tables.  
- **Auto-Maintained Column Support**: Offers a flexible solution for managing auto-maintained columns, either through generated triggers or configurable column expressions.  
- **Organised Output**: Output files are neatly arranged in staging directories for streamlined deployment.  
- **Error Handling**: Configurable behaviour for handling missing tables, with options to skip or halt processing.  
- **Connection Manager**: Includes a connection manager (similar to named connections in SQLcl), allowing password and OCI IAM token connections to be stored and used transparently.

### Limitations
LDAP-based connections require thick mode.

Wallet-based connections can work in thin mode, but if the target database requires Oracle Native Network Encryption
or checksumming, you must use thick mode with Oracle Instant Client.

OCI IAM database token connections use token and private-key files maintained outside OraTAPI. OraTAPI reads these
files but never creates or renews them. Thin mode requires an `ewallet.pem` file and its wallet password when the PEM
is encrypted. Thick mode requires `cwallet.sso` and a compatible Oracle Instant Client.

To configure thick mode, OraTAPI can use any of the following:

- Pass `--oracle-client-dir <instant-client-dir>` to `ora_tapi` for the current run.
- Set and export the `ORACLE_IC_HOME` shell variable to point to the Instant Client location.
- Place the basic [Oracle Instant Client](https://www.oracle.com/database/technologies/instant-client/downloads.html) in an `oracle_client` folder under the active profile directory, for example `~/OraTAPIProfiles/configs/<active-profile>/oracle_client`.
- Place it in an `oracle_client` folder beside the virtual environment running OraTAPI, for example `/opt/oratapi/oracle_client` when the environment is `/opt/oratapi/.venv`. This is checked after the profile directory.
---

### Documentation
Please note that if you are working with the latest minor version of OraTAPI, it's always worth selecting the 
`develop` branch from the GitHub OraTAPI page Code tab. This will ensure that you are looking at the latest 
version of the README.md. Alternatively, you should be able to navigate straight to the "develop" branch documentation, 
from [here](https://github.com/avalon60/OraTAPI/tree/develop?tab=readme-ov-file#oratapi---oracle-table-api-generator).

## Preinstallation
### Preparing the Environment

In order to make OraTAPI installable, you need Python 3.10, 3.11, 3.12, or 3.13.  
On macOS, you can install Python using:  

`brew install python@3.11` # Install Python 3.11 - safe choice.

On Windows, ensure that you obtain Python from: https://www.python.org/downloads/windows/
you should preferably download Python 3.11, 3.12, or 3.13. 

### Development Environment

OraTAPI uses two different workflows:

- Development uses Poetry for dependency management, virtual environment management, and lock-file maintenance.
- Deployment is now wheel-first. The packaged `.tar.gz` together with `setup.sh` or `setup.ps1` remains available only for the legacy extracted-install model.

If you are contributing to OraTAPI or running it directly from a working copy, use Poetry:

```bash
poetry config virtualenvs.in-project true --local
poetry install --with dev --sync
```

This creates a project-local `.venv` by default. The shell wrappers under `bin/` now detect either `venv` or `.venv`, so working-copy execution remains straightforward:

```bash
./bin/ora_tapi.sh -h
./bin/quick_config.sh -t basic
```

On Windows PowerShell:

```powershell
.\bin\ora_tapi.ps1 -h
.\bin\quick_config.ps1 -t basic
```

For development maintenance tasks:

```bash
./utils/build-e.sh
./utils/freeze.sh
./utils/package.sh
```

These now map to Poetry operations:

- `build-e` runs `poetry install --sync`
- `freeze` exports `requirements.txt` from `poetry.lock`
- `package.sh` exports `requirements.txt` and builds the release artefacts used for packaging

Poetry is required only on the development machine. It is not required on the target system when installing from a packaged release.

### Familiarisation with the Layout

OraTAPI uses the installed package, a developer-local runtime home, and optionally repository-managed configuration:

- The installed package location inside the Python environment. In the preferred wheel/PyPI model this is read-only and contains the Python modules, packaged default resources, and console-script entry points.
- The runtime home, `~/OraTAPIProfiles`, which contains the user-instantiated configuration, CSV control files, templates, and default staging directories.
- With `--repo PATH`, profiles and bundles live in `PATH/OraTAPIProfiles`. Runtime output and credentials remain local. See [repository-managed profiles](#repository-managed-profiles).

In the wheel-first model, you typically interact with OraTAPI through the installed console scripts:

```text
ora_tapi
quick_config
profile_mgr
conn_mgr
update_ora_tapi
```

If you are developing from a working copy, or using an extracted legacy install, the `bin/` wrappers remain available.

The runtime home created by `quick_config` looks similar to this:

```
~/OraTAPIProfiles
├── active_config
├── configs
│   ├── basic
│   │   ├── created_version.md
│   │   ├── purpose.md
│   │   └── resources
│   │       ├── config
│   │       │   ├── OraTAPI.csv
│   │       │   ├── OraTAPI.ini
│   │       │   └── pi_columns.csv
│   │       └── templates
│   │           ├── column_expressions
│   │           ├── misc
│   │           ├── packages
│   │           └── ut_packages
│   ├── liquibase
│   ├── logger
│   └── llogger
├── orc_bundles
│   ├── README.md
│   └── example.toml.sample
├── staging
└── ut_staging
```

The packaged defaults remain in the installation, but OraTAPI reads and writes user-owned runtime files from `~/OraTAPIProfiles`. The active profile is determined by the plain-text file `~/OraTAPIProfiles/active_config`, whose content is simply the selected profile name. The active `OraTAPI.ini`, CSV files, and instantiated `.tpt` templates therefore live under `~/OraTAPIProfiles/configs/<active-profile>/resources`, not in the installation directory.

If `~/OraTAPIProfiles/active_config` does not yet exist, OraTAPI stops with setup guidance. If no profiles exist yet, it tells you to run `quick_config`. If profile directories already exist, it tells you to activate one with `profile_mgr`.

The profile model allows you to maintain multiple named OraTAPI configurations side by side. For example, you might keep one profile for basic generation, one for Liquibase-enabled output, and one for logger-based templates. Profiles can also be used to support different project requirements, where each project needs its own configuration, template customisations, and control-file settings. Switching profiles updates only `~/OraTAPIProfiles/active_config`; it does not copy files or rely on symbolic links.

If you want to experiment with template changes without disturbing an existing working profile, consider cloning the current profile first with `profile_mgr -C <source-profile> <new-profile>`, then make the template changes in the new profile and activate it when ready. For example, one profile might use a trigger template that maintains `created_by`, `created_on`, `updated_by`, `updated_on`, and `row_version`, while another profile might rely on `default on null` table DDL for `created_by` and `created_on` and leave the trigger to maintain only `updated_by`, `updated_on`, and optionally `row_version`.

Each profile may also contain two optional metadata files at the profile root:

- `purpose.md`: A one-line description of the profile's intended purpose.
- `created_version.md`: The OraTAPI version recorded when the profile was created, bootstrapped, or migrated.

These values are shown by `profile_mgr --list` and `profile_mgr --show-active`. If either metadata file is missing, the value is reported as `Unknown`.

## Installation

The supported public installation model is wheel-first. Install OraTAPI into a Python 3.10, 3.11, 3.12, or 3.13 virtual environment and run the installed console scripts from that environment. This is the recommended path for both local wheel installs and PyPI installs.

### Recommended directory layout

Keep the installation and personal profiles separate. Both default to your home directory;
custom parent directories are supported, but the directory names remain `OraTAPI` and
`OraTAPIProfiles`.

```text
<User home>/
├── OraTAPI/                       # Installed application and platform-specific libraries
│   ├── .venv/                    # Python environment containing the installed wheel
│   │   ├── bin/                  # Commands on macOS/Linux
│   │   └── Scripts/              # Commands on Windows (instead of bin)
│   └── oracle_client/            # Optional extracted Oracle Instant Client
├── OraTAPIProfiles/               # Personal configuration and runtime files
│   ├── active_config             # Selected personal profile name
│   ├── configs/
│   │   ├── basic/
│   │   │   └── resources/
│   │   │       ├── config/
│   │   │       └── templates/
│   │   ├── liquibase/
│   │   ├── logger/
│   │   └── llogger/
│   ├── orc_bundles/
│   ├── repo_state/               # Local selection for each repository
│   ├── staging/                  # Generated output, created when needed
│   └── ut_staging/
└── .OraTAPI/                     # Existing local credentials; not migrated
```

`<User home>` is normally `/Users/<username>` on macOS or `C:\Users\<username>` on
Windows. OraTAPI uses Python's home-directory lookup on both systems. The original wheel
file can be stored elsewhere or removed after installation. Do not move an existing virtual
environment: recreate it and reinstall the wheel at the new location.

### Install into the recommended location

The repository and source distribution include `install_oratapi.py`, a standalone installer
using Python's standard library. It creates `OraTAPI/.venv` and installs a wheel, or installs
from PyPI when `--wheel` is omitted. Run it using a supported Python version:

macOS / Linux:

```bash
python3 install_oratapi.py --wheel /path/to/oratapi-<x.y.z>-py3-none-any.whl
export PATH="$HOME/OraTAPI/.venv/bin:$PATH"
quick_config -t basic
oratapi --locations
```

Windows PowerShell:

```powershell
py -3.12 install_oratapi.py --wheel 'C:\Downloads\oratapi-<x.y.z>-py3-none-any.whl'
$env:Path = "$HOME\OraTAPI\.venv\Scripts;$env:Path"
quick_config -t basic
oratapi --locations
```

The PATH changes above apply to the current terminal. Add the executable directory to your
user PATH for future terminals, or use its full path in scripts and scheduled jobs. Activation
is optional. Existing aliases or functions can take precedence over PATH; check `type -a oratapi`
on macOS or `Get-Command oratapi` in PowerShell.

Use `--home /some/parent/OraTAPI` or `--home 'D:\Tools\OraTAPI'` for another installation
location. Setup refuses a directory containing old profile data. For an existing deployment,
first install the new release into a separate `OraTAPI` directory and follow the migration
instructions below. Do not run `quick_config` over an unmigrated home.

### Personal profile locations and migration

Personal profiles default to `~/OraTAPIProfiles`. The following configuration commands accept
`--profiles-home PATH` and `--locations`: `oratapi`, `quick_config`, `profile_mgr`, and
`oratapi-orc`, including their command aliases. Location precedence is:

1. `--profiles-home PATH` for the current command.
2. `ORATAPI_PROFILES_HOME` from the environment.
3. `OraTAPIProfiles` in the user's home directory.

The complete path must end in `OraTAPIProfiles`; OraTAPI does not append the name automatically.
Relative paths are resolved from the current directory, so use absolute paths in persistent
settings. For example:

```bash
export ORATAPI_PROFILES_HOME="/Volumes/Work/OraTAPIProfiles"
quick_config -t basic
```

```powershell
$env:ORATAPI_PROFILES_HOME = 'D:\Work\OraTAPIProfiles'
quick_config -t basic
```

`--repo PATH` still selects `PATH/OraTAPIProfiles` for profiles and bundles. The personal profiles
home still holds local repository-selection state and default staging output. Orchestration passes
the selected personal home to every child command. Credentials remain under `~/.OraTAPI`.

To copy an existing personal runtime home into the new location:

```text
profile_mgr --migrate-home
```

The source defaults to the old `~/OraTAPI`. Supply `--migrate-home OLD_HOME` for another source,
and `--profiles-home PATH` for another destination. The operation copies the whole profile tree,
including bundles, state, staging and profile-local clients, verifies file hashes and directory
contents, and retains the source. It refuses an existing destination, symbolic links, junctions
and a source mixed with a Python environment. Stop other OraTAPI commands while migrating.
If the destination already exists, choose a new parent directory or resolve the conflict manually;
the migration never merges or overwrites it.

Review any absolute paths in your configuration afterwards: file contents are preserved unchanged.
After checking the new home, explicitly archive the old directory before reusing `~/OraTAPI` for
installation. Migration never removes it. If only the legacy home exists, normal configuration
commands stop with migration guidance instead of silently creating a fresh profiles home.

`oratapi --locations` reports the running Python executable, version, personal home, configuration
root, selected profile and discovered Instant Client directory without creating files, loading client
libraries or connecting to a database. It also accepts `--repo`, `--profiles-home`, `--profile`
and `--oracle-client-dir`. A missing home is reported without being initialised.

### Manual wheel installation

If managing the environment yourself, create a dedicated `OraTAPI` directory first. Check that it
contains no old profiles before continuing. Direct `pip` installation does not perform the standalone
installer's directory checks. From your chosen installation directory:

1. Create and activate a virtual environment.

   macOS / Linux
   ```bash
   python3 -m venv .venv
   source .venv/bin/activate
   ```

   Windows PowerShell
   ```powershell
   python -m venv .venv
   .\.venv\Scripts\Activate.ps1
   ```

   If you use the Windows Python launcher and have a specific supported version installed, you can also use:
   ```powershell
   py -3.11 -m venv .venv
   .\.venv\Scripts\Activate.ps1
   ```

2. Install OraTAPI from a built wheel or from PyPI when published.

   From a local release artefact:
   ```bash
   pip install dist/oratapi-<x.y.z>-py3-none-any.whl
   ```

   From PyPI:
   ```bash
   pip install oratapi
   ```

3. Confirm the console scripts are available. Wheel installs provide `oratapi`, `ora_tapi`, and `ora-tapi` for the main controller, along with underscore and dashed forms for the companion tools such as `quick_config` / `quick-config`, `profile_mgr` / `profile-mgr`, `conn_mgr` / `conn-mgr`, and `update_ora_tapi` / `update-ora-tapi`.

   ```bash
   oratapi --help
   ora_tapi --help
   ora-tapi --help
   quick_config --help
   quick-config --help
   profile_mgr --help
   profile-mgr --help
   conn_mgr --help
   conn-mgr --help
   update_ora_tapi --help
   update-ora-tapi --help
   ```

Poetry is not required on the target system for wheel installation.

If you need Oracle Instant Client, download it separately and place its extracted libraries in an
`oracle_client` folder beside `.venv`. For example, an environment at `~/OraTAPI/.venv` uses
`~/OraTAPI/oracle_client`; on Windows, `C:\OraTAPI\.venv` uses `C:\OraTAPI\oracle_client`.
OraTAPI checks this location automatically after any explicit client setting and the active profile's
client directory. See [Using Oracle Instant Client](#using-oracle-instant-client) for the lookup order
and directory layout.

### Legacy: extracted source distribution install

The packaged `.tar.gz` source distribution and `setup.sh` / `setup.ps1` are still available for the legacy extracted-install model. This path is retained only for compatibility with existing extracted installs. It is no longer the recommended deployment or upgrade method.

If you are moving away from an older extracted install, you do not need to preserve the old installation directory itself. OraTAPI stores user-owned runtime data under `~/OraTAPIProfiles` rather than inside the install tree, so you can remove the old extracted install and perform a fresh install without losing your profiles, active configuration, instantiated templates, CSV files, or profile-local Oracle Instant Client files.

If you kept Instant Client inside the old installation directory, move it to the `oracle_client` folder
beside the new virtual environment before removing that installation.

### Optional: Create A Personal Launcher Script

If you install OraTAPI into a dedicated virtual environment and want a simpler day-to-day command, you can create your own small wrapper script that calls the venv's installed executable directly. This is optional convenience only. It does not change the supported installation model. Replace the example paths below with the actual path to your OraTAPI virtual environment.

For Linux or macOS:

```bash
#!/usr/bin/env bash
exec "/path/to/OraTAPI/.venv/bin/ora-tapi" "$@"
```

For Windows PowerShell:

```powershell
& "C:\path\to\OraTAPI\.venv\Scripts\ora-tapi.exe" @args
```

You can do the same for `quick-config`, `profile-mgr`, or `conn-mgr` by replacing the executable name.

This approach is preferred over embedding environment-activation logic in the wrapper. It keeps the launcher simple and makes it obvious which virtual environment OraTAPI is using.

### Optional: Use Shell Aliases On macOS Or Linux

For interactive terminal use, aliases can call the installed commands directly without activating the virtual environment. Add the following to `~/.zshrc` for Zsh (the default shell on macOS), or `~/.bashrc` for Bash on Linux. Define the installation directory once in `ORATAPI_HOME`; if you recreate the installation at a new location, update that line. This example uses a virtual environment at `~/OraTAPI/.venv`.

```bash
# OraTAPI commands from the dedicated pip virtual environment.
ORATAPI_HOME="$HOME/OraTAPI"

alias conn_mgr='"$ORATAPI_HOME/.venv/bin/conn_mgr"'
alias conn-mgr='"$ORATAPI_HOME/.venv/bin/conn-mgr"'
alias migrate_config='"$ORATAPI_HOME/.venv/bin/migrate_config"'
alias migrate-config='"$ORATAPI_HOME/.venv/bin/migrate-config"'
alias oratapi='"$ORATAPI_HOME/.venv/bin/oratapi"'
alias ora_tapi='"$ORATAPI_HOME/.venv/bin/ora_tapi"'
alias ora-tapi='"$ORATAPI_HOME/.venv/bin/ora-tapi"'
alias profile_mgr='"$ORATAPI_HOME/.venv/bin/profile_mgr"'
alias profile-mgr='"$ORATAPI_HOME/.venv/bin/profile-mgr"'
alias quick_config='"$ORATAPI_HOME/.venv/bin/quick_config"'
alias quick-config='"$ORATAPI_HOME/.venv/bin/quick-config"'
alias update_ora_tapi='"$ORATAPI_HOME/.venv/bin/update_ora_tapi"'
alias update-ora-tapi='"$ORATAPI_HOME/.venv/bin/update-ora-tapi"'
alias oratapi-orc='"$ORATAPI_HOME/.venv/bin/oratapi-orc"'
alias oratapi_orc='"$ORATAPI_HOME/.venv/bin/oratapi_orc"'
```

These aliases cover all OraTAPI console commands, including their underscore and dashed forms. Arguments pass through normally, for example `oratapi --help` or `profile_mgr --list`.

`ORATAPI_HOME` is a shell variable used by these aliases, not an OraTAPI configuration setting. It does not move the profiles, bundles or staging files under `~/OraTAPIProfiles`, and it does not need to be exported. The single quotes defer variable expansion until you invoke an alias; the inner double quotes allow spaces in the installation path.

Open a new terminal or reload your shell configuration:

```bash
source ~/.zshrc
# For Bash:
source ~/.bashrc
```

For scripts and scheduled jobs, use the executable's full path or a personal launcher script, since these environments generally do not load interactive shell aliases.

After reloading, check `type -a quick-config` and `type -a oratapi-orc` to confirm which commands your shell will use. Existing aliases can otherwise continue pointing to an older installation even after you activate a different virtual environment.

### Optional: Use PowerShell Functions On Windows

PowerShell aliases cannot include an executable path and reliably forward command-line arguments, so use functions instead. Add the following to your PowerShell profile. Open it with `notepad $PROFILE`; if it does not yet exist, create it first with `New-Item -ItemType File -Path $PROFILE -Force`. Define the installation directory once in `$OraTapiHome`; if you recreate the installation at a new location, update that line. This example uses a virtual environment at `C:\OraTAPI\.venv`.

```powershell
# OraTAPI commands from the dedicated pip virtual environment.
$OraTapiHome = 'C:\OraTAPI'

function conn_mgr { & "$OraTapiHome\.venv\Scripts\conn_mgr.exe" @args }
function conn-mgr { & "$OraTapiHome\.venv\Scripts\conn-mgr.exe" @args }
function migrate_config { & "$OraTapiHome\.venv\Scripts\migrate_config.exe" @args }
function migrate-config { & "$OraTapiHome\.venv\Scripts\migrate-config.exe" @args }
function oratapi { & "$OraTapiHome\.venv\Scripts\oratapi.exe" @args }
function ora_tapi { & "$OraTapiHome\.venv\Scripts\ora_tapi.exe" @args }
function ora-tapi { & "$OraTapiHome\.venv\Scripts\ora-tapi.exe" @args }
function profile_mgr { & "$OraTapiHome\.venv\Scripts\profile_mgr.exe" @args }
function profile-mgr { & "$OraTapiHome\.venv\Scripts\profile-mgr.exe" @args }
function quick_config { & "$OraTapiHome\.venv\Scripts\quick_config.exe" @args }
function quick-config { & "$OraTapiHome\.venv\Scripts\quick-config.exe" @args }
function update_ora_tapi { & "$OraTapiHome\.venv\Scripts\update_ora_tapi.exe" @args }
function update-ora-tapi { & "$OraTapiHome\.venv\Scripts\update-ora-tapi.exe" @args }
function oratapi-orc { & "$OraTapiHome\.venv\Scripts\oratapi-orc.exe" @args }
function oratapi_orc { & "$OraTapiHome\.venv\Scripts\oratapi_orc.exe" @args }
```

The functions cover all OraTAPI console commands, including their underscore and dashed forms. `@args` forwards arguments to the installed executable, so `oratapi --help` and `profile_mgr --list` work as expected. `$OraTapiHome` is only a PowerShell variable; it does not move the profiles, bundles or staging files under `C:\Users\<your-user>\OraTAPIProfiles`.

Open a new PowerShell window or reload the profile with:

```powershell
. $PROFILE
```

For scripts and scheduled jobs, use the executable's full path or a personal launcher script, since these environments generally do not load an interactive PowerShell profile. After reloading, use `Get-Command quick-config` or `Get-Command oratapi-orc` to confirm which command PowerShell will use.

### Development Checkout

If you cloned the Git repository and want a local development environment instead of installing from a release archive:

1. Clone the repository and open it in your editor or IDE.
2. Create the in-project virtual environment with Poetry:

   ```powershell
   poetry config virtualenvs.in-project true --local
   poetry install --with dev --sync
   ```

   This creates `.venv` in the project root.

3. In PyCharm, set the interpreter to:

   ```text
   <repo>\.venv\Scripts\python.exe
   ```

If PyCharm still shows stale package information after `poetry install` has completed, recreate the interpreter entry
or invalidate the IDE caches. The command-line environment created by Poetry is the source of truth.

## Post Installation
The next step is to initialise the runtime home at `~/OraTAPIProfiles`. OraTAPI will not generate code until the runtime config and templates have been instantiated. The `quick_config` tool bootstraps the built-in profiles under `~/OraTAPIProfiles/configs`, copies the packaged defaults into each profile's `resources` directory, and points `~/OraTAPIProfiles/active_config` at the selected built-in profile. Several options are available:

- Basic
- Liquibase
- Logger
- Liquibase & Logger

The respective parameters passed need to be in lowercase (`basic`, `liquibase`, `logger`, `llogger`).

If you opt for the `llogger` templates, you will need to install the [PL/SQL logging utility](https://github.com/OraOpenSource/Logger).  

Here we are configuring for Liquibase:

```bash
quick_config -t liquibase
```

If you are working from a source checkout or extracted legacy install, the equivalent wrapper scripts remain available under `bin/`.

Assuming we were to configure for "Liquibase with Logger", the output should look similar to this:

```
$ quick_config -t llogger
OraTAPI quick config started...
[basic] Copied: /path/to/site-packages/oratapi/ora_tapi_package_data/resources/config/OraTAPI.ini.sample -> configs/basic/resources/config/OraTAPI.ini
[logger] Copied: /path/to/site-packages/oratapi/ora_tapi_package_data/resources/templates/packages/procedures/samples/select.logger.sample -> configs/logger/resources/templates/packages/procedures/select.tpt
[llogger] Copied: /path/to/site-packages/oratapi/ora_tapi_package_data/resources/templates/misc/view/samples/view.llogger.sample -> configs/llogger/resources/templates/misc/view/view.tpt
...
Active profile set to: llogger
OraTAPI quick config complete.
```
The destination paths shown above are relative to `~/OraTAPIProfiles`. `quick_config` instantiates all four built-in profiles (`basic`, `liquibase`, `logger`, `llogger`) and then activates the one selected with `-t`.

If OraTAPI reports that the runtime files have not yet been initialised, use one of these commands:

```bash
quick_config -t <template-category>
```

```powershell
quick_config -t <template-category>
```

Valid template categories are:

1. `basic`     - No Liquibase directives or logging
2. `liquibase` - Generated code includes Liquibase directives
3. `logger`    - Generated PL/SQL includes logger logging calls for parameter values and related diagnostics
4. `llogger`   - Includes both Liquibase directives and logger logging

For options `logger` and `llogger`, the logger utility must already be deployed to the database.

To underscore the point, you should either specify `-t basic`, `-t liquibase`,  `-t logger` or `-t llogger`. Optionally, specify `--template_category` 
instead of `-t`.  

If you run the command more than once, it will have no effect. This is to prevent you from overwriting any later 
customisations to the configuration. However, you can force an overwriting, by adding the `-f/--force` flag. Example:

   ```bash
   quick_config -t liquibase --force
   ```

The full command synopsis is:
```
usage: quick_config.py [-h] -t {liquibase,basic,logger,llogger} [-T] [-f]

Initialise OraTAPI profiles under ~/OraTAPIProfiles/configs and activate the selected built-in profile.

options:
  -h, --help            show this help message and exit
  -t {liquibase,basic,logger,llogger}, --template_category {liquibase,basic,logger,llogger}
                        Built-in profile to activate after bootstrapping all provided profiles.
  -T, --templates_only  Only instantiate templates (Do not overwrite control files).
  -f, --force           Overwrite existing files.

This also instantiates the control files OraTAPI.ini and pi_columns.csv for the built-in profiles basic,
liquibase, logger, and llogger, then points ~/OraTAPIProfiles/active_config at the selected profile.
```
Note that the `-T/--templates_only` can be used in conjunction with the `-f/--force option`, to re-instantiate the templates.
This may be useful if you have started configuring your control files, but wish to switch to a different template set 
to that originally chosen, assuming that you don't wish to reset your bespoke config.

Finally, ensure that you have access to an Oracle database and configure your `TNS` entries or connection settings. 
You should test your connection to the database, via SQLcl or SQL Developer, before attempting with OraTAPI.

### Using Oracle Instant Client

If you need to work with the database via encrypted Oracle Net connections, for example when the target environment
requires Oracle Native Network Encryption, checksumming, LDAP-based resolution, or a wallet-backed connection that
depends on thick mode, then you will need to install Oracle Instant Client.

Download the Basic Oracle Instant Client package for your operating system from:

`https://www.oracle.com/database/technologies/instant-client/downloads.html`

After downloading, extract the archive to a directory on your machine. OraTAPI can then use that Instant Client in
any of the following ways, listed in precedence order:

1. For the current run only, pass:

   ```bash
   ora_tapi --oracle-client-dir /path/to/instantclient_23_8 -c dev_db
   ```

   Windows PowerShell:

   ```powershell
   ora_tapi --oracle-client-dir C:\Oracle\instantclient_23_8 -c dev_db
   ```

2. For all profiles on the current machine, set the `ORACLE_IC_HOME` environment variable to the Instant Client
   directory.

3. For one OraTAPI profile only, place the Instant Client under:

   ```text
   ~/OraTAPIProfiles/configs/<active-profile>/oracle_client
   ```

4. For an installation using a virtual environment, place the Instant Client in an `oracle_client` folder
   in the parent directory of that environment. For example, with the wheel installed in `/opt/oratapi/.venv`:

   ```text
   /opt/oratapi/
   ├── .venv/
   └── oracle_client/
   ```

   Put the extracted client libraries directly inside `oracle_client`, rather than inside a further
   `instantclient_*` subdirectory. OraTAPI uses the running Python environment to locate this folder;
   activation and the current working directory do not affect the lookup. This fallback is skipped
   when running outside a virtual environment.

   The same layout applies on Windows: a virtual environment at `C:\OraTAPI\.venv` uses
   `C:\OraTAPI\oracle_client`, with `oci.dll` directly inside that client folder. The virtual environment
   can have any name; `.venv` is only an example.

The command-line directory takes precedence over `ORACLE_IC_HOME`. If that chosen location is missing or
does not contain the expected client library, OraTAPI checks the profile directory, then the virtual
environment's parent directory. If no valid client is found, it falls back to thin mode.


NOTES:   
If you are on Windows and have Git Bash installed, the Linux/macOS instructions should also work in a Git Bash terminal. 
OraTAPI can be used via PowerShell or Git Bash. 

## Performing Upgrades
### Migrations
Wheel-first upgrades should normally be handled by first activating the virtual environment where OraTAPI is installed,
then installing a newer package version, for example:

```bash
source .venv/bin/activate
pip install --upgrade oratapi
```

Windows PowerShell:

```powershell
.\.venv\Scripts\Activate.ps1
pip install --upgrade oratapi
```

If you are migrating from an older extracted install tree, use `profile_mgr` to migrate the old runtime content into a named profile:

```bash
profile_mgr --migrate-old <path_to_old_install_dir> migrated_profile
```

This will result in your old OraTAPI.ini file, CSV files, and templates being copied into `~/OraTAPIProfiles/configs/migrated_profile`. After migration, `profile_mgr` prompts whether to activate the migrated profile.

If new config settings are introduced, then you will get feedback from the migration tool. It will list any new 
OraTAPI.ini sections that you have missing as well as any properties. In addition, it will inform you if there are 
any obsolete entries. You can view the current shipped settings in context by looking at the packaged `OraTAPI.ini.sample` file.  

Any previously configured named database connections (see [Connection Manager](#connection-manager)) are preserved since they are located under the directory 
$HOME/.OraTAPI.  

The synopsis for the `profile_mgr` command is:

```
usage: profile_mgr.py [-h]
                      (-l | -s | -c PROFILE | -C SOURCE TARGET | -d PROFILE | -a PROFILE | -P PROFILE PURPOSE | -e PROFILE ZIP_PATH | -i ZIP_PATH | -m OLD_INSTALL_DIR TARGET_PROFILE)
                      [-p PURPOSE_TEXT]

Manage OraTAPI configuration profiles stored under ~/OraTAPIProfiles/configs.

options:
  -h, --help            show this help message and exit
  -l, --list            List available profiles.
  -s, --show-active     Show the active profile.
  -c PROFILE, --create PROFILE
                        Create a new profile by cloning the active profile.
  -C SOURCE TARGET, --copy SOURCE TARGET
                        Copy an existing profile.
  -d PROFILE, --delete PROFILE
                        Delete a profile.
  -a PROFILE, --activate PROFILE
                        Activate a profile.
  -P PROFILE PURPOSE, --set-purpose PROFILE PURPOSE
                        Set or replace the one-line purpose text for a profile.
  -e PROFILE ZIP_PATH, --export PROFILE ZIP_PATH
                        Export a profile to a ZIP file, or use a directory to
                        generate a versioned filename.
  -i ZIP_PATH, --import-profile ZIP_PATH
                        Import a profile from a ZIP file.
  -m OLD_INSTALL_DIR TARGET_PROFILE, --migrate-old OLD_INSTALL_DIR TARGET_PROFILE
                        Migrate a legacy install tree into a named profile.
  -p PURPOSE_TEXT, --purpose PURPOSE_TEXT
                        Purpose text to store with a newly created, copied,
                        imported, or migrated profile.
```
You can use `profile_mgr` to back up, restore, or transport named profiles. Export and import work on one profile per ZIP archive. If the export path is an existing directory, `profile_mgr` generates the filename as `<profile>.<created-version>.zip`, using the current OraTAPI version when the profile has no recorded creation version. An explicit file path is used unchanged. If the imported profile already exists, OraTAPI prompts before overwrite and then prompts again to decide whether to activate the imported profile. Profile exports intentionally exclude any profile-local `oracle_client` directory, so embedded Oracle Instant Client files are not bundled into the ZIP archive.

The `-p/--purpose` option can be used with `--create`, `--copy`, `--import-profile`, and `--migrate-old` to set a one-line profile description as part of the operation. Use `-P/--set-purpose` to add or replace the purpose text for an existing profile. Profile listings and `--show-active` also display the recorded creation version from `created_version.md`. If either metadata file is absent, the value is shown as `Unknown`.

Example list output:
```
$ profile_mgr --list
OraTAPI profiles:
  basic (created with 2.0.1; purpose: Built-in profile for standard OraTAPI generation without Liquibase directives or logger calls.)
* logger (created with Unknown; purpose: Unknown)
```

Example export:
```
$ profile_mgr --export basic /tmp
Exported profile 'basic' to /tmp/basic.2.0.1.zip
```

Example import:
```
$ profile_mgr --import-profile /tmp/logger-profile.zip --purpose "Client A reporting profile"
Imported profile 'logger' from /tmp/logger-profile.zip
Activate profile 'logger'? [y/N]:
```

---
### In-situ upgrades
The `update_ora_tapi` command belongs to the legacy extracted-install model, where OraTAPI updates a writable installation tree in place. It is a compatibility mechanism for older extracted installs, not the primary upgrade path for current releases.

For wheel and PyPI installs, first activate the virtual environment where OraTAPI is installed, then upgrade:

```bash
source .venv/bin/activate
pip install --upgrade oratapi
```

Windows PowerShell:

```powershell
.\.venv\Scripts\Activate.ps1
pip install --upgrade oratapi
```

If an in-place legacy upgrade fails or leaves the install tree in an inconsistent state, the recommended recovery is to remove the extracted installation directory and install the new release again from scratch. This does not remove your OraTAPI runtime home under `~/OraTAPIProfiles`, so existing profiles, runtime configuration, instantiated templates, and profile-local Oracle Instant Client content remain available after reinstall.

Use `update_ora_tapi` only if you are deliberately staying on the extracted-install model. In that legacy mode you can either point the command at a release tarball, or let it download a release from GitHub for you.

Example:
```
$ update_ora_tapi -t ../oratapi-1.4.25.tar.gz
update_ora_tapi.py: OraTAPI upgrade utility version: 1.4.24
OraTAPI upgrade started...
Current OraTAPI version: 1.4.24
Tarball OraTAPI version: 1.4.25
A newer version of OraTAPI is available. Do you want to proceed with the upgrade? [y/n]: 
```
Here `-t` specifies the pathname to a tarball. Entering `y` allows the legacy upgrade to proceed.

If you still use the GitHub-based legacy flow, `check_github_for_updates` can tell `ora_tapi` to warn that a newer GitHub release exists. That setting is deprecated and applies only to extracted installs. Current wheel and PyPI installs should instead use `check_pypi_for_updates` and then activate the target virtual environment before running `pip install --upgrade oratapi`.

For legacy extracted installs, you must still run the `setup` command mentioned at the end of `update_ora_tapi` output.

Command Synopsis:

```
update_ora_tapi -h
update_ora_tapi.py: OraTAPI upgrade utility version: 1.4.22
OraTAPI upgrade started...
usage: update_ora_tapi.py [-h] (-t TARBALL | -s STAGING_DIR)

Upgrade OraTAPI by unpacking a tarball or downloading the latest version from GitHub.

options:
  -h, --help            show this help message and exit
  -t TARBALL, --tarball TARBALL
                        Specify the path to the tarball file.
  -s STAGING_DIR, --staging-dir STAGING_DIR
                        Specify a staging directory to download the latest version from GitHub.
```
#### The check_pypi_for_updates Property
Use this property to instruct the `ora_tapi` command to check PyPI for newer published versions of OraTAPI.

```
# Set check_pypi_for_updates to true to enable checks for newer versions of OraTAPI on PyPI, otherwise set to false.
check_pypi_for_updates = true
```
When a newer version is found, OraTAPI prints a reminder that you can activate the target virtual environment and upgrade with:

```bash
source .venv/bin/activate
pip install --upgrade oratapi
```

Windows PowerShell:

```powershell
.\.venv\Scripts\Activate.ps1
pip install --upgrade oratapi
```

#### The check_github_for_updates Property (Deprecated)
This property is deprecated. It is retained only for legacy extracted-install workflows that still follow GitHub release
updates rather than PyPI publication.

```
# DEPRECATED: Prefer check_pypi_for_updates for current releases.
check_github_for_updates = true
```

Both properties live in the `behaviour` section.


## The Primary Components
The OraTAPI tools consist of three major parts:

- The ora_tapi command line tool.
- Code templates.
- The OraTAPI.ini configuration file

The `ora_tapi` command line tool is used to launch the code generation process.

The code templates form the basic shape of the generated source code files. There are various templates that are read 
at runtime and constitute regions such as package file headers, footers, and procedures. You can also implement view and 
trigger templates, and sample templates are provided for you to copy and modify. You should not amend the original sample 
files. These have a suffix of `.tpt.sample`. Instead, work on the instantiated `.tpt` files under `~/OraTAPIProfiles/configs/<active-profile>/resources/templates`. See [Modifying Templates](#modifying-templates) for the recommended workflow. There are also `column expression` templates. These are discussed in
the [Maintained by Column Expression](#maintained-by-column-expression) section.

Finally, much of the behaviour of OraTAPI is governed by the configuration of the `OraTAPI.ini` file, which is located 
in the `config` directory. The OraTAPI.ini file consists of property/value pairs, which are located into various 
sections, which are used to categorise their purpose. Section names are enclosed in square brackets 
(<i>e.g. [<api_controls]</i>). Specifically, for the OraTAPI framework, each property name in the file must be globally unique, 
irrespective of which section it belongs to.

## Modifying Templates
For application labels and other text that varies between invocations, see
[Template overrides for one run](#template-overrides-for-one-run). Custom definitions use the same `%name%` placeholders.

OraTAPI is designed for profile-local template customisation. The packaged defaults are copied into the runtime home by
`quick_config`, and the files you should edit are the instantiated `.tpt` templates under:

```text
~/OraTAPIProfiles/configs/<active-profile>/resources/templates
```

Do not edit the packaged defaults in the installation directory, and do not edit the original `.tpt.sample` files.
Those files act as source material for profile bootstrapping, not as the normal day-to-day customisation point.

If you want to preserve an existing working profile before changing templates, consider cloning it first:

```bash
profile_mgr -C <source-profile> <new-profile>
profile_mgr -a <new-profile>
```

This is especially useful when you want to support different audit-column strategies in different environments. For
example, one profile might use a trigger template that maintains `created_by`, `created_on`, `updated_by`,
`updated_on`, and `row_version`, while another profile might assume `created_by` and `created_on` are handled by
`default on null` clauses in the table DDL and leave the trigger to maintain only `updated_by`, `updated_on`, and
optionally `row_version`.

The same approach works well when you need different Liquibase, logger, trigger, view, or package-shape conventions
for different projects. Keep those variations in separate profiles rather than repeatedly editing a single active
profile back and forth.

For trigger generation specifically, OraTAPI processes every `.tpt` file found in the active profile's trigger
template directory. That means if you place more than one trigger template in
`~/OraTAPIProfiles/configs/<active-profile>/resources/templates/misc/trigger`, OraTAPI will generate one trigger script per
matching template for each selected table. The output file name is derived from the template file name, with
`table_name` replaced by the actual table name.

### Command-line Tools
In the preferred wheel/PyPI model, the command-line tools are installed as console scripts into the Python environment. There are two tools that you will need to work with most often, `conn_mgr` and `ora_tapi`. The latter of these will be used more frequently.

In respect of the `conn_mgr` tool (see [Connection Manager](#connection-manager)), this is used to securely store 
database connections (authentication settings, credentials where applicable, and DSNs). Such connections are named and can be used in conjunction with the
`-c/--conn_name` option of the `ora_tapi` command. This is a secure alternative to specifying the `-u/--db_username`, 
`-p/--db_password` and `-d/--dsn` options.

The primary launch commands are:

- `ora_tapi` / `ora-tapi` / `oratapi`
- `oratapi-orc` / `oratapi_orc`
- `conn_mgr` / `conn-mgr`
- `quick_config` / `quick-config`
- `profile_mgr` / `profile-mgr`
- `update_ora_tapi` / `update-ora-tapi`

If you are running from a source checkout or extracted legacy install, equivalent wrapper scripts remain available under `bin/`.

Use `oratapi -v` or `oratapi --version` to display the installed version and exit.
This also works without an active profile or database connection.

To get command line help, you can simply type:

```
ora_tapi -h

usage: ora_tapi.py [-h] [-v] [-A APP_NAME] [-a TAPI_AUTHOR] [-D NAME=VALUE]
                   [--profile PROFILE] [--outputs {tapi,utplsql,view,trigger} ...]
                   [-c CONN_NAME] [-d DSN]
                   [--oracle-client-dir ORACLE_CLIENT_DIR] [-g STAGING_DIR]
                   [-G UT_STAGING_DIR] [-u DB_USERNAME] [-p DB_PASSWORD]
                   [-To TABLE_OWNER] [-po PACKAGE_OWNER] [-to TRIGGER_OWNER]
                   [-vo VIEW_OWNER] [-t TABLE_NAMES [TABLE_NAMES ...]]
                   [-T API_TYPES [API_TYPES ...]]
                   [-U UT_API_TYPES [UT_API_TYPES ...]]

Oracle Table API Generator

options:
  -h, --help            show this help message and exit
  -v, --version         Display the version and exit.
  --profile PROFILE    Use a profile for this run without changing active_config.
  --outputs {tapi,utplsql,view,trigger} [{tapi,utplsql,view,trigger} ...]
                        Restrict generation to these profile-enabled categories.
  -A APP_NAME, --app_name APP_NAME
                        Application name - included to the package header.
                        Default: Human Resources
  -a TAPI_AUTHOR, --tapi_author TAPI_AUTHOR
                        TAPI author
  -D NAME=VALUE, --define NAME=VALUE
                        Override metadata or custom template text for this
                        run; repeat for multiple definitions.
  -c CONN_NAME, --conn_name CONN_NAME
                        Database connection name (created via OraTAPI
                        connection manager).
  -d DSN, --dsn DSN     Database data source name (TNS name).
  --oracle-client-dir ORACLE_CLIENT_DIR
                        Path to an Oracle Instant Client directory to use for
                        this run.
  -g STAGING_DIR, --staging_dir STAGING_DIR
                        Directory for staging area. Default:
                        /home/clive/OraTAPI/<configured-staging-dir>
  -G UT_STAGING_DIR, --ut_staging_dir UT_STAGING_DIR
                        Directory for unit tests staging area. Default:
                        /home/clive/OraTAPI/<configured-ut-staging-dir>
  -u DB_USERNAME, --db_username DB_USERNAME
                        Database connection username.
  -p DB_PASSWORD, --db_password DB_PASSWORD
                        Database connection password.
  -To TABLE_OWNER, --table_owner TABLE_OWNER
                        Database schema name of the tables from which to
                        generate the code. Default: aut
  -po PACKAGE_OWNER, --package_owner PACKAGE_OWNER
                        Database schema in which to place the TAPI packages.
                        Default: aut
  -to TRIGGER_OWNER, --trigger_owner TRIGGER_OWNER
                        The schema in which to place the generated triggers.
                        Default: aut
  -vo VIEW_OWNER, --view_owner VIEW_OWNER
                        The schema in which to place the generated views.
                        Default: aut
  -t TABLE_NAMES [TABLE_NAMES ...], --table_names TABLE_NAMES [TABLE_NAMES ...]
                        A space separated list of table names. Default: all
  -T API_TYPES [API_TYPES ...], --api_types API_TYPES [API_TYPES ...]
                        Space-separated list of API types. Valid options:
                        insert, select, update, upsert, delete or merge.
  -U UT_API_TYPES [UT_API_TYPES ...], --ut_api_types UT_API_TYPES [UT_API_TYPES ...]
                        Space-separated list of unit test API types. Valid
                        options: insert, select, update, upsert, delete or
                        merge.

The majority of defaults can be changed via the OraTAPI.ini file.
```
The DB_USERNAME database user, must have sufficient privileges to view the TABLE_OWNER's database objects via the 
Oracle `ALL_` data dictionary views.

## Usage

Run OraTAPI from the command line with the desired options.

### Repository-managed profiles

Use `--repo PATH` to keep an application's profiles and orchestrator bundles beside its
source code. The path must be an existing directory; OraTAPI does not search for a Git
repository or infer one from the current working directory. Use `--repo .` explicitly.

```text
<application-repo>/OraTAPIProfiles/
├── configs/
│   ├── agr/
│   ├── agr_ut/
│   └── agr_tapi_tests/
└── orc_bundles/
    ├── README.md
    ├── example.toml.sample
    └── agr.toml
```

The override is available on `ora_tapi`, `oratapi-orc`, `profile_mgr` and `quick_config`,
including their installed command aliases. Without it, profiles still come from
`~/OraTAPIProfiles/configs`. With it, all named profiles and bundles come from the selected
repository; missing entries do not fall back to local copies.

```sh
quick_config --repo . --template_category basic
profile_mgr --repo . --list
profile_mgr --repo . --activate agr
ora_tapi --repo . --profile agr -c MY_CONNECTION -To cfg_core -t cfg_json_mappings
oratapi-orc --repo . --bundle agr -c MY_CONNECTION -To cfg_core -t cfg_json_mappings --dry-run
```

The first normal command seeds a missing `OraTAPIProfiles` directory from **all** profiles
under `~/OraTAPIProfiles/configs`, including custom profile resources and metadata. It copies local
bundle assets from `~/OraTAPIProfiles/orc_bundles`, using the older `~/OraTAPIProfiles/bundles` for names
not present in the new directory. Duplicate names are reported and the new directory wins.
It adds the packaged README and starter sample if missing. Profile-local Oracle clients,
migration backups, temporary files and Python caches are excluded; credentials and the
runtime home are not copied. Symbolic links in seed assets are rejected rather than copied
from outside the profile. Seeding does not rewrite configured absolute paths.

Seeding prepares a temporary sibling directory and publishes it without replacing any
concurrently created destination. Failures leave no partial seeded directory. If no local
profiles exist, start with `quick_config --repo . --template_category basic`; other commands
report the missing source profiles. Once `OraTAPIProfiles` exists, it is authoritative:
OraTAPI never automatically merges later changes from the local copies. Commit and review
its contents with the application code, then pull updates through your usual Git workflow.
OraTAPI itself does not commit files.

**Active-profile selection is personal, even with repository-managed profiles.** Selection
is stored under `~/OraTAPIProfiles/repo_state`, keyed by the repository's resolved absolute path.
Initial seeding inherits the local active profile if it was copied. A newly cloned, existing
configuration has no personal selection: use `--profile` for one run or
`profile_mgr --repo . --activate NAME`. Activating a repository profile does not change the
ordinary local selection or any tracked repository file. The orchestrator explicitly
selects each component's profile and passes `--repo` to every generation subprocess.

Import, export, copy, delete and configuration migration still operate on individual
profiles, now within the selected root:

```sh
profile_mgr --repo . --export agr ./agr.zip
profile_mgr --repo . --import-profile ./agr.zip
profile_mgr --repo . --split-utplsql agr --write --remove-legacy
```

Relative profile resource paths resolve beneath that profile. Credentials and saved
connections stay in their existing developer-local stores; use your local connection name
with `--conn_name`. Oracle client lookup retains the explicit command/environment choices
and the matching **local** profile's `oracle_client` directory under `~/OraTAPIProfiles/configs`, followed by
the `oracle_client` folder beside the running virtual environment.
Default staging and reports remain under `~/OraTAPIProfiles`; an explicit staging path retains its
existing behaviour. The repository override only redirects configuration.

Help, version and orchestrator dry runs create no repository files or personal selection
state. If repository configuration is absent, `--dry-run` describes the seed and validates
using the local profiles and bundles that would be copied. A missing profile or bundle is
still an error. It does not execute the sample or connect to Oracle.

### Profile bundles with oratapi-orc

Each profile can now keep shared/production settings in `resources/config/OraTAPI.ini`
and test settings in a sibling `utplsql.ini`. The test file owns `[ut_controls]` and
`[file_controls] default_ut_staging_dir`; shared copyright, project identity, formatting
and file extensions remain in `OraTAPI.ini`. Interpolation can reference those shared settings.
Relative test-contract paths still resolve against the profile root.

Existing combined profiles remain supported. When `utplsql.ini` exists, its test settings
take precedence as a whole and legacy test entries are ignored with a warning. New built-in
profiles use the split layout. Bootstrap does not add a shadowing test file to an existing profile.

Preview migration for a combined profile or an existing split profile, then write it:

```sh
profile_mgr --split-utplsql agr_atp
profile_mgr --split-utplsql agr_atp --write
```

To also remove the migrated test settings from `OraTAPI.ini`:

```sh
profile_mgr --split-utplsql agr_atp --write --remove-legacy
```

Despite its historical name, `--split-utplsql` now upgrades an existing `utplsql.ini`
as well as creating one from a combined configuration. It applies these conversions:

| Previous setting | Current setting |
|---|---|
| `[ut_controls] tested_tapi_owner` | `[ut_controls] tapi_owner` |
| `[ut_controls] ut_suite` | `[suite] suite` |
| `[suite] name_template` | `[suite] suite_template` |
| `[suite] path_template` | `[suite] suitepath_template` |

The separate `[package-location:NAME] path_template` is unchanged. Existing mapped mode,
package mappings and custom values are retained; an absent mode defaults to `legacy`.
If both an old and a new setting exist, the new setting wins, including a blank value.
An existing split file retains its normal precedence over legacy test settings in
`OraTAPI.ini`; migration does not fill its missing test controls from stale legacy entries.

Preview writes no profile files. Writing creates a uniquely named `.backup-*` copy of
each existing file that changes. Without `--remove-legacy`, `OraTAPI.ini` remains untouched.
With it, test controls, test staging and suite/mapping sections move out of that file.
A repeat run on an up-to-date profile reports that it is already current and creates no
additional backups. Comments and interpolation references are retained where possible;
references to renamed properties are updated to their current names. The migration checks
effective values before writing and stops if a custom reference would change meaning.
Unsupported non-empty `fixture_catalogue` settings require manual conversion to inline
fixture source. If a write fails, the migration restores any configuration files it has
already replaced.

`profile_mgr --migrate-old OLD_INSTALL_DIR TARGET_PROFILE` also applies these conversions
to the copied configuration, including an existing `utplsql.ini`. It leaves the original
installation untouched. Importing a profile archive still preserves the archived files;
run `--split-utplsql` afterwards to update an imported profile.

Legacy combined `.sample` files are retained for compatibility; new test defaults are in
`utplsql.ini.sample`.

#### Suite settings and substitution formats

A utPLSQL suite is a named collection of tests. OraTAPI writes its name as
`--%suite(...)` and its position in the test hierarchy as `--%suitepath(...)` in the
**test package specification**. The test implementations go in the package body.

The `[suite]` section in `utplsql.ini` chooses how those annotations are supplied:

| Setting | Used when | Meaning |
|---|---|---|
| `mode = legacy` | Legacy mode | Use the literal `suite` value and the profile's existing annotation templates. |
| `suite = Agriculture` | Legacy mode only | Supply `Agriculture` as the literal suite text. |
| `mode = mapped` | Mapped mode | Resolve the production package through explicit mapping sections. |
| `suite_template = {package_name}` | Mapped mode only | Build the text inside `--%suite(...)`. |
| `suitepath_template = {product}.{group}.{category}.{package_name}` | Mapped mode only | Build the hierarchy inside `--%suitepath(...)`. |

There are three different substitution formats. They are processed at different stages:

| Format | Who replaces it and when? | Example |
|---|---|---|
| `${section:property}` | Python's INI reader resolves a reference to another configuration value when it is read. The loaded `utplsql.ini` can reference shared settings in `OraTAPI.ini`. | `${file_controls:default_staging_dir}` uses the configured staging directory. |
| `{placeholder}` | OraTAPI's suite resolver fills fields when resolving a mapped package. | `{product}.{group}` becomes `agriculture.shared` in the example below. |
| `%placeholder%` | OraTAPI inserts resolved values into its SQL template files when generating code. | `--%suite(%ut_suite%)` becomes `--%suite(cfg_json_mappings_tapi)`. |

The `--%suite(...)` and `--%suitepath(...)` forms themselves are utPLSQL annotations;
they remain in the generated SQL. A custom scaffold package header used in mapped mode
must contain these substitutions:

```sql
%STAB%--%suite(%ut_suite%)
%STAB%--%suitepath(%ut_suitepath%)
```

`%STAB%` supplies indentation. Replace a legacy header that constructs `%suitepath`
directly from `%ut_prod_code%` and `%ut_prod_sub_domain_code%` before using mapped mode.
OraTAPI rejects annotations that disagree with the resolved mapping.

The former `[ut_controls] ut_suite` location remains supported; an explicit `[suite] suite`
takes precedence, including an intentionally blank value. Existing `%ut_suite%` SQL
substitutions and `-D ut_suite` overrides retain their names. The former `[suite]` keys
`name_template` and `path_template` remain accepted with a warning; their replacements
`suite_template` and `suitepath_template` take precedence. The separate
`[package-location:NAME] path_template` retains its name and has a different purpose.

#### Worked example: cfg_core.cfg_json_mappings

Start with the source table `cfg_core.cfg_json_mappings`. Following the schema naming
convention in this example, its production TAPI belongs in `cfg_api`. Supply that owner
with `--package_owner cfg_api` when generating the production package:

```sh
ora_tapi --profile agr \
  --conn_name dev_db \
  --table_owner cfg_core \
  --table_names cfg_json_mappings \
  --package_owner cfg_api \
  --outputs tapi
```

Replace `agr` with your production profile and `dev_db` with your saved connection name.
The production profile must enable TAPI output: for example, leave
`[ut_controls] enable_ut_code_generation = false` for this production run.

`--package_owner` (`-po`) overrides `[schemas] default_package_owner` in the selected
profile's `OraTAPI.ini`. If the argument is omitted, that configured default supplies
the owner. OraTAPI does not automatically replace `_core` with `_api`: the relationship
between `cfg_core` and `cfg_api` is your naming convention, expressed through the command
argument or configuration. If neither supplies an owner, provide one explicitly;
OraTAPI cannot derive it from the table owner.

Owner selection and package naming are separate. With these settings in the production
profile's `OraTAPI.ini`:

```ini
[api_controls]
tapi_pkg_name_prefix =
tapi_pkg_name_postfix = _tapi
```

OraTAPI combines the empty prefix, table name and postfix to obtain
`cfg_json_mappings_tapi`. Together with `--package_owner cfg_api`, that identifies the
production package as `cfg_api.cfg_json_mappings_tapi`.

Next configure a separate test profile, for example by copying the production profile:

```sh
profile_mgr --copy agr agr_tapi_tests
```

Use an existing suitable test profile instead if you already have one. The copy command
requires a new target name. Retain the production `tapi_pkg_name_prefix` and
`tapi_pkg_name_postfix` settings in the test profile: mapped scaffold generation uses
them to reconstruct the production package name for `{package_name}`. The generated
test package has its own name, controlled by `ut_pkg_name_prefix` and `ut_pkg_name_postfix`.

For this separate test run, the current implementation reads the production package
owner from `[ut_controls] tapi_owner`. Set it to `cfg_api`, matching the owner supplied
to the production command above. OraTAPI does not remember the previous command or copy
its owner argument into this setting. The test command's own `--package_owner` will
instead specify where the generated test package belongs, here `cfg_test`.

Merge the following settings into the test profile's `utplsql.ini`, retaining its other
settings and avoiding duplicate section headers:

```ini
[ut_controls]
enable_ut_code_generation = true
test_generation_mode = scaffold
# Match --package_owner cfg_api from the production TAPI generation command.
tapi_owner = cfg_api

[suite]
mode = mapped
# Literal suite text is only used when mode = legacy.
suite =
suite_template = {package_name}
suitepath_template = {product}.{group}.{category}.{package_name}

[package-location:production_packages]
production_owner = cfg_api
allowed_prefixes = cfg
path_template = product/dggiu_agg/db/cfg_api/package_spec/{package_name}.sql

[suitepath-map:common_area]
source_root = product/dggiu_agg/db/cfg_api
product = agriculture

[suitepath-group:configuration_packages]
area = common_area
allowed_prefixes = cfg
group = shared

[package-categories]
_tapi = tapi
```

The names after the colons are identifiers you choose, not words with built-in meaning.
Here `area = common_area` explicitly refers to `[suitepath-map:common_area]`.
The labels `production_packages` and `configuration_packages` identify their own entries;
they need not match `common_area`. None of these section labels supplies an annotation value.
The actual `group = shared` setting supplies the word `shared` in the suite path.

With those settings and the mapped SQL header above, run:

```sh
ora_tapi --profile agr_tapi_tests \
  --conn_name dev_db \
  --table_owner cfg_core \
  --table_names cfg_json_mappings \
  --package_owner cfg_test \
  --outputs utplsql
```

Replace `agr_tapi_tests` with the test profile you configured and `dev_db` with a saved
connection that can inspect the source table. The `--outputs` option restricts generation;
it does not enable tests if `enable_ut_code_generation` is false. Both generation commands
write files, rather than installing packages in Oracle.

The owners in these two runs come from:

| Role | Value in this example | Source |
|---|---|---|
| Source table owner | `cfg_core` | `--table_owner` in each command; otherwise `[schemas] default_table_owner`. |
| Owner of the generated production TAPI | `cfg_api` | `--package_owner` in the production command; otherwise `[schemas] default_package_owner` in the production profile. |
| Production owner used for mapped scaffold resolution | `cfg_api` | `[ut_controls] tapi_owner` in the test profile, explicitly set to match the production owner. |
| Owner of the generated test package | `cfg_test` | `--package_owner` in the test command; otherwise `[schemas] default_package_owner` in the test profile. |

`[package-location:production_packages] production_owner = cfg_api` is a match condition
for the suite resolver. It does not choose the schema in which a package is generated.

OraTAPI resolves the annotation values in this order:

1. The production package is `cfg_json_mappings_tapi`, and `[ut_controls] tapi_owner`
   supplies `cfg_api`. These supply `{package_name}` and `{production_owner}` respectively.
2. Owner `cfg_api` and prefix `cfg_` select `[package-location:production_packages]`.
   Substituting the package name constructs the string
   `product/dggiu_agg/db/cfg_api/package_spec/cfg_json_mappings_tapi.sql`.
3. That path sits beneath `[suitepath-map:common_area] source_root`, so this map matches.
   Its `product = agriculture` supplies `{product}`.
4. `[suitepath-group:configuration_packages]` refers to that map through `area = common_area`
   and accepts the package prefix `cfg_`. Its `group = shared` supplies `{group}`.
5. The package suffix `_tapi` matches `[package-categories]`, supplying `{category} = tapi`.
6. The two suite templates are expanded to produce:

```sql
--%suite(cfg_json_mappings_tapi)
--%suitepath(agriculture.shared.tapi.cfg_json_mappings_tapi)
```

The resolver lowercases the production owner, package name, product, group and category;
it also lowercases the final suite path. Literal text in `suite_template` retains its case.

**The intermediate pathname is only used to select a source-root mapping.** OraTAPI
neither opens nor creates that file. The suffix `package_spec/{package_name}.sql` places
the constructed path beneath the configured root; `package_spec` and `.sql` have no
special meaning to the resolver. In this example, that suffix contributes no additional
classification. A production package body needs no second location entry: the mapping
classifies the package as a whole, using its declared specification location.

This mapping does not select the output directories. Generated test specifications go to
`default_ut_staging_dir/spec_dir`, and bodies to `default_ut_staging_dir/body_dir`, using
the configured test package prefix/postfix and file extensions. `default_ut_staging_dir`
lives in `utplsql.ini`; the subdirectory names and extensions remain in `OraTAPI.ini`.
The `--ut_staging_dir` command option can override the test staging directory.

Every mapped package must match exactly one location, source area, group and category.
Missing or ambiguous mappings cause an error; the resolver does not guess. Source roots
must not overlap. In scaffold mode, leaving `tapi_owner` blank prevents a match against
a named production owner. The required mapping values are configuration declarations,
not values discovered from the database or filesystem.

#### Executable test configuration

For executable tests, use `[ut_controls] test_generation_mode = executable` and add
`tested_component = "tapi"` to the test component below. Leave its `tested_tapi_contract`
empty: the orchestrator writes the named earlier component's contract to a temporary
directory and passes it using `--tested-tapi-contract`. The directory is removed when
the run finishes, including on failure or interruption. These intermediate files are
not retained in the generated output. It never assumes a utility component is the production TAPI.
Standalone combined generation handles the same information in memory, as shown under
[One command for production code and executable tests](#one-command-for-production-code-and-executable-tests).

Executable presentation uses dedicated profile templates under
`resources/templates/ut_executable/spec` and `resources/templates/ut_executable/body`,
each with `package_header.tpt` and `package_footer.tpt`. Add them from the matching built-in
samples when migrating a customised profile. Copy these four files explicitly; do not use
the force-based template refresh on a customised profile just to add executable wrappers.
Wrappers surround generator-owned PL/SQL and support shared settings plus `%package_name%`
and `%package_owner%`. Scenario logic and annotations remain generator-owned.

Missing fixture values are marked inside each test with the local fields, constraints and
an explicit ORA-20001 failure. Complete these sections directly, then compile and test.
The generated requirements report is an inventory, not a catalogue to maintain. Generation
status is separate from fixture counts and execution, which remains `not_run` until tested.
Regeneration uses fresh staging and does not merge completed source.

`oratapi-orc` (also available as `oratapi_orc`) generates complementary components from a named bundle of profiles.
Profiles run sequentially in separate processes, using the same Python interpreter and source-table selection.
Their configurations are not merged, and the saved `active_config` selection is never changed.

Each component has its own package owner. The connection account, source-table schema, TAPI schema,
data-utility schema and unit-test schema need not be the same. Generated TAPI references explicitly qualify
their target schema. Assigning an owner does not create schemas, synonyms or grants, or verify that the output
will compile.

`quick_config` provisions `orc_bundles/README.md` and `orc_bundles/example.toml.sample`
under the selected configuration root: `~/OraTAPIProfiles` locally, or `PATH/OraTAPIProfiles`
with `--repo PATH`. Existing files are preserved on every run, including `--force`;
that flag applies to profile bootstrap files, not bundle documents.

Copy the starter to a name such as `agr.toml` and customise it. The sample is never run
automatically. A fuller Agriculture example is supplied as
[`resources/orc_bundles/agr.toml.sample`](resources/orc_bundles/agr.toml.sample), also included in wheel package data.
For an installed wheel, this read-only command locates the supplied example:

```bash
python -c "from oratapi.lib.fsutils import resolve_default_path; print(resolve_default_path('resources/orc_bundles/agr.toml.sample'))"
```

Copy the example to `orc_bundles` in the selected root and review it before use.
The orchestrator looks there first. If the named file is absent, it accepts the legacy
`bundles` directory beneath the same root with a migration notice. A malformed preferred
file fails validation rather than falling back. Repository runs never search the local
home for a missing bundle. Installation alone does not provision configuration;
`quick_config` and first-time repository seeding supply the starter documents.

```toml
version = 1
description = "Agriculture TAPIs, utilities and test stubs"

[[components]]
name = "tapi"
profile = "agr"
outputs = ["tapi", "view", "trigger"]
package_owner = "agr_api"
view_owner = "agr_api"
trigger_owner = "agr_core"

[[components]]
name = "data_utility"
profile = "agr_ut"
outputs = ["tapi"]
package_owner = "data_utility"

[[components]]
name = "utplsql"
profile = "agr_tapi_tests"
outputs = ["utplsql"]
package_owner = "unit_test"
```

`outputs = ["tapi"]` means the ordinary package generator, including when a profile configures it to produce
`du_...` utility packages. Use separate components for ordinary and utPLSQL packages. Each component's
`package_owner`, `view_owner` and `trigger_owner` may override that profile's defaults; omitted owners retain
their profile defaults. There is deliberately no global package-owner option on the orchestrator.
Only one component may emit views, and only one may emit triggers.

The former `[ut_controls] tested_tapi_owner` setting is accepted with a warning;
`tapi_owner` takes precedence when both are supplied. Template substitutions and report
fields now use `tapi_owner` too. The name identifies ownership, not test execution status.

For a utPLSQL component, optional `[ut_controls] tapi_owner` profile metadata identifies the production
TAPI owner separately in diagnostics and reports. It is never inferred from the source or test-package schema;
an unset value is reported as unspecified (`null` in JSON). This metadata does not qualify existing SQL references
or redirect calls. A customised template can use `%tapi_owner%`, while unqualified references still depend
on the deployment's synonyms and grants.

Bundle and component names use letters, digits, underscores and hyphens. Output subdirectories must be relative,
distinct paths within their component directory; absolute paths and parent traversal are rejected. Owner and
table arguments use unquoted Oracle identifiers. Component order is the order of the `[[components]]` entries.

Before using the Agriculture example:

1. Prepare `agr_tapi_tests` by cloning the regular TAPI profile, not the data-utility profile. For example:
   `profile_mgr -C agr agr_tapi_tests -p "Agriculture TAPI utPLSQL specifications and stubbed bodies"`.
   Decline activation if you want to keep the current selection.
2. In the new profile, set `[ut_controls] enable_ut_code_generation = true` and
   `[behaviour] enable_tapis_when_ut_enabled = false`. Retain the production TAPI package/procedure naming and
   configure `ut_..._tapi` test-package naming, appropriate suite metadata and `[schemas] default_package_owner = unit_test`.
   Set `[ut_controls] tapi_owner = agr_api` to identify the production TAPI schema, independently of `unit_test`.
3. Review inherited defaults. The profiles examined during development contained Food Security/DQU defaults,
   ATP-labelled application/staging values and placeholder product codes. Bundle schema overrides do not correct
   application labels, suite metadata or other inherited settings.
4. Review the inherited UT specification header: close the explanatory `/* ... */` comment before `--%suitepath`,
   and use `--%displayname`, not `-- %displayname`. Check test API labels against the production TAPI owner,
   not the source-table or unit-test owner, using `%tapi_owner%` where appropriate.
   These existing template defects are not automatically repaired.

Validate the complete bundle without opening a connection or writing files:

```bash
oratapi-orc --bundle agr -c MY_CONNECTION -To agr_core -t TABLE_ONE TABLE_TWO --dry-run
```

Remove `--dry-run` to generate. Use `-t '%'` to select all tables: the first component resolves the table list,
which is then passed explicitly to subsequent components. Each profile's `OraTAPI.csv` still controls exclusions;
those exclusions can differ between components and are reported. Normal generation can create or update the
profile's CSV control file, just as a standalone OraTAPI run does. The INI, templates and active selection are not edited.

The default staging root is `~/OraTAPIProfiles/staging`; override it with `-g/--staging_dir`. Relative staging roots resolve
under `~/OraTAPIProfiles`, not the current working directory. Every run creates a timestamp-and-identifier directory:

```text
<staging-root>/agr/<run-id>/
  tapi/package_spec/       tapi/package_body/       tapi/view/       tapi/trigger/
  data_utility/package_spec/                        data_utility/package_body/
  utplsql/package_spec/                             utplsql/package_body/
  run-summary.json
```

Components use their profile's specification/body directory names. Earlier runs, including any manual edits,
are preserved. `run-summary.json` records profiles, resolved schema owners, generated files, skipped objects,
counts and completion status. Each component also has a `.generation-report.json` containing its generation results.
Reports do not contain credentials or generated SQL text. A failure stops later components, preserves partial
output and returns a non-zero exit status. Review skipped-object reasons even when the run is complete.

Source-checkout wrappers are `bin/oratapi_orc.sh` and `bin/oratapi_orc.ps1`. They prefer a project virtual
environment; set `ORATAPI_PYTHON` to choose another interpreter containing OraTAPI's dependencies.

The same selection/filtering capabilities are available independently:

```bash
oratapi --profile agr -c MY_CONNECTION -To agr_core -t TABLE_ONE --outputs tapi view trigger
oratapi --profile agr_tapi_tests -c MY_CONNECTION -To agr_core -t TABLE_ONE --outputs utplsql -po unit_test
```

`--outputs` restricts generation; it cannot enable categories disabled in the profile. In particular,
`-U/--ut_api_types` selects operations to test but does not enable utPLSQL generation. Omitting `--profile`
or `--outputs` preserves the existing saved-profile and generation-mode behaviour.

This command generates files only. It does not deploy, compile or execute them or complete fixtures with AI.
Executable test components generate calls and assertions with inline fixture instructions where data is unresolved.
XAPI testing remains separate work.

### Template overrides for one run

Use `-D NAME=VALUE` or `--define NAME=VALUE` to override metadata or custom template text for a single invocation.
Repeat the argument for each definition. The `oratapi`, `ora_tapi` and `ora-tapi` commands all support this option.
Definitions leave the active profile's `OraTAPI.ini` and templates unchanged.

For example, with `enable_ut_code_generation = true` already set in the active profile:

```bash
oratapi -c agr_atp \
  -D ut_suite="Agriculture" \
  -D ut_prod_code=agr \
  -D ut_prod_sub_domain_code=applications
```

With the standard utPLSQL package specification template, this produces:

```sql
--%suite(Agriculture)
--%suitepath(agr.applications)
```

The supported built-in metadata names are:

| Definition name | Template placeholder | Derived lower-case placeholder |
|-----------------|----------------------|--------------------------------|
| `app_name` | `%app_name%` | `%app_name_lc%` |
| `tapi_author` | `%tapi_author%` | `%tapi_author_lc%` |
| `company_name` | `%company_name%` | `%company_name_lc%` |
| `copyright_year` | `%copyright_year%` | `%copyright_year_lc%` |
| `ut_suite` | `%ut_suite%` | `%ut_suite_lc%` |
| `ut_prod_code` | `%ut_prod_code%` | `%ut_prod_code_lc%` |
| `ut_prod_sub_domain_code` | `%ut_prod_sub_domain_code%` | `%ut_prod_sub_domain_code_lc%` |

Definitions take precedence over profile substitutions and CLI metadata options such as `-A/--app_name` and
`-a/--tapi_author`, regardless of argument order. If a name is defined more than once, its last definition wins.
Define the base name to update its lower-case placeholder; directly defining a built-in `_lc` name is rejected.
Use `app_name` to override application text, rather than the INI property name `default_app_name`.

Custom names must match a `%name%` placeholder in an instantiated `.tpt` file beneath the active profile's
`resources/templates` directory. Sample directories are excluded. A match in a template that is not used by the
current run is sufficient. For example, add this comment to a profile template:

```sql
-- Release: %release_label%
```

Then supply its value with:

```bash
oratapi -c agr_atp --define 'release_label=September release'
```

Names are case-sensitive and must match `[A-Za-z_][A-Za-z0-9_]*`. Omit the `%` delimiters and any INI section
prefix. Every definition requires `=`. Values are strings: quote spaces, use `-D 'release_label='` for an empty
value, or `-D 'release_label=build=42, channel=preview'` to include additional equals signs and commas.
Only the first `=` separates the name from the value. Definitions do not add INI interpolation or type conversion;
values such as `current` and `auto_table` are replacement text. An explicit `ut_prod_sub_domain_code` replaces the
profile's automatically derived subdomain.

Unknown names are rejected before database connection or output creation. Check the spelling or add the corresponding
placeholder to a profile template. Built-in operational controls, object identifiers and generated SQL fragments
remain protected even if a template refers to them. For example, `-D enable_ut_code_generation=true` and
`-D table_name=OTHER_TABLE` are rejected. Use the supported CLI option or profile setting where applicable.

Overrides apply throughout TAPI and utPLSQL rendering, including package headers, footers, procedures, setup/teardown,
views, triggers and embedded column expressions. The parameter summary lists the definition names. Keep separate
profiles where schemas, package naming, templates or generation behaviour differ; use definitions for metadata and
custom text that varies between runs.

### Examples
The following examples assume that OraTAPI has been installed into an active virtual environment and the console scripts are on `PATH`.

#### Basic Example
```bash
ora_tapi --table_owner HR --table_names employees departments --conn_name dev_db --tapi_author cbostock
```
Using the terse flags, this is equivalent to:
```bash
ora_tapi -To HR -t employees departments -c dev_db -a cbostock
```
If you need to force thick mode for a specific run, for example when a target environment requires Oracle Native
Network Encryption or checksumming, add `--oracle-client-dir`:

```bash
ora_tapi --oracle-client-dir /opt/oracle/instantclient_23_8 -To HR -t employees departments -c dev_db
```

You can omit `--oracle-client-dir` when the client libraries are in an `oracle_client` folder beside
the virtual environment. OraTAPI discovers that location automatically, subject to the
[lookup order](#using-oracle-instant-client) above.

#### More Advanced Example
Here we want to override the default target schemas for the packages, views, and triggers:
```bash
ora_tapi -To HR -t employees departments -c dev_db -a cbostock -po logic -to core -vo logic
```

Based on this last example, the DDL statements in the generated scripts will place the packages and views in the logic 
schema, and the triggers in the core schema.  



Remember that when these flags are not provided, the defaults are retrieved from the active profile's `OraTAPI.ini` file at `~/OraTAPIProfiles/configs/<active-profile>/resources/config/OraTAPI.ini`.

#### Explicitly Specifying Credentials
In the previous examples, we relied on the `OraTAPI` connection manager, in as much as we were using the `--conn_name` 
argument to specify a connection. This took advantage of a stored, named connection, called dev_db.  

If we don't want to use a named connection, the alternative is to specify:  

- db_username
- db_password
- dsn/TNS connect string

Taking the basic example, we can modify this to:
```bash
ora_tapi -To HR -t employees departments -a cbostock -u cbostock -p <my_password> -d dev-db
```
In this example, we assume that the dev-db is a TNS Names entry.  

**It is recommended that you use the connection manager approach.**

### Full Command-Line Arguments:

| Argument | Description | Default |
|----------|-------------|---------|
| `-A`, `--app_name`   | Application name included in the package header.               | `Undefined`      |
| `-a`, `--tapi_author`  | Author name for the package header.                    | `OraTAPI generator`  |
| `-D`, `--define NAME=VALUE` | Override metadata or custom template text for one run; repeat for multiple definitions. | No overrides |
| `-c`, `--conn_name`    | Connection name for saved configuration.                 |          |
| `-d`, `--dsn`      | Database Data Source Name (TNS entry).                   |          |
| `--oracle-client-dir`  | Oracle Instant Client directory to use for the current run.            |          |
| `-g`, `--staging_dir` | Directory for the staging area. Relative paths are resolved below `~/OraTAPIProfiles`. | `~/OraTAPIProfiles/staging` |
| `-G`, `--ut_staging_dir` | Directory for the Unit Test staging area. Relative paths are resolved below `~/OraTAPIProfiles`. | `~/OraTAPIProfiles/ut_staging` |
| `-p`, `--db_password`  | Database password.                         |          |
| `-po`, `--package_owner`  | Schema to own the generated TAPI packages (required).              |          |
| `-t`, `--table_names`  | A space separated list of table names.                  | All tables     |
| `-To`, `--table_owner`   | The table owner/schema on whose tables the generated APIs are to be based.       |          |
| `-to`, `--trigger_owner` | The schema in which the generated scripts should create the triggers.        |          |
| `-vo`, `--view_owner`  | The schema in which the generated scripts should create the views.         |          |
| `-u`, `--db_username`  | Database username.                         |          |
| `-T`, `--api_types`    | A space separated list of API types (e.g. `insert  select  update  delete  upsert  merge`). | Configured default types |
| `-U`, `--ut_api_types`   | A space separated list of Unit Test API types (e.g. `insert  select  update  delete  upsert  merge`). | Configured default types |

---

## Output Structure

Generated files are written to the staging area and organised into subdirectories:
- **Package Specification (`spec_dir`)**: Contains DDL source files defining the PL/SQL package interface.
- **Package Body (`body_dir`)**: Contains DDL source files implementing the PL/SQL package logic.
- **View (`view`)**: Contains DDL source files implementing any generated view scripts.
- **Trigger (`trigger`)**: Contains DDL source files implementing any generated trigger scripts.

Each API package is customised based on a combination of the `.ini` configuration, command-line options and template 
files. File extensions for package spec and body source files can be configured via the 
OraTAPI.ini file, under the `file_controls` section. Look for the `body_file_ext` and
`spec_file_ext` properties.

The majority of command line options have defaults which can be set via the OraTAPI.ini configuration file. 

---

These are just a few of the controls. Read on for further detail.

## Configuration Settings
### Control Files
As well as tailoring the templates to your requirements, the behaviour of OraTAPI, is governed by 3 files:
- OraTAPI.ini
- OraTAPI.csv
- pi_columns.csv


The last 2 of these are covered in subsequent sections, under [Fine-Grained File Controls](#fine-grained-file-controls). Here we cover the first of these files, the OraTAPI.ini file.

### The OraTAPI.ini File
The OraTAPI.ini provides the main controls for governing the OraTAPI behaviour.  

The OraTAPI.ini file is made up of named sections. The sections are denoted by square brackets in which the 
section name is enclosed. Within each section is one or more properties, used to control the behaviour in one way or 
another, of the `ora_tapi` command.  

As a reminder, the active file is located at `~/OraTAPIProfiles/configs/<active-profile>/resources/config/OraTAPI.ini`. When OraTAPI starts up, it reads `~/OraTAPIProfiles/active_config` to determine which profile is active, and then initialises settings from that profile's configuration file.

Here we cover the various sections and properties.
---

#### [project]
- **default_app_name**: Defines the default name of the application, used as a reference when generating API names.
  - Example: `default_app_name = Human Resources`
  - **Purpose**: Helps identify the application context for the generated APIs.

---

#### [copyright]
- **company_name**: Specifies the company name for the copyright information.
  - Example: `company_name = Clive's Software Emporium`
  - **Purpose**: Customises the copyright message in the generated code.
  
- **copyright_year**: Sets the year for the copyright, either as a static year or the word "current" to reflect the generation date.
  - Example: `copyright_year = current`
  - **Purpose**: Dynamically reflects the year when the TAPI was generated.

---

#### [behaviour]
- **skip_on_missing_table**: Determines whether missing tables are skipped or cause an error.
  - Example: `skip_on_missing_table = true`
  - **Purpose**: Controls error handling when a table specified in the API generation process is missing. If true, processing continues; if false, an error halts the process.

---

#### [formatting]
- **indent_spaces**: Defines the number of spaces for indentation in the generated SQL files.
  - Example: `indent_spaces = 3`
  - **Purpose**: Controls the indentation style to ensure consistent formatting across generated code.

  NOTE: The templates have embedded %STAB% substitution placeholders, which are replaced with the number of spaces as defined by the `indent_spaces` property.

---

#### [file_controls]
- **default_staging_dir**: Specifies the root directory where the generated files will be written.
  - Example: `default_staging_dir = /u02/projects/demo/staging`
  - **Purpose**: Defines the folder where all generated files will be placed. The default location is `staging`, which resolves to `~/OraTAPIProfiles/staging`. You can specify a pathname relative to `~/OraTAPIProfiles`, or a full pathname. This can be overridden at runtime, using the `-g/--staging_dir` argument.

- **default_ut_staging_dir**: Specifies the root directory where generated utPLSQL files will be written.
  - Example: `default_ut_staging_dir = ut_staging`
  - **Purpose**: Defines the default unit-test staging folder. The default location is `ut_staging`, which resolves to `~/OraTAPIProfiles/ut_staging`. You can specify a pathname relative to `~/OraTAPIProfiles`, or a full pathname. This can be overridden at runtime using the `-G/--ut_staging_dir` argument.
  
  Sub-directories are created at run-time, as required, to host the generated code. The names of the sub-directories are configurable (read on).
  
-  **spec_file_ext** & **body_file_ext**: Set the file suffix for the package body and specification files.
  - Examples:   
  `spec_file_ext = .pks`   
  `body_file_ext = .pkb`  
  - **Purpose**: Specifies the file extension for generated SQL files. The default for these is `.sql`.  
  

    NOTE: If the spec_dir and the body_dir are defined as the same directory, **spec_file_ext**  and **body_file_ext**  must be different.

- **spec_dir** & **body_dir**: Define the directories for package specification and package body files.
  - Example: `spec_dir = package_spec`
  - **Purpose**: Organises generated files in a specific staging subdirectory for clarity and structure.

- **trigger_dir**: Define the directory for trigger files.
  - Example: `trigger_dir = trigger`
  - **Purpose**: Organises generated trigger source files in a specific staging subdirectory for clarity and structure.

- **view_dir**:  Define the directory for view source files.
  - Example: `view_dir = view`
  - **Purpose**:  Organises generated view source files in a specific staging subdirectory for clarity and structure.

- **ora_tapi_csv_dir**: Defines the directory for the OraTAPI CSV file.
  - Example: `ora_tapi_csv_dir = resources/config`
  - **Purpose**: Used to control which files should be generated based on the CSV configuration file. OraTAPI resolves this setting as follows:
    1. If you supply an absolute path, OraTAPI uses that exact location.
    2. If you supply a relative path such as `resources/config`, OraTAPI resolves it under the active profile home, for example `~/OraTAPIProfiles/configs/<active-profile>/resources/config`.
    3. Packaged defaults are not used as a live fallback during generation. They are the source files that `quick_config` copies into `~/OraTAPIProfiles/configs/<active-profile>/resources/...` when a profile is bootstrapped.
    This allows fine grain control of which files should be generated and written/overwritten. New file entries are automatically added when tables are processed and no corresponding entry is found. In addition this also allows table domains (%table_domain_lc%) to be configured.

- **pi_columns_csv_dir**: Defines the directory for the OraTAPI CSV file.
  - Example: `pi_columns_csv_dir = resources/config`
  - **Purpose**: Used to control which columns should be omitted from parameter logging when the `llogger` templates are active. OraTAPI resolves this setting as follows:
    1. If you supply an absolute path, OraTAPI uses that exact location.
    2. If you supply a relative path such as `resources/config`, OraTAPI resolves it under the active profile home, for example `~/OraTAPIProfiles/configs/<active-profile>/resources/config`.
    3. Packaged defaults are not used as a live fallback during generation. They are copied into the active profile when you run `quick_config`.
    This is provided to avoid PI (personal information) columns being logged.
---

#### [api_controls]
- **delete_procname**: Specifies the procedure name to be used for the delete API.
  - Example: `delete_procname = del`
  - **Purpose**: Customises the naming conventions for the delete procedure.

- **select_procname**: Specifies the procedure name to be used for select API.
  - Example: `select_procname = get`
  - **Purpose**: Customises the naming conventions for the select procedure.

- **insert_procname**: Specifies the procedure name to be used for insert API.
  - Example: `insert_procname = ins`
  - **Purpose**: Customises the naming conventions for the insert API procedures.

- **merge_procname**: Specifies the procedure name to be used for merge API procedures.
  - Example: `merge_procname = mrg`
  - **Purpose**: Customises the naming conventions for the merge API procedures.

- **update_procname**: Specifies the procedure name to be used for update API procedures.
  - Example: `update_procname = upd`
  - **Purpose**: Customises the naming conventions for the update API procedures.

- **upsert_procname**: Specifies the procedure name to be used for upsert API procedures.
  - Example: `upsert_procname = ups`
  - **Purpose**: Customises the naming conventions for the upsert API procedures.

- **auto_maintained_cols**: A comma-separated list of columns managed automatically by triggers or column expressions (e.g., timestamps, user fields).
  - Example: `auto_maintained_cols = created_by, created_on, updated_by, updated_on`
  - **Purpose**: Prevents these columns from being included in data modification APIs, but they are returned in select APIs. These are assumed to be maintained by triggers, or OraTAPI column expressions.

- **col_auto_maintain_method**: Defines how auto-maintained columns maintained.
  - Example: `col_auto_maintain_method = trigger`
  - **Purpose**: Specifies whether column values are managed via database triggers or column expressions. For column expressions, the setting is `expression`.

- **row_vers_column_name**: Defines the column name used for optimistic locking.
  - Example: `row_vers_column_name = row_version`
  - **Purpose**: Supports optimistic locking column used for tracking changes to rows using a version number. Where the named column exists, generated writes enforce the version read by the caller; see [the version policy](#the-row_vers_column_name-property). In addition column expressions (see later) can be used to maintain the column. Alternatively triggers can be used.

- **signature_types**: Defines the API signature types (rowtype or coltype).
  - Example: `signature_types = rowtype, coltype`
  - **Purpose**: Determines whether to generate APIs which implement parameters as rowtypes (p_row) or column types (one parameter for each column). This must be set to `coltype` and / or `rowtype`.

- **include_defaults**: Includes default values for insert APIs.
  - Example: `include_defaults = true`
  - **Purpose**: Ensures that default values for table columns are included in insert APIs.

- **noop_column_string**: Defines a string to be used for non-key, character string type column parameter defaults.
  - Example: `noop_column_string = auto`
  - **Purpose**: Helps avoid passing unnecessary parameters by preserving existing values. Comment out or remove value assigned to disable the feature. The value can be set to a character string, the value `auto`, or `dynamic`. Setting to `dynamic` involves a slight resource overhead at runtime. Only works for character string columns (VARCHAR2, CLOB etc.)

- **default_api_types**: Specifies which types of APIs should be included by default.
  - Example: `default_api_types = insert, select, update, delete`
  - **Purpose**: Controls which API types are generated by default. Options are insert, select, update, delete, upsert, and merge. These can be overridden at runtime via the `-T/--api_types` command line argument.

- **return_pk_columns**: Determines whether primary key columns are included as in/out parameters in the generated APIs.
  - Example: `return_pk_columns = true`
  - **Purpose**: Ensures primary/unique key columns are returned in APIs that modify data.

- **return_ak_columns**: Determines whether unique key constraint columns are included as in/out parameters in the generated APIs.
  - Example: `return_ak_columns = true`
  - **Purpose**: Ensures primary/unique key columns are returned in APIs that modify data.

- **include_commit**: Defines whether a commit parameter should be included.
  - Example: `include_commit = true`
  - **Purpose**: Includes a commit parameter to implement a transactional behaviour.
  - 
---
#### [logger]
- **logger_pkg**: Specifies the name/alias of the logger package.
  - Example: `logger_pkg = logger`
  - **Purpose**: Defines the logger package name (optionally prefixed by the owning schema, e.g. logger_user.logger).
- **logger_logs**: Specifies the logger_logs table.
  - Example: `logger_logs = logger_logs`
  - **Purpose**: Defines the logger_logs table name (optionally prefixed by the owning schema, e.g. logger_user.logger_logs). This is used purely for data typing inside the generated package code.
- **skip_logged_data_types**: Specifies a comma-separated list of Oracle datatypes that must not have generated `logger.append_param` calls.
  - Example: `skip_logged_data_types = CLOB, NCLOB, BLOB, BFILE, LONG, LONG RAW, XMLTYPE, JSON, SDO_GEOMETRY`
  - **Purpose**: Prevents large or awkward datatypes from being logged. Entries can be bare datatype names such as `CLOB` or owner-qualified object types such as `MDSYS.SDO_GEOMETRY`.
- **skip_logged_data_types_mode**: Controls how blocked datatypes are rendered in generated logger parameter code.
  - Example: `skip_logged_data_types_mode = omit`
  - **Purpose**: Supports `omit` to generate no `append_param` call, `comment` to emit `-- skipped logger append for p_doc (XMLTYPE)`, and `redact` to emit an `append_param` call with a placeholder such as `[datatype skipped: XMLTYPE]`.
---
#### [schemas]
- **default_table_owner**: Specifies the default schema for tables.
  - Example: `default_table_owner = aut`
  - **Purpose**: Defines the default schema for tables on which APIs are based. This can be overridden by command-line argument (e.f.`-To <schema_name>`).

- **default_package_owner**: Specifies the default schema for packages.
  - Example: `default_package_owner = aut`
  - **Purpose**: Defines the default target schema for package creation. This can be overridden by command-line argument (e.g. `-po <schema_name>`).

- **default_view_owner**: Specifies the default schema for views.
  - Example: `default_view_owner = aut`
  - **Purpose**: Defines the default target schema for view creation. This can be overridden by command-line arguments (e.g. `-vo <schema_name>`).

- **default_trigger_owner**: Specifies the default schema for triggers.
  - Example: `default_trigger_owner = aut`
  - **Purpose**: Defines the default schema for trigger creation. This can be overridden by command-line argument (e.g. `-to <schema_name>`).

---

#### [misc]
- **view_name_suffix**: Defines a suffix to be added to the derived view name.
  - Example: `view_name_suffix = _v`
  - **Purpose**: Customises the name of generated views by appending the suffix.

---

#### [suite]

These settings live in `utplsql.ini`. See [the worked example](#worked-example-cfg_corecfg_json_mappings)
for the mapping sections and the complete resolution sequence.

- **mode**: `legacy` uses the literal suite text and existing annotation templates;
  `mapped` uses the declared package mappings. The default is `legacy`.
- **suite**: Literal text for `--%suite(...)`, only in effect when `mode = legacy`.
  For example, `suite = HR` supplies `HR`. A blank assignment is an empty string, not an
  automatically derived suite name; the executable-test generator supplies its own fallback
  name when the legacy suite text is blank.
- **suite_template**: Text for `--%suite(...)` in mapped mode. Default: `{package_name}`.
- **suitepath_template**: Hierarchy for `--%suitepath(...)` in mapped mode.
  Default: `{product}.{group}.{category}.{package_name}`.

---

#### [ut_controls]

- **tapi_owner**: Schema owning the production TAPI package. In mapped scaffold mode,
  this supplies `{production_owner}` and must match a package-location owner. It is not
  inferred from the table owner or the generated test-package owner, and does not indicate
  whether tests have run. A blank assignment means no owner is configured.
- **tested_tapi_contract**: Path to a production API contract file describing callable
  operations for executable tests. A relative path resolves against the profile root.
  A blank assignment means no file is configured here. Scaffold mode needs no contract;
  executable mode uses the TAPI generated in the same run when both `tapi` and `utplsql`
  outputs are selected. Test-only runs require this setting, `--tested-tapi-contract`,
  or an orchestrator `tested_component`. An explicitly supplied contract takes precedence
  over automatic generation and must match the source metadata.
- **test_generation_mode**: `scaffold` generates starter tests; `executable` uses a production
  API contract to generate executable test code. Default: `scaffold`.

- **enable_ut_code_generation**: Enables / disables code generation of utPLSQL packages.
  - Example: `enable_ut_code_generation = true`
  - **Purpose**: When set to true / on, utPLSQL code generation is switched on. When set to false / off utPLSQL code generation is disabled.

- **ut_prod_code**: Specify a string for the first (dot separated) component of the %suitepath annotation
  - Example: `ut_prod_code = hr`
  - **Purpose**: See utPLSQL documentation for more details on [annotations](https://www.utplsql.org/utPLSQL/v3.0.4/userguide/annotations.html).

- **ut_prod_sub_domain_code**: Specify a string for the second (dot separated) component of the %suitepath annotation
  - Example: `ut_prod_sub_domain_code = dept`
  - **Purpose**: If set to the value `auto_table`, the first characters of the table name, leading up to the first underscore are assumed. Otherwise 
    the value is taken as a literal. See utPLSQL documentation for more details on [annotations](https://www.utplsql.org/utPLSQL/v3.0.4/userguide/annotations.html).

- **ut_pkg_name_prefix**: Specify a string to be used as a prefix in formulating the generated package name. 
  - Example: `ut_pkg_name_prefix = ut_`
  - **Purpose**: Allows you to define a character string with which to append when generating the utPLSQL package names (<prefix_string><table_name><postfix_string>).

- **ut_pkg_name_postfix**: Specify a string to be used as a postfix when formulating the generated package name. 
  - Example: `ut_pkg_name_postfix = _tapi`
  - **Purpose**: Allows you to define a character string with which to append when generating the utPLSQL package names (<prefix_string><table_name><postfix_string>).

- **ut_uk_test_throws**: Specify a throws code to be used with procedures used to test key constraints. 
  - Example: `ut_uk_test_throws = dup_val_on_index`
  - **Purpose**: This provides the means for you to define an Oracle exception/error code to associate with the \%throws() annotation, associated 
                 with primary or unique key constraint test procedures.

- **ut_parent_fk_test_throws**: Specify a throws code to be used with procedures used to test parent key constraints. 
  - Example: `ut_parent_fk_test_throws = -02291`
  - **Purpose**: This provides the means for you to define an Oracle exception/error code to associate with the \%throws() annotation, associated 
                 with (parent) foreign key constraint test procedures.

- **ut_cc_test_throws**: Specify a throws code to be used with procedures used to test check constraints. 
  - Example: `ut_cc_test_throws = -02290`
  - **Purpose**: This provides the means for you to define an Oracle exception/error code to associate with the \%throws() annotation, associated 
                 with check constraint test procedures.

- **ut_nn_test_throws**: Specify a throws code to be used with procedures used to testing not null constraints. 
  - Example: `ut_nn_test_throws = -01400`
  - **Purpose**: This provides the means for you to define an Oracle exception/error code to associate with the \%throws() annotation, associated 
                 with not null, check constraint testing procedures.


---

#### [console]
- **INFO_COLOUR**, **WARN_COLOUR**, **ERR_COLOUR**, **CRIT_COLOUR**, **HIGH_COLOUR**: Defines colour schemes for different output categories.
  - Example: `INFO_COLOUR = white`
  - **Purpose**: Customises the colours used in the console output for different message priority levels (info, warning, error, etc.).

- **colour_console**: Enables or disables colour output in the console.
  - Example: `colour_console = true`
  - **Purpose**: Controls whether coloured output is shown in the console.

---

#### Example configuration file:

```ini
[OraTAPI]
version = oratapi-<x.y.z>

[project]
default_app_name = Human Resources

[copyright]
# company_name: Modify company name to reflect your company
company_name = Clive`s Software Emporium
# copyright_year: set to a static year or the word current, to reflect the date the TAPI was generated.
copyright_year = current

[behaviour]
# skip_on_missing_table: If set to true and a specified table is not found, then report the table as missing
# but continue processing. If set to false, and error is reported and processing is terminated. Only pertinent
# when using the -t/--table_names argument.
skip_on_missing_table = true

[formatting]
indent_spaces = 3

[file_controls]
# The root location where the generated files are to be written. A simple directory name is assumed to be located
# below ~/OraTAPIProfiles. Full path-names are permissible.
default_staging_dir = staging

# Unit Tests package staging area. Relative paths are also resolved below ~/OraTAPIProfiles.
default_ut_staging_dir = ut_staging
# The file extension properties are appended to the respective files.
body_file_ext = .sql
spec_file_ext = .sql

# spec_dir/body_dir: these define the locations where the package specification and package body files are to be
# written. Simple names (no slashes) are assumed to be below the staging directory.
spec_dir = package_spec
body_dir = package_body
# Set the trigger_dir property to have any triggers generated from the trigger templates.
trigger_dir = trigger
# Set the view_dir property to have any triggers generated from the view templates.
view_dir = view

# Set the directory pathname to locate the OraTAPI.csv file. Relative paths are resolved from the active
# profile home, so resources/config means ~/OraTAPIProfiles/configs/<active-profile>/resources/config.
# This file is used to fine control which files should be generated.
ora_tapi_csv_dir = resources/config

# Set the path to the OraTAPI pi_columns.csv file. This CSV file is used to flag columns as personal information.
# Such columns are not logged when using the llogger format templates. Relative paths are resolved from the active
# profile home, so resources/config means ~/OraTAPIProfiles/configs/<active-profile>/resources/config.
pi_columns_csv_dir = resources/config

[api_controls]
# API naming properties follow. Set these to the preferred procedure names of the respective APIs
delete_procname = del
select_procname = get
insert_procname = ins
merge_procname  = mrg
update_procname = upd
upsert_procname = ups

# col_auto_maintain_method: Set to `trigger` or `expression`, it is assumed that your table triggers
# are to manage the modification of the columns. However, if set to `expression`, you must define a column expression
# for each of the named columns.
col_auto_maintain_method = trigger

# auto_maintained_cols is a comma separated list of columns which are not to be set by the TAPI parameters.
# These are columns typically auto-maintained by triggers or column expressions. As such they are not included in APIs
# responsible for data modifications. However they are included in select API return parameters.
auto_maintained_cols = created_by, created_on, updated_by, updated_on

# row_vers_column_name: For optimistic locking (optional). Name the optimistic column name.
# Leave empty to disable. When present on a table, update/delete require the version read.
# Upsert/merge: null version inserts only; non-null version updates only if it matches.
# Trigger or expression must advance the version. Conflicts raise -20003; missing version -20004.
row_vers_column_name = row_version

# signature_types: One of more comma separated values. Valid values: rowtype, coltype. Default is rowtype.
# coltype causes parameter signatures with a parameter for each table column, for the select, insert, update and
# merge APIs. rowtype, causes signatures based on primary keys and a table rowtype.
signature_types = rowtype, coltype

# include_defaults: Set to true, to have parameter defaults included to insert APIs,
# reflect those in the data dictionary.
include_defaults = true

# noop_column_string: If set, parameter defaults for non-key column parameters are defined as <no_column_op_string>.
# If the default is detected, then the column value in the database is preserved. This provides a mechanism of
# avoiding to pass all parameters unnecessarily. This only applies to the "coltype" signature types (see the
# signature_types property). Set to auto, to have a (static) generated, enhanced GUID (42 characters in total) Set to
# dynamic to have the NOOP character string (partly, by sys_guid()) dynamically generated on a per-session basis.
# noop_column_string = auto
# noop_column_string = #NO~OP#

# default_api_types: Specifies the default of which APIs to include to the package.
# Comma separated - must be one or more of insert, select, update, delete, upsert, merge.
# default_api_types = insert, select, update, delete, upsert, merge
default_api_types = insert, select, update, delete

# The parameters influence the generated name packages.
# The default package name format is <table_name_lc>_tapi
tapi_pkg_name_prefix =
tapi_pkg_name_postfix = _tapi

# return_pk_columns: If set to true, causes primary/unique keys to be in/out parameters. Returning the values.
# This applies to APIs which modify data.
return_pk_columns = true
return_ak_columns = false
# Include p_commit boolean parameter (in). Should be set to true or false. Typically this would be set to false.
include_commit = false

[logger]
# If you have not set up synonyms, we need prefix with the schema where logger is installed.
# By default we assume logger_user. If you have run create_logger_synonyms.sql, you don't need to
# prefix these. If these are absent, defaults are assumed to be `logger` and `logger_logs`. The `llogger` sample
# templates take advantage of these settings.
logger_pkg = logger_user.logger
logger_logs = logger_user.logger_logs
skip_logged_data_types = CLOB, NCLOB, BLOB, BFILE, LONG, LONG RAW, XMLTYPE, JSON, SDO_GEOMETRY
skip_logged_data_types_mode = omit

[schemas]
# Set default owners. These can be overridden on the command line.
# default_table_owner can be overridden using the -to / --table_owner argument.
default_table_owner = aut

# default_package_owner can be overridden using the -po / --package_owner argument.
default_package_owner = aut

# default_view_owner can be overridden using the -vo / --view_owner argument.
default_view_owner = aut

# default_trigger_owner can be overridden using the -to / --trigger_owner argument.
default_trigger_owner = aut

[misc]
# The view_name_suffix is appended to the end of the derived view name
view_name_suffix = _v

[console]
INFO_COLOUR = white
WARN_COLOUR = bold yellow
ERR_COLOUR = bold red
CRIT_COLOUR = bold red
HIGH_COLOUR = bold blue
# Set colour_console to false, to disable colour output.
colour_console = true
```

### Fine-Grained File Controls

The OraTAPI.ini file has been covered in the previous sections. Here we look at the CSV controls.

#### Controlling File Updates
Fine-grained control over which files can or cannot be updated, is implemented via the OraTAPI.csv file. The location of 
this file is determined via the `ora_tapi_csv_dir` property, which resides in the `file_controls` section of the 
`OraTAPI.ini` file. If the associated property is unset, `ora_tapi` will assume its 
location as the active profile's `resources/config` directory, i.e. `~/OraTAPIProfiles/configs/<active-profile>/resources/config`. The supplied OraTAPI.ini sample,
sets this location to `resources/config`.  

The OraTAPI.csv file is not provided at installation time. It is instantiated into the active profile area under `~/OraTAPIProfiles/configs/<active-profile>/resources/config` by `quick_config`, and then created and populated further as you run `ora_tapi`. The file contents should be maintained as a spreadsheet, but
ensure that it is saved as a CSV file when exporting it from the spreadsheet application.

Each row represents a schema / table. The following 
columns are represented:

- Schema Name
- Table Name
- Domain
- Packages Enabled
- Views Enabled
- Triggers Enabled

The file is auto-populated when you generate scripts. If a schema/table combination is missing, a row is automatically 
added. Once rows are added, you can maintain the last 3 columns. Setting these to `True`, `1`, or `On` instructs OraTAPI 
that the respective files can be created/overwritten. Setting these to `False`, `0` or `Off` will instruct OraTAPI to not 
create/overwrite the file.  

Note that OraTAPI updates the file after each run and all settings are normalised to either 
`True` or `False`.

The `Domain` column is provided so that table domain mappings can be recorded. These are then automatically substituted 
to the %table_domain_lc% substitution string in the templates.

#### PI (Personal Information) Columns & Logging
If you wish to avoid logging PI data, you can leverage the pi_columns.csv file to achieve this.  
Like `OraTAPI.ini` and `OraTAPI.csv`, `pi_columns.csv` is maintained per profile under
`~/OraTAPIProfiles/configs/<active-profile>/resources/config/pi_columns.csv`, so different profiles can carry different PI
column rules.
This is only pertinent, if you are working with the `logger` or `llogger` based templates (or similar).  

Datatype-based suppression is configured separately under `[logger]` via `skip_logged_data_types` and
`skip_logged_data_types_mode`. This allows large or special-case datatypes such as `CLOB`, `BLOB`, `XMLTYPE`,
`JSON`, and `SDO_GEOMETRY` to be handled in one of three ways:

- `omit`: generate no `logger.append_param` call
- `comment`: generate a comment such as `-- skipped logger append for p_doc (XMLTYPE)`
- `redact`: generate an `append_param` call that logs a placeholder such as `[datatype skipped: XMLTYPE]`

The file contains the following columns:

- Schema Name
- Table Name
- Column Name
- Description

This allows you to map out the columns that should be omitted from logging. You can set exact matches for `Schema Name` 
and / or `Table Name`, or you can wild-card the entries with any of the following: `%`, `*` or `all`. You must always 
enter an exact column name. The Description is optional, but allows you to describe why the column has been entered to 
the list.  

When generating the parameter logging commands, a check is made to see if a match is found. If a match is found, then the parameter logging statement is commented out, and prepended with the string `PI column: `. Example:  

```
logger_user.logger.append_param(l_params, '* p_row.employee_id', p_row.employee_id);
logger_user.logger.append_param(l_params, '  p_row.first_name', p_row.first_name);
-- PI column: logger_user.logger.append_param(l_params, '  p_row.last_name', p_row.last_name);
-- PI column: logger_user.logger.append_param(l_params, '  p_row.email', p_row.email);
-- PI column: logger_user.logger.append_param(l_params, '  p_row.phone_number', p_row.phone_number);
logger_user.logger.append_param(l_params, '  p_row.hire_date', p_row.hire_date);
logger_user.logger.append_param(l_params, '  p_row.job_id', p_row.job_id);
logger_user.logger.append_param(l_params, '  p_row.salary', p_row.salary);
```
The pi_columns.csv file contents should be maintained as a spreadsheet, but ensure that it is saved as a CSV file when 
exporting it from the spreadsheet application.

---

## Auto Column Management
### What are Auto-managed Columns?
In this context, the term auto-managed columns, refers to columns whose data are not managed directly via the application. 
Rather, they are populated/updated by table triggers, default values or expressions which are effectively virtualised 
by the API.  


### Configuring the Column Management Method
Under the `api_controls` section of `OraTAPI.ini`, there are two entries pertaining to auto managed columns. These allow 
you to configure how you manage your auto-managed columns. Because the management is made almost transparent to the 
developer, there are no input parameters to populate them via the API. For example, you may have columns which are used 
to track who created, or last updated a row. The entries that control the behaviour are:

- col_auto_maintain_method
- auto_maintained_cols
- row_vers_column_name

### The col_auto_maintain_method Property
If you are using columns which you want to be automatically updated during DML operations, you should set this property 
value to one of: 

- trigger
- expression

#### Maintained by Trigger 
If you set the `col_auto_maintain_method` property to <i>trigger</i>, you should ensure that your trigger template(s) 
are designed to make appropriate updates to the columns that are listed via this property.
```
create or replace trigger %trigger_owner_lc%.%table_name_lc%_biu
before insert or update on %table_owner_lc%.%table_name_lc%
for each row
begin

   if inserting then
      :new.row_version := 1;
   elsif updating then
      :new.updated_on := current_timestamp;
      :new.updated_by := coalesce(sys_context('APEX$SESSION','APP_USER'), sys_context('USERENV', 'PROXY_USER'), sys_context('USERENV','SESSION_USER'), user);
      :new.row_version := :old.row_version + 1;
   end if;

end;
/
```
#### Maintained by Column Expression
Column expressions are configured using special templates located in the resources/templates/column_expressions 
directory. This directory contains two subdirectories, allowing you to differentiate column expressions for 
inserts and updates. The subdirectories are listed here:

- inserts
- updates

If the col_auto_maintain_method property is set to expression, then for each column listed in the auto_maintained_cols 
and row_vers_column_name properties, a corresponding template entry is required in both the inserts and updates 
directories. These expressions are injected into assignment statements for the generated API procedures.

For example, assume we have a column called row_version. We would expect to find a row_version.tpt file in both the 
inserts and updates directories. The contents of these files might look like this:

inserts/row_version.tpt:
```
1
```
updates/row_version.tpt:
```
row_version + 1
```
When it comes to the "who" columns, we have to be slightly creative. For example, take the `created_by` column; we 
might have something like this:

inserts/created_by.tpt:
```
current_user
```
updates/created_by.tpt:
```
created_by
```
Because we must satisfy the requirement to include an `updates\created_by.tpt` entry, we just have it set the column to 
its current value.

### The auto_maintained_cols Property
This is a comma separated list of column names which are maintained either by table triggers or by use of column 
expressions, configured to appear within the generated TAPIs (more on these a little later).

This list should not include the column included to the `row_vers_column_name` property (if one is set).

### The row_vers_column_name Property

Set `row_vers_column_name = row_version` in the active profile's
`resources/config/OraTAPI.ini`, under `[api_controls]`, to protect writes with
the version that the caller read. Leave it empty to disable this behaviour.
Tables without the configured column retain their existing behaviour.
Keep this column out of `auto_maintained_cols`; a trigger or the configured
column expression must advance it on every update.

For example, if two callers read version 7, the first update matches version 7
and advances it to 8. The second update still supplies 7, affects no row and
raises `ORA-20003: Row missing or changed since it was read`.
The key and version are checked in the same SQL statement.

| API | Version supplied by caller | Behaviour |
| --- | --- | --- |
| Insert / select | Output parameter | Returns the stored version. |
| Update / delete | Required; scalar parameter is `IN OUT` | Matches primary key and version; null raises ORA-20004. |
| Upsert | Scalar parameter is `IN OUT` | Null means insert only; non-null means update only with a matching version. |
| Merge | Input parameter | Same strict intent as upsert; does not return the new version. |

Record signatures use `p_row.row_version`. Update and upsert return the new
stored version; delete returns the deleted row's version. Version returns do
not depend on `return_pk_columns` or `return_ak_columns`.
A stale or missing row raises ORA-20003. Insert-only intent with an existing
primary key raises ORA-00001. A failed update never falls through to an insert.
The optional commit occurs only after the write succeeds.

**Migration:** regenerate package specifications and bodies together, and update
callers to supply the version they read. Use null deliberately for insert-only
upsert/merge calls. Migrate customised update, delete, upsert and merge templates
from the matching shipped samples, preserving their logging and project edits.
Generation rejects protected templates without the required locking placeholders.
Exported TAPI contracts now use version 2; regenerate older contracts and their
executable tests.

Executable tests include stale-version, missing-row, missing-version and
insert-existing scenarios where applicable. They verify the error and that the
failed call leaves stored data unchanged. Their fixture requirements still apply;
generation does not execute these tests.

Tables with no assignable columns cannot generate update, upsert or merge:
OraTAPI warns with the table and operation, skips that TAPI and its requested
UT package, and continues with the remaining tables. Views and triggers can
still be generated for the rejected table. The run report records the skips.
Existing staged package files for the rejected table are left unchanged, so
do not deploy those older files as part of the new run.
Remove update-capable operations for such tables to generate their other APIs.

## utPLSQL Support
Suite labels and suite-path components can be supplied with [template overrides for one run](#template-overrides-for-one-run).
The active profile must already have utPLSQL generation enabled.

An opt-in [executable test pilot](resources/pilot/README.md) adds metadata-driven calls, assertions and inline fixture instructions.
Set `test_generation_mode = executable` to use this generator. The default remains `scaffold`.
Production code and executable tests can share a profile and be generated together, without a contract file.
The pilot includes controlled tables, a source maintenance workflow and a separate Oracle validation runner;
generation and orchestration do not deploy or execute tests.

### One command for production code and executable tests

In the chosen profile's `resources/config/OraTAPI.ini`, enable combined generation:

```ini
[behaviour]
enable_tapis_when_ut_enabled = true
```

In its `resources/config/utplsql.ini`:

```ini
[ut_controls]
enable_ut_code_generation = true
test_generation_mode = executable
# Blank: use the production API generated in this same run.
tested_tapi_contract =
# Blank: take the production owner from --package_owner in the combined run.
tapi_owner =
```

The profile also needs the `resources/templates/ut_executable` templates supplied by
`quick_config`. The local `agr_apps` profile has these settings and templates.
Executable tests use the API contract's target for setup, verification and datatype
anchors. With `api_surface = view`, these reference the configured API view, not
the base table. The test owner needs direct `SELECT`, `INSERT`, `UPDATE` and
`DELETE` privileges on those views, plus `EXECUTE` on the TAPI packages. Base-table
metadata still supplies columns, constraints and expected Oracle error identities;
`api_surface = table` retains direct table access. Standalone generation uses the
target recorded in the supplied contract.

Foreign-key checks against other tables need an authorised parent API query: a
single-table contract does not identify those parents' views. In view mode these
checks contain an explicit `FIXTURE REQUIRED` failure to replace, rather than
querying parent base tables or guessing view names. Self-referencing checks use
the contracted view. Existing hand-written fixture code is not rewritten.

For example, using your saved connection name in place of `my_connection`:

```bash
oratapi --profile agr_apps -c my_connection -To cfg_core -t cfg_json_mappings --package_owner cfg_api --ut-package-owner cfg_test --outputs tapi utplsql
```

This generates the production TAPI in `cfg_api` and tests in `cfg_test`, using the
columns and constraints of `cfg_core.cfg_json_mappings`. OraTAPI passes the generated
API's signatures and owner directly to the test generator in memory. No contract
file is needed or written. `--ut-package-owner` defaults to `--package_owner` when
omitted; the table owner does not determine either package owner. A nonblank
`tapi_owner` must agree with `--package_owner` for automatic combined generation.
Use `-g PATH` and `-G PATH` to override production and test staging locations.

For `api_surface = view`, `--view_owner` can differ from both package owners.
For example:

```bash
oratapi --profile agr_apps -c my_connection \
  --table_owner asv_core_test --package_owner unit_test \
  --ut-package-owner unit_test --view_owner asv_api_test \
  --trigger_owner asv_api_test --outputs tapi utplsql view trigger
```

Generated TAPI SQL and `%TYPE`/`%ROWTYPE` references use the view's schema explicitly;
table-based APIs use the source table's schema. Contracts record that same target
independently of the package owner. Triggers retain their selected owner and target
the source table. Omitted owners use the existing profile defaults, except
`--ut-package-owner`, which defaults to `--package_owner` in combined generation.

Existing shipped profile templates work without being recreated. Within procedure
templates, `%table_name_lc%` and `%table_name%` now expand to the qualified SQL target;
package names and output filenames still use the bare source-table name. Review
custom procedure templates that already prepend a schema to these placeholders.
OraTAPI does not create schemas or grants; the required access between schemas must
be provided during deployment. Generation does not verify database compilation.

Use `--api-contract-out PATH` only when you want to keep a contract for a later run.
A test-only run (`--outputs utplsql`) still needs `--tested-tapi-contract PATH` or
`tested_tapi_contract` in the profile. Relative configured paths resolve against
that profile. The contract is used during generation; the generated PL/SQL does
not need it afterwards. The controlled executable pilot retains its explicit
contracts for its separate generation and validation steps.

The command works in macOS shells and Windows Git Bash. Quote paths containing
spaces. Temporary orchestrator contracts use Python's platform-specific temporary
directory; child commands receive argument lists without shell expansion.
Generation does not install or run the SQL. Some generated scenarios still need
fixture values or supporting rows, identified in `fixture-requirements.json` and
the generated source.

Executable test generation supports `CHAR`, `VARCHAR2`, `NCHAR`, `NVARCHAR2`,
`NUMBER`, `DATE`, `BOOLEAN`, `RAW`, native `JSON`, `MDSYS.SDO_GEOMETRY`, and all three timestamp forms: plain,
`WITH TIME ZONE`, and `WITH LOCAL TIME ZONE`, including fractional-second precision.
SQL `BOOLEAN` columns require a database version that supports them.
Time-zone test values use explicit UTC offsets; assertions compare UTC instants,
preserving fractional seconds and utPLSQL's handling of nulls. Equal instants with
different time-zone offsets are treated as equal. Generated packages use utPLSQL
directly and do not require a separate assertion utility package.

Native JSON uses small generated documents when constraints permit. Comparisons use
Oracle's `JSON_EQUAL`, ignoring insignificant whitespace and object-key order, while
distinguishing SQL nulls from non-null documents. Documents with duplicate object keys
are unsuitable for these equality assertions because Oracle leaves their comparison
unspecified. JSON-specific validation rules may still require supplied test data.

Spatial tests retain API calls and assertions but require inline geometry fixtures:
column metadata alone does not establish a suitable coordinate system, dimensions or
geometry type. Comparisons use `SDO_UTIL.TO_WKBGEOMETRY` and `DBMS_LOB.COMPARE`, together
with the SRID. This checks the WKB representation and coordinate-system identifier,
not topological equivalence with a tolerance. Fixture geometries must be supported by
Oracle's WKB conversion. Both-null values compare equal; a single null does not.

Unsupported datatypes produce console warnings naming the table, columns and types,
and are recorded in `fixture-requirements.json`. They currently prevent executable
API tests for that table. Each generated test package also reports scenario counts:
generated without placeholders, requiring test data, unsupported, and not applicable.
Package counts indicate files generated, not tests passed. Supported datatypes can
still require supplied test data for relationships, constraints or unique keys.

#### Understanding UT scenario counts

A scenario is an individual test case within a generated utPLSQL package, not a
package, table or datatype. Cases cover API operations, separate insert/update
branches of upsert, and applicable constraint checks. Different API signatures
can produce separate cases for the same operation.

| Console category | Meaning | What to do |
| --- | --- | --- |
| Generated without placeholders | The test code has no setup sections awaiting completion. It has not necessarily been compiled or passed. | Compile and run it to verify the generated test and its assumptions. |
| Require test data | API calls and assertions are generated, but marked setup sections need valid values or supporting rows. | Find `FIXTURE REQUIRED` in the package body and complete the setup described there. |
| Unsupported | The generator cannot produce an executable test for that scenario. A failure stub records the reason. | Review the reason in the generated body or `fixture-requirements.json`. |
| Not applicable | A candidate scenario does not apply to the API or constraint and is omitted from the generated test procedures. | Consult the report for the reason; no setup completion is required for that case. |

**Unsupported counts scenarios, not unsupported datatypes.** A datatype restriction
can block many scenarios. Other causes include deferred constraint enforcement or
being unable to construct a violation of one constraint without violating another.
A separate datatype warning names the affected table, columns and types.

For example, `0 generated without placeholders, 21 require test data, 0 unsupported,
14 not applicable` means 21 test cases need their setup completed, none was rejected
as unsupported, and 14 candidate cases were excluded. It does not mean that 21
tests failed or that 14 datatype checks were skipped. A missing parent row for a
foreign key is a typical reason for requiring test data.

The scenario summary is a warning when cases require test data, are unsupported,
or none is generated without placeholders. These are generation counts, not test
results. The `ora_tapi` command generates SQL files; it does not install packages
or execute tests. Compile and run the generated tests separately using utPLSQL.
The JSON report in the utPLSQL staging directory records each scenario's generation
status and reason.

#### Reading a FIXTURE REQUIRED section

A fixture is the data and setup needed by a test. Each `FIXTURE REQUIRED` block
describes one data requirement at a particular point in a test procedure. It may
cover one column or several related columns; it is not necessarily the complete
setup for the test or a request to insert one database row.

| Label | Meaning |
| --- | --- |
| `FIXTURE REQUIRED` | Identifies the underlying data requirement. The same requirement can occur in several tests. |
| `Section` | Identifies this particular occurrence in the generated source. The JSON report links it to its file and scenario. |
| `Scenario` | Identifies the individual test: source table, API surface, operation, branch, signature and, where relevant, target constraint. |
| `variation` | Specifies the setup needed at this occurrence, such as `valid`, `changed`, `violate:<constraint>` or `locate`. |
| `Purpose` | Explains why the generator needs supplied data rather than automatically generated values. |
| `Populate` | Names the variable to assign and gives the column's datatype and metadata. |
| `Constraint` and parent references | Describe relationships and rules that the supplied values must satisfy. |
| Run token | Names an available token that setup code can use to distinguish data belonging to this test execution. |

For `valid`, supply valid input values, including suitable existing parent keys.
For `changed`, preserve the primary key and supply a valid changed row.
For `violate:<constraint>`, supply values that violate only the named constraint.
For `locate`, identify the row just created by the API so its stored values can be
checked. Follow the variables and instructions in that specific section.
The general guidance may describe several variations; only the variation shown
beside `Scenario` is requested at that occurrence.

For example, a test of `GTR_DATREJ_GTR_DATRST_FK2` may contain a `valid` section
asking you to populate `l_row.data_file_id`. That section keeps the separate
`GTR_DATREJ_GTR_DATFIL_FK1` relationship valid by referencing a suitable row in
`ASV_CORE_TEST.GTR_DATA_FILES`. Another part of the test prepares the violation of
the target foreign key. Keeping the other relationships valid lets the test check
that the intended constraint caused the failure.

Replace the explicit failure statement immediately following the block with the
required setup code. Do not perform the API operation under test in that section,
commit, run DDL, or modify shared baseline rows. “Golden parent rows” means stable,
known-valid reference rows that tests read without changing. Consult both the
generated source and `fixture-requirements.json`; the report is an inventory, not
an input for supplying fixture values.

### Generate TAPIs now and executable tests later

Combined generation is optional. A single retained contract can describe several
production TAPI packages, with one entry for each source table processed in that
generation run. For example, generate two TAPIs and retain their shared contract:

```bash
oratapi --profile agr_apps -c my_connection -To cfg_core -t cfg_json_mappings cfg_settings --package_owner cfg_api --outputs tapi --api-contract-out "./contracts/cfg-tapis.json"
```

Later, generate their executable tests using the same profile:

```bash
oratapi --profile agr_apps -c my_connection -To cfg_core -t cfg_json_mappings cfg_settings --package_owner cfg_test --outputs utplsql --tested-tapi-contract "./contracts/cfg-tapis.json"
```

For this test-only run, `--package_owner cfg_test` selects the test-package owner.
The production owner, `cfg_api`, comes from the contract. Keep the profile's
`tested_tapi_contract` and `tapi_owner` blank as in the preceding example.
Command-line contract paths above are relative to the working directory.

Each selected table is matched to its contract entry by source schema and table
name. That entry supplies the production package name, owner and callable
signatures. The test profile supplies the test-package naming prefix and suffix;
the command supplies its owner. With the usual `ut_` prefix and `_tapi` suffix:

| Source table | Production package | Generated test package |
|---|---|---|
| `cfg_core.cfg_json_mappings` | `cfg_api.cfg_json_mappings_tapi` | `cfg_test.ut_cfg_json_mappings_tapi` |
| `cfg_core.cfg_settings` | `cfg_api.cfg_settings_tapi` | `cfg_test.ut_cfg_settings_tapi` |

To generate tests for only one of these tables, name just that table after `-t`;
the same contract file can be used. A selected table missing from the contract
produces an error. If its source metadata has changed, OraTAPI rejects the stale
contract and asks you to regenerate it. Both runs read database metadata, so the
later test-generation run still needs a connection. Once generation is complete,
the resulting PL/SQL packages do not need the contract file.

### Overview
In the default scaffold mode, OraTAPI provides starter utPLSQL packages for generated table APIs. This support provides for
the generation of the package spec, which includes requisite utPLSQL annotations and configurable %throws codes for table column 
constraints.  

When generated, the generated package specs are more or less complete (depending on your specific requirements).
The generated package body, includes a matching set of procedures with stubbed bodies.
Hints, by the way of comments, are included to each, providing details such as columns associated with constraints,
search conditions and parent tables (foreign key related) are listed.

### Controls
utPLSQL code generation is controlled by properties in the `ut_controls` section of the profile’s `utplsql.ini` file.
There is also a related property, `enable_tapis_when_ut_enabled` under the `behaviour` section. This should be set to false, if
you wish to disable generation of TAPI, View and Trigger code whilst generating utPLSQL package code.
The control properties associated with utPLSQL package generation are described under the [\[ut\_control\]](#ut_controls) 
subsection.


## Template Substitution Strings
Any properties from the OraTAPI.ini file may be interpolated into the templates.  

[Template overrides for one run](#template-overrides-for-one-run) describes `-D/--define` for overriding selected
metadata or supplying custom placeholders. Configuration controls and generated SQL substitutions are protected.

**When embedding into the templates, the substitution strings must be delimited by a pair of % characters**.  

In addition, the following may be used.

| Substitition String      | Description                                                                                      |
|--------------------------|--------------------------------------------------------------------------------------------------|
| STAB                     | Indent Tab-space (%STAB% is converted to [OraTAPI.ini specified] indent_spaces number of spaces) |
| package_owner_lc         | The (lowercase) target schema in which the generated package(s) will be placed                   |
| table_domain_lc          | The table domain mapping (maintained in OraTAPI.csv)     
| table_name_lc            | Table name (in lowercase)                                                                        |
| table_owner_lc           | Table schema (in lowercase)                                                                      |
| tapi_author_lc           | TAPI author (in lowercase)                                                                       |
| tapi_pkg_name_prefix_lc  | Package name prefix (in lowercase)                                                               |
| tapi_pkg_name_postfix_lc | Package name postfix (in lowercase)                                                              |
| trigger_owner_lc         | Target trigger schema (in lowercase)                                                             |
| view_name_suffix_lc      | View name postfix (in lowercase)                                                                 |
| view_owner_lc            | Target Table schema (in lowercase)                                                               |


## Connection Manager

The connection manager allows you to treat database connections in a similar manner to named connections in `SQLcl`. Connection settings and DSN (TNS) strings can be stored and retrieved locally by use of a convenient name. Passwords are transparently encrypted/decrypted from a locally maintained store. OraTAPI maintains its own connection store and does not read SQLcl's `.dbtools` files. The `conn_mgr` command allows you to save a connection by using a combination of the following command line arguments:

- -c NAME / --create NAME
- -e NAME / --edit NAME
- -d NAME / --delete NAME
- -l / --list
- -C / --print-creds
- --auth-type password / oci_iam_token

This allows you to:

- Add new connections
- Update connections
- Delete connections
- List existing connections
- List existing connections with decrypted credentials

The Add and Update options cause the `conn_mgr` to enter an interactive dialog mode.

When creating or editing a saved connection, you may provide either a wallet ZIP path or an extracted wallet
directory. OraTAPI extracts ZIP files to a temporary directory at runtime and uses aliases from the wallet's
`tnsnames.ora`. A wallet directory is used read-only. Thin mode requires `ewallet.pem`; thick mode requires
`cwallet.sso`.

Synopsis:

```
conn_mgr -h

usage: conn_mgr.py [-h] (-c NAME | -e NAME | -d NAME | -l) [-C] [-t {dsn,url}]
                   [--auth-type {password,oci_iam_token}]

Database connection manager.

options:
  -h, --help            show this help message and exit
  -c NAME, --create NAME
                        Create a new connection.
  -e NAME, --edit NAME  Edit an existing connection.
  -d NAME, --delete NAME
                        Delete an existing connection.
  -l, --list            List all connections.
  -C, --print-creds     If used with --list, includes decrypted password
                        credentials; IAM secrets are never displayed.
  -t {dsn,url}, --credential-type {dsn,url}
                        Type of credential to use (default: dsn).
  --auth-type {password,oci_iam_token}
                        Authentication type. New connections default to
                        password; existing connections retain their stored
                        type unless this option is supplied.

Used to create/edit/delete or store named database connections. Database
connections are stored, encrypted, in a local store.

NOTE: For OraTAPI, you should not use the -t flag, if you do, you should specify dsn.

```
Supply the connection name directly after `-c/--create`, `-e/--edit`, or `-d/--delete`. Exactly one of these actions or `-l/--list` is required. The `-C/--print-creds` option is only meaningful with `-l/--list`.

The separate `-n/--name` option has been removed. Existing scripts must replace forms such as `conn_mgr -c -n dev` with `conn_mgr -c dev`. Both `conn_mgr` and `conn-mgr` accept the same options.

Examples:

```bash
conn_mgr -c dev
conn_mgr --edit dev
conn_mgr -d dev
conn_mgr -l
conn_mgr -l -C
```

Using `-C/--print-creds` causes `conn_mgr` to attempt to decrypt and display the stored username and password for each saved connection. This is intended for local inspection on the machine that created the credential store.

For OCI IAM connections, `-C/--print-creds` reports only whether a wallet password has been saved. It never prints
the wallet password, IAM token, or private key.

### OCI IAM token connections

Before using an OCI IAM connection, obtain or renew the database token using the OCI profile and authentication method
appropriate to your environment. For example:

```bash
oci iam db-token get
```

Security-token profiles may instead require options such as `--auth security_token` and `--profile`. OraTAPI does not
run this command automatically. By default it reads `token` and `oci_db_key.pem` from `~/.oci/db-token`; a different
directory can be recorded when the connection is created or edited.

Create the OraTAPI connection as follows:

```bash
conn_mgr --create dev_iam --auth-type oci_iam_token
```

Enter the TNS alias, the original wallet ZIP or a suitable extracted wallet directory, and the wallet password if an
encrypted PEM wallet will be used in thin mode. Then use the connection normally:

```bash
ora_tapi --conn_name dev_iam --table_owner HR --table_names employees
```

SQLcl connection directories can be used as extracted wallet directories when they contain the files required by the
selected driver mode. A directory containing only `cwallet.sso` is thick-mode only. Prefer the original wallet ZIP
when the connection must work in both thin and thick modes.

For the inspected SI001 `si001_mcp_dev_dba` and `si001_mcp_ro_dba` connections, use the original wallet ZIP where
possible. Their SQLcl connection directories contain `cwallet.sso` but not `ewallet.pem`, so those extracted
directories can only be used in thick mode.

Connection credentials are stored with 256-bit AES encryption, to a local store, at: `<USER_HOME_DIR>/.OraTAPI/dsn_credentials.ini`.  

<b>NOTE: The credential store is non-transportable. If you try to use it on a computer on which it was not maintained, the decryption will fail.</b>


## Sample Generated Table API Packages:

The following historical examples show package layout using the `jobs` table and
`basic` and `logger` templates. They predate enforced version checks. For current
parameter modes and write behaviour, see [the version policy](#the-row_vers_column_name-property);
regenerate from the current templates before using these APIs.

The `jobs` table:
```
Name        Null?    Type                        
----------- -------- --------------------------- 
JOB_ID      NOT NULL VARCHAR2(10)                
JOB_TITLE   NOT NULL VARCHAR2(35)                
MIN_SALARY           NUMBER(6)                   
MAX_SALARY           NUMBER(6)                   
CREATED_BY           VARCHAR2(60)                
CREATED_ON           TIMESTAMP(6) WITH TIME ZONE 
ROW_VERSION          NUMBER          
```

The command:
```
ora_tapi --package_owner aut --conn_name TAPI  --tapi_author cbostock
```
In the above command, the connection name, `TAPI`, has been configured using the OraTAPI `conn_mgr` command.

### Basic Templates Example
Generated package body using the `basic` templates:
```
create or replace package body aut.jobs_tapi
as
--------------------------------------------------------------------------------
--
-- Copyright(C) 2025, Clive`s Software Emporium
-- All Rights Reserved
--
--------------------------------------------------------------------------------
-- Application      :   Human Resources
-- Domain           :   undefined
-- Package          :   jobs_tapi
-- Source file name :   jobs_tapi.sql
-- Purpose          :   Table API (TAPI) for table jobs
--
-- Notes            :   Generated using OraTAPI, by cbostock on 08-Jan-2025.
--                  :   From basic sample
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--< PRIVATE TYPES AND GLOBALS >-------------------------------------------------
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--< PRIVATE METHODS >-----------------------------------------------------------
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--< PUBLIC METHODS >------------------------------------------------------------


   -----------------------------------------------------------------------------
   -- Insert TAPI for: aut.jobs
   -----------------------------------------------------------------------------
   procedure ins
   (
        p_job_id           in       jobs.job_id%type
      , p_row              in out   jobs%rowtype
   )
   is
   begin

      insert into jobs
         (
              job_id
            , job_title
            , min_salary
            , max_salary
         )
      values
         (
              p_row.job_id
            , p_row.job_title
            , p_row.min_salary
            , p_row.max_salary
         )
      returning
              job_id
            , row_version
            into
              p_row.job_id
            , p_row.row_version;

   end ins;

   -----------------------------------------------------------------------------
   -- Insert TAPI for: aut.jobs
   -----------------------------------------------------------------------------
   procedure ins
   (
        p_job_id           in out   jobs.job_id%type
      , p_job_title        in       jobs.job_title%type
      , p_min_salary       in       jobs.min_salary%type
      , p_max_salary       in       jobs.max_salary%type
      , p_row_version         out   jobs.row_version%type
   )
   is
   begin

      insert into jobs
         (
              job_id
            , job_title
            , min_salary
            , max_salary
         )
      values
         (
              p_job_id
            , p_job_title
            , p_min_salary
            , p_max_salary
         )
      returning
              job_id
            , row_version
            into
              p_job_id
            , p_row_version;

   end ins;

   -----------------------------------------------------------------------------
   -- Select TAPI for: aut.jobs
   -----------------------------------------------------------------------------
   procedure get
   (
        p_job_id           in       jobs.job_id%type
      , p_row                 out   jobs%rowtype
   )
   is
   begin

      select
           job_id
         , job_title
         , min_salary
         , max_salary
         , created_by
         , created_on
         , row_version
        into
           p_row.job_id
         , p_row.job_title
         , p_row.min_salary
         , p_row.max_salary
         , p_row.created_by
         , p_row.created_on
         , p_row.row_version
       from jobs
      where
            job_id = p_job_id;

   end get;

   -----------------------------------------------------------------------------
   -- Select TAPI for: aut.jobs
   -----------------------------------------------------------------------------
   procedure get
   (
        p_job_id           in out   jobs.job_id%type
      , p_job_title           out   jobs.job_title%type
      , p_min_salary          out   jobs.min_salary%type
      , p_max_salary          out   jobs.max_salary%type
      , p_created_by          out   jobs.created_by%type
      , p_created_on          out   jobs.created_on%type
      , p_row_version         out   jobs.row_version%type
   )
   is
   begin

      select
           job_id
         , job_title
         , min_salary
         , max_salary
         , created_by
         , created_on
         , row_version
        into
           p_job_id
         , p_job_title
         , p_min_salary
         , p_max_salary
         , p_created_by
         , p_created_on
         , p_row_version
       from jobs
      where
            job_id = p_job_id;

   end get;

   -----------------------------------------------------------------------------
   -- Update TAPI for: aut.jobs
   -----------------------------------------------------------------------------
   procedure upd
   (
        p_job_id           in       jobs.job_id%type
      , p_row              in out   jobs%rowtype
   )
   is
   begin

      update jobs
      set
           job_title                      = p_row.job_title
         , min_salary                     = p_row.min_salary
         , max_salary                     = p_row.max_salary
         , created_by                     = p_row.created_by
         , created_on                     = p_row.created_on
      where
            job_id = p_job_id
      returning
              job_id
            , row_version
            into
              p_row.job_id
            , p_row.row_version;

   end upd;


   -----------------------------------------------------------------------------
   -- Update TAPI for: aut.jobs
   -----------------------------------------------------------------------------
   procedure upd
   (
        p_job_id           in out   jobs.job_id%type
      , p_job_title        in       jobs.job_title%type
      , p_min_salary       in       jobs.min_salary%type
      , p_max_salary       in       jobs.max_salary%type
      , p_row_version         out   jobs.row_version%type
   )
   is
   begin

      update jobs
      set
           job_title                      = p_job_title
         , min_salary                     = p_min_salary
         , max_salary                     = p_max_salary
      where
            job_id = p_job_id
      returning
              job_id
            , row_version
            into
              p_job_id
            , p_row_version;

   end upd;


   -----------------------------------------------------------------------------
   -- Delete TAPI for: aut.jobs
   -----------------------------------------------------------------------------
   procedure del
   (
        p_job_id           in out   jobs.job_id%type
      , p_row_version         out   jobs.row_version%type
   )
   is

   begin

        delete
          from jobs
         where
               job_id = p_job_id
      returning
              job_id
            , row_version
            into
              p_job_id
            , p_row_version;

--          if sql%rowcount = 0
--          then
--             raise NO_DATA_FOUND;
--          end if;

   end del;
end jobs_tapi;
/
```
### Logger Templates Example
Generated package body using the `logger` templates:
```sql
create or replace package body aut.jobs_tapi
as
--------------------------------------------------------------------------------
--
-- Copyright(C) 2025, Clive`s Software Emporium
-- All Rights Reserved
--
--------------------------------------------------------------------------------
-- Application      :   Human Resources
-- Domain           :   undefined
-- Package          :   jobs_tapi
-- Source file name :   jobs_tapi.sql
-- Purpose          :   Table API (TAPI) for table jobs
--
-- HAS COMMITS      :   NO
-- HAS ROLLBACKS    :   NO
--
-- Notes            :   Generated by cbostock on 08-Jan-2025
--                  :   From Liquibase/Logger sample
--  
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--< PUBLIC TYPES AND GLOBALS >--------------------------------------------------

   gc_unit_prefix       constant varchar(64) := lower($$pls_unit) || '.';

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
--< PUBLIC METHODS >------------------------------------------------------------


   -----------------------------------------------------------------------------
   -- Insert TAPI for: aut.jobs
   -----------------------------------------------------------------------------
   procedure ins
   (
        p_job_id           in       jobs.job_id%type
      , p_row              in out   jobs%rowtype
   )
   is

      l_scope           logger_user.logger_logs.scope%type    := gc_unit_prefix || 'del';
      l_params          logger_user.logger.tab_param;

   begin

      -- Parameter logging is filtered by datatype and PI rules.
      logger_user.logger.append_param(l_params, '* p_row.job_id', p_row.job_id);
      logger_user.logger.append_param(l_params, '  p_row.job_title', p_row.job_title);
      logger_user.logger.append_param(l_params, '  p_row.min_salary', p_row.min_salary);
      logger_user.logger.append_param(l_params, '  p_row.max_salary', p_row.max_salary);

      logger.log('START', l_scope, null, l_params);

      insert into jobs
         (
              job_id
            , job_title
            , min_salary
            , max_salary
         )
      values
         (
              p_row.job_id
            , p_row.job_title
            , p_row.min_salary
            , p_row.max_salary
         )
      returning
              job_id
            , row_version
            into
              p_row.job_id
            , p_row.row_version;

      logger.log('END', l_scope);

   exception
      when others then
         logger_user.logger.log_error('Unhandled exception ', l_scope, null, l_params);
         raise;

   end ins;


   -----------------------------------------------------------------------------
   -- Insert TAPI for: aut.jobs
   -----------------------------------------------------------------------------
   procedure ins
   (
        p_job_id           in out   jobs.job_id%type
      , p_job_title        in       jobs.job_title%type
      , p_min_salary       in       jobs.min_salary%type
      , p_max_salary       in       jobs.max_salary%type
      , p_row_version         out   jobs.row_version%type
   )
   is

      l_scope           logger_user.logger_logs.scope%type    := gc_unit_prefix || 'del';
      l_params          logger_user.logger.tab_param;

   begin

      -- Parameter logging is filtered by datatype and PI rules.
      logger_user.logger.append_param(l_params, '* p_job_id', p_job_id);
      logger_user.logger.append_param(l_params, '  p_job_title', p_job_title);
      logger_user.logger.append_param(l_params, '  p_min_salary', p_min_salary);
      logger_user.logger.append_param(l_params, '  p_max_salary', p_max_salary);

      logger.log('START', l_scope, null, l_params);

      insert into jobs
         (
              job_id
            , job_title
            , min_salary
            , max_salary
         )
      values
         (
              p_job_id
            , p_job_title
            , p_min_salary
            , p_max_salary
         )
      returning
              job_id
            , row_version
            into
              p_job_id
            , p_row_version;

      logger.log('END', l_scope);

   exception
      when others then
         logger_user.logger.log_error('Unhandled exception ', l_scope, null, l_params);
         raise;

   end ins;


   -----------------------------------------------------------------------------
   -- Select TAPI for: aut.jobs
   -----------------------------------------------------------------------------
   procedure get
   (
        p_job_id           in       jobs.job_id%type
      , p_row                 out   jobs%rowtype
   )
   is

      l_scope           logger_user.logger_logs.scope%type    := gc_unit_prefix || 'del';
      l_params          logger_user.logger.tab_param;

   begin

      -- Parameter logging is filtered by datatype and PI rules.
      logger_user.logger.append_param(l_params, '* p_row.job_id', p_row.job_id);

      logger.log('START', l_scope, null, l_params);

      select
           job_id
         , job_title
         , min_salary
         , max_salary
         , created_by
         , created_on
         , row_version
        into
           p_row.job_id
         , p_row.job_title
         , p_row.min_salary
         , p_row.max_salary
         , p_row.created_by
         , p_row.created_on
         , p_row.row_version
       from jobs
      where
            job_id = p_job_id;

      logger.log('END', l_scope);

   exception
      when others then
         logger_user.logger.log_error('Unhandled exception ', l_scope, null, l_params);
         raise;

   end get;


   -----------------------------------------------------------------------------
   -- Select TAPI for: aut.jobs
   -----------------------------------------------------------------------------
   procedure get
   (
        p_job_id           in out   jobs.job_id%type
      , p_job_title           out   jobs.job_title%type
      , p_min_salary          out   jobs.min_salary%type
      , p_max_salary          out   jobs.max_salary%type
      , p_created_by          out   jobs.created_by%type
      , p_created_on          out   jobs.created_on%type
      , p_row_version         out   jobs.row_version%type
   )
   is

      l_scope           logger_user.logger_logs.scope%type    := gc_unit_prefix || 'del';
      l_params          logger_user.logger.tab_param;

   begin

      -- Parameter logging is filtered by datatype and PI rules.
      logger_user.logger.append_param(l_params, '* p_job_id', p_job_id);

      logger.log('START', l_scope, null, l_params);

      select
           job_id
         , job_title
         , min_salary
         , max_salary
         , created_by
         , created_on
         , row_version
        into
           p_job_id
         , p_job_title
         , p_min_salary
         , p_max_salary
         , p_created_by
         , p_created_on
         , p_row_version
       from jobs
      where
            job_id = p_job_id;

      logger.log('END', l_scope);

   exception
      when others then
         logger_user.logger.log_error('Unhandled exception ', l_scope, null, l_params);
         raise;

   end get;


   -----------------------------------------------------------------------------
   -- Update TAPI for: aut.jobs
   -----------------------------------------------------------------------------
   procedure upd
   (
        p_job_id           in       jobs.job_id%type
      , p_row              in out   jobs%rowtype
   )
   is

      l_scope           logger_user.logger_logs.scope%type    := gc_unit_prefix || 'del';
      l_params          logger_user.logger.tab_param;

   begin

      -- Parameter logging is filtered by datatype and PI rules.
      logger_user.logger.append_param(l_params, '* p_row.job_id', p_row.job_id);
      logger_user.logger.append_param(l_params, '  p_row.job_title', p_row.job_title);
      logger_user.logger.append_param(l_params, '  p_row.min_salary', p_row.min_salary);
      logger_user.logger.append_param(l_params, '  p_row.max_salary', p_row.max_salary);

      logger.log('START', l_scope, null, l_params);

      update jobs
      set
           job_title                      = p_row.job_title
         , min_salary                     = p_row.min_salary
         , max_salary                     = p_row.max_salary
         , created_by                     = p_row.created_by
         , created_on                     = p_row.created_on
      where
            job_id = p_job_id
      returning
              job_id
            , row_version
            into
              p_row.job_id
            , p_row.row_version;

      logger.log('END', l_scope);

   exception
      when others then
         logger_user.logger.log_error('Unhandled exception ', l_scope, null, l_params);
         raise;

   end upd;


   -----------------------------------------------------------------------------
   -- Update TAPI for: aut.jobs
   -----------------------------------------------------------------------------
   procedure upd
   (
        p_job_id           in out   jobs.job_id%type
      , p_job_title        in       jobs.job_title%type
      , p_min_salary       in       jobs.min_salary%type
      , p_max_salary       in       jobs.max_salary%type
      , p_row_version         out   jobs.row_version%type
   )
   is

      l_scope           logger_user.logger_logs.scope%type    := gc_unit_prefix || 'del';
      l_params          logger_user.logger.tab_param;

   begin

      -- Parameter logging is filtered by datatype and PI rules.
      logger_user.logger.append_param(l_params, '* p_job_id', p_job_id);
      logger_user.logger.append_param(l_params, '  p_job_title', p_job_title);
      logger_user.logger.append_param(l_params, '  p_min_salary', p_min_salary);
      logger_user.logger.append_param(l_params, '  p_max_salary', p_max_salary);

      logger.log('START', l_scope, null, l_params);

      update jobs
      set
           job_title                      = p_job_title
         , min_salary                     = p_min_salary
         , max_salary                     = p_max_salary
      where
            job_id = p_job_id
      returning
              job_id
            , row_version
            into
              p_job_id
            , p_row_version;

      logger.log('END', l_scope);

   exception
      when others then
         logger_user.logger.log_error('Unhandled exception ', l_scope, null, l_params);
         raise;

   end upd;


   -----------------------------------------------------------------------------
   -- Delete TAPI for: aut.jobs
   -----------------------------------------------------------------------------
   procedure del
   (
        p_job_id           in out   jobs.job_id%type
      , p_row_version         out   jobs.row_version%type
   )
   is

      l_scope           logger_user.logger_logs.scope%type    := gc_unit_prefix || 'del';
      l_params          logger_user.logger.tab_param;

   begin

      -- Parameter logging is filtered by datatype and PI rules.
      logger_user.logger.append_param(l_params, '* p_job_id', p_job_id);

      logger.log('START', l_scope, null, l_params);

        delete
          from jobs
         where
               job_id = p_job_id
      returning
              job_id
            , row_version
            into
              p_job_id
            , p_row_version;

      logger.log('END', l_scope);

   exception
      when others then
         logger_user.logger.log_error('Unhandled exception ', l_scope, null, l_params);
         raise;

   end del;

end jobs_tapi;
/
```

---
## Licensing

This project is licensed under the MIT License (see [LICENSE](LICENSE)).

An additional internal-use license has been granted to Oracle Corporation.
See [ORACLE_INTERNAL_LICENSE.txt](ORACLE_INTERNAL_LICENSE.txt) for details.

