OraTAPI - Oracle Table API Generator

Version 3.0.1

OraTAPI - Oracle Table API Generator

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, 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 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.

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:

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:

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

On Windows PowerShell:

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

For development maintenance tasks:

./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.

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

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.

<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:

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:

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:

--profiles-home PATH for the current command.
ORATAPI_PROFILES_HOME from the environment.
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:

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

$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:

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:

Create and activate a virtual environment.

macOS / Linux
```
python3 -m venv .venv
source .venv/bin/activate
```
Windows 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:
```
py -3.11 -m venv .venv
.\.venv\Scripts\Activate.ps1
```
Install OraTAPI from a built wheel or from PyPI when published.

From a local release artefact:
```
pip install dist/oratapi-<x.y.z>-py3-none-any.whl
```
From PyPI:
```
pip install oratapi
```
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.
```
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 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:

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

For Windows 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.

# 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:

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.

# 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:

. $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:

Clone the repository and open it in your editor or IDE.
Create the in-project virtual environment with Poetry:
```
poetry config virtualenvs.in-project true --local
poetry install --with dev --sync
```
This creates .venv in the project root.
In PyCharm, set the interpreter to:
```
<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.

Here we are configuring for Liquibase:

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:

quick_config -t <template-category>

quick_config -t <template-category>

Valid template categories are:

basic - No Liquibase directives or logging
liquibase - Generated code includes Liquibase directives
logger - Generated PL/SQL includes logger logging calls for parameter values and related diagnostics
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:

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:

For the current run only, pass:

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

Windows PowerShell:

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

For all profiles on the current machine, set the ORACLE_IC_HOME environment variable to the Instant Client directory.
For one OraTAPI profile only, place the Instant Client under:
```
~/OraTAPIProfiles/configs/<active-profile>/oracle_client
```
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:
```
/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:

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

Windows 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:

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) 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:

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

Windows 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:

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

Windows 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 for the recommended workflow. There are also column expression templates. These are discussed in the 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 (e.g. [<api_controls]). 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. 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:

~/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:

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), 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.

<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.

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:

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:

profile_mgr --split-utplsql agr_atp
profile_mgr --split-utplsql agr_atp --write

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

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:

%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:

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:

[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:

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:

[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:

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:

The production package is cfg_json_mappings_tapi, and [ut_controls] tapi_owner supplies cfg_api. These supply {package_name} and {production_owner} respectively.
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.
That path sits beneath [suitepath-map:common_area] source_root, so this map matches. Its product = agriculture supplies {product}.
[suitepath-group:configuration_packages] refers to that map through area = common_area and accepts the package prefix cfg_. Its group = shared supplies {group}.
The package suffix _tapi matches [package-categories], supplying {category} = tapi.
The two suite templates are expanded to produce:

--%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.

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, also included in wheel package data. For an installed wheel, this read-only command locates the supplied example:

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.

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:

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.
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.
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.
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:

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:

<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:

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:

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:

--%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:

-- Release: %release_label%

Then supply its value with:

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

ora_tapi --table_owner HR --table_names employees departments --conn_name dev_db --tapi_author cbostock

Using the terse flags, this is equivalent to:

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:

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 above.

More Advanced Example

Here we want to override the default target schemas for the packages, views, and triggers:

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:

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. 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. 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 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.
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.
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:

[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 trigger, 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:

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. The active profile must already have utPLSQL generation enabled.

An opt-in executable test pilot 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:

[behaviour]
enable_tapis_when_ut_enabled = true

In its resources/config/utplsql.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:

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:

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:

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:

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] subsection.

Template Substitution Strings

Any properties from the OraTAPI.ini file may be interpolated into the templates.

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:

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:

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:

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:

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.

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.

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; 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:

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).

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

Name		Name	Last commit message	Last commit date
.codex		.codex
assets/images		assets/images
bin		bin
resources		resources
src		src
staging		staging
tests		tests
utils		utils
.bumpversion.cfg		.bumpversion.cfg
.gitignore		.gitignore
AGENTS.md		AGENTS.md
LICENSE		LICENSE
ORACLE_INTERNAL_LICENSE.txt		ORACLE_INTERNAL_LICENSE.txt
README.md		README.md
deploy-migration-plan.md		deploy-migration-plan.md
install_oratapi.py		install_oratapi.py
poetry.lock		poetry.lock
poetry.toml		poetry.toml
pyproject.toml		pyproject.toml
requirements.txt		requirements.txt
setup.ps1		setup.ps1
setup.sh		setup.sh

Latest commit

History

Folders and files

Repository files navigation

OraTAPI - Oracle Table API Generator

About OraTAPI

Features & Limitations

Features

Limitations

Documentation

Preinstallation

Preparing the Environment

Development Environment

Familiarisation with the Layout

Installation

Recommended directory layout

Install into the recommended location

Personal profile locations and migration

Manual wheel installation

Legacy: extracted source distribution install

Optional: Create A Personal Launcher Script

Optional: Use Shell Aliases On macOS Or Linux

Optional: Use PowerShell Functions On Windows

Development Checkout

Post Installation

Using Oracle Instant Client

Performing Upgrades

Migrations

In-situ upgrades

The check_pypi_for_updates Property

The check_github_for_updates Property (Deprecated)

The Primary Components

Modifying Templates

Command-line Tools

Usage

Repository-managed profiles

Profile bundles with oratapi-orc

Suite settings and substitution formats

Worked example: cfg_core.cfg_json_mappings

Executable test configuration

Template overrides for one run

Examples

Basic Example

More Advanced Example

Explicitly Specifying Credentials

Full Command-Line Arguments:

Output Structure

Configuration Settings

Control Files

The OraTAPI.ini File

Here we cover the various sections and properties.

[project]

[copyright]

[behaviour]

[formatting]

[file_controls]

[api_controls]

[logger]

[schemas]

[misc]

[suite]

[ut_controls]

[console]

Example configuration file:

Fine-Grained File Controls

Controlling File Updates

PI (Personal Information) Columns & Logging

Auto Column Management

What are Auto-managed Columns?

Configuring the Column Management Method

The col_auto_maintain_method Property

Maintained by Trigger

Maintained by Column Expression

The auto_maintained_cols Property

The row_vers_column_name Property

utPLSQL Support

One command for production code and executable tests

Understanding UT scenario counts

Reading a FIXTURE REQUIRED section

Generate TAPIs now and executable tests later