### Install Ora2Pg from Source

Source: https://context7.com/darold/ora2pg/llms.txt

Build and install Ora2Pg from its source tarball. Optionally, specify a custom installation prefix and set the PERL5LIB environment variable.

```bash
# Unpack source tarball
tar xjf ora2pg-25.0.tar.bz2
cd ora2pg-25.0/

# Build and install (installs Ora2Pg.pm, ora2pg binary, ora2pg.conf)
perl Makefile.PL
make && make install

# Install to a custom prefix
perl Makefile.PL PREFIX=/opt/ora2pg
make && make install
export PERL5LIB=/opt/ora2pg
```

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

### Ora2Pg CLI - Basic Usage Examples

Source: https://context7.com/darold/ora2pg/llms.txt

Examples demonstrating the conversion of Oracle SQL queries, PL/SQL scripts, and PL/SQL functions using the ora2pg CLI.

```APIDOC
## ora2pg CLI - Basic Usage Examples

Convert Oracle SQL queries from a file (useful for application SQL)
```bash
ora2pg -t QUERY -p -i app_queries.sql -o pg_queries.sql -c /etc/ora2pg/ora2pg.conf
```

Process an entire SQLPlus script as a unit (SCRIPT action)
```bash
ora2pg -t SCRIPT -p -i ddl_script.sql -o pg_ddl_script.sql -c /etc/ora2pg/ora2pg.conf
```

Migration cost assessment of PL/SQL code in a file
```bash
ora2pg -t FUNCTION -p -i oracle_functions.sql --estimate_cost -c /etc/ora2pg/ora2pg.conf
```
```

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

### Ora2Pg Configuration File Example

Source: https://context7.com/darold/ora2pg/llms.txt

Example configuration for ora2pg.conf, specifying connection parameters, schema, export type, PostgreSQL version, and output settings. Includes alternative settings for MySQL and SQL Server.

```ini
#--- Oracle connection ---
ORACLE_HOME     /usr/lib/oracle/12.2/client64
ORACLE_DSN      dbi:Oracle:host=mydb.mydom.fr;sid=MYDB;port=1521
ORACLE_USER     system
ORACLE_PWD      manager

#--- Schema selection ---
SCHEMA          HR
EXPORT_SCHEMA   1

#--- Export type ---
TYPE            TABLE

#--- PostgreSQL target version ---
PG_VERSION      15

#--- Output ---
OUTPUT          output.sql
OUTPUT_DIR      /var/tmp/migration

#--- Data export tuning ---
DATA_LIMIT      10000
JOBS            4

#--- PL/SQL conversion ---
PLSQL_PGSQL     1

#--- MySQL migration (alternative) ---
# ORACLE_DSN    dbi:mysql:host=192.168.1.10;database=sakila;port=3306

#--- SQL Server migration (alternative) ---
# ORACLE_DSN    dbi:ODBC:driver=msodbcsql18;server=mydb.database.windows.net;database=testdb;TrustServerCertificate=yes
```

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

### Install Required Perl Modules

Source: https://context7.com/darold/ora2pg/llms.txt

Install necessary Perl modules for database connectivity. DBD::Oracle is required for Oracle migrations, DBD::MySQL for MySQL/MariaDB, and ODBC drivers for SQL Server.

```bash
# Install required Perl module for Oracle
export LD_LIBRARY_PATH=/usr/lib/oracle/12.2/client64/lib
export ORACLE_HOME=/usr/lib/oracle/12.2/client64
perl -MCPAN -e 'install DBD::Oracle'

# For MySQL migration
perl -MCPAN -e 'install DBD::MySQL'

# For SQL Server migration
sudo apt install unixodbc libdbd-odbc-perl
```

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

### Kettle with Parallel Readers and Writers

Source: https://context7.com/darold/ora2pg/llms.txt

Configure Kettle ETL generation with parallel Oracle readers (-J) and PostgreSQL writers (-j). This example uses 4 readers and 12 writers.

```bash
ora2pg -t KETTLE -J 4 -j 12 -c ora2pg.conf \
  -a EMPLOYEES -o load_employees.sh
```

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

### Ora2Pg CLI - FDW / Kettle Export

Source: https://context7.com/darold/ora2pg/llms.txt

Examples for exporting Oracle schema and data using Foreign Data Wrappers (FDW) or generating Kettle ETL templates.

```APIDOC
## `ora2pg` CLI — FDW / Kettle Export

Export schema and data using Foreign Data Wrappers or generate Kettle ETL templates.

```bash
# Export Oracle tables as oracle_fdw foreign tables
ora2pg -t FDW -c ora2pg.conf -o foreign_tables.sql
# Output:
# CREATE FOREIGN TABLE oratab (
#   id integer NOT NULL,
#   text character varying(30)
# ) SERVER oradb OPTIONS (table 'ORATAB');

# Generate Kettle XML transformation files (12 parallel insert threads)
ora2pg -t KETTLE -j 12 -c ora2pg.conf -a EMPLOYEES -o load_employees.sh

# Kettle with 4 parallel Oracle readers + 12 PostgreSQL writers
ora2pg -t KETTLE -J 4 -j 12 -c ora2pg.conf \
  -a EMPLOYEES -o load_employees.sh
# Creates: HR.EMPLOYEES.ktr
# load_employees.sh contains:
# JAVAMAXMEM=4096 ./pan.sh -file $KETTLE_TEMPLATE_PATH/HR.EMPLOYEES.ktr -level Detailed

# Dispatch SQL index creation over 4 parallel PostgreSQL connections
ora2pg -t LOAD -c ora2pg.conf \
  -i schema/tables/INDEXES_table.sql -j 4 \
  --pg_dsn "dbi:Pg:dbname=mydb;host=pghost"
```
```

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

### Control Column Export Programmatically

Source: https://context7.com/darold/ora2pg/llms.txt

Use modify_struct() or exclude_columns() methods on an Ora2Pg object to programmatically control which columns are exported per table. This example shows instantiation for COPY type.

```perl
use Ora2Pg;

my $ora2pg = new Ora2Pg(
    config => '/etc/ora2pg/ora2pg.conf',
    type   => 'COPY',
);
```

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

### Initialize ora2pg Project

Source: https://context7.com/darold/ora2pg/llms.txt

Scaffolds a new ora2pg migration project directory tree. This includes configuration files and export/import scripts. After initialization, edit the config file and run the generated scripts.

```bash
ora2pg --project_base /app/migration/ --init_project myproject
```

```bash
cd /app/migration/myproject
# Set ORACLE_DSN, ORACLE_USER, ORACLE_PWD in config/ora2pg.conf
./export_schema.sh
# Then load into PostgreSQL:
./import_all.sh
```

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

### List Oracle Schemas

Source: https://context7.com/darold/ora2pg/llms.txt

Use this command to list all available schemas in the Oracle database. Requires a configuration file with Oracle connection details.

```bash
ora2pg -t SHOW_SCHEMA -c ora2pg.conf
```

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

### Ora2Pg CLI: Basic Commands

Source: https://context7.com/darold/ora2pg/llms.txt

Basic Ora2Pg command-line interface usage for showing version and testing database connections. The `-v` flag shows the version, and `-t SHOW_VERSION` tests the connection using a specified configuration file.

```bash
# Show version
ora2pg -v
# => Ora2Pg v25.0

# Test Oracle connection (verify DSN/credentials)
ora2pg -t SHOW_VERSION -c /etc/ora2pg/ora2pg.conf
```

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

### Customizing BFILE Handling

Source: https://context7.com/darold/ora2pg/llms.txt

Configures BFILE data types to be exported as text paths instead of bytea. Useful for managing large binary files.

```ini
# Change BFILE handling: export as path text rather than bytea
DATA_TYPE   BFILE:TEXT
```

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

### Dispatch SQL Index Creation over Parallel Connections

Source: https://context7.com/darold/ora2pg/llms.txt

Use the LOAD type to dispatch SQL index creation over multiple parallel PostgreSQL connections. Specify the input file for indexes and the number of jobs.

```bash
ora2pg -t LOAD -c ora2pg.conf \
  -i schema/tables/INDEXES_table.sql -j 4 \
  --pg_dsn "dbi:Pg:dbname=mydb;host=pghost"
```

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

### Instantiate Ora2Pg Object with Config File

Source: https://context7.com/darold/ora2pg/llms.txt

Create a new Ora2Pg object programmatically using the 'new' method. Pass configuration directives as lowercase arguments, including the config file path and export type.

```perl
use Ora2Pg;

# Minimal usage with a config file
my $ora2pg = new Ora2Pg(
    config => '/etc/ora2pg/ora2pg.conf',
    type   => 'TABLE',
);
$ora2pg->export_schema('tables.sql');
```

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

### Show Oracle to PostgreSQL Type Mappings

Source: https://context7.com/darold/ora2pg/llms.txt

Displays all columns with their Oracle to PostgreSQL type mappings. You can specify a table name with the -a option to filter results.

```bash
ora2pg -t SHOW_COLUMN -c ora2pg.conf
```

```bash
ora2pg -t SHOW_COLUMN -c ora2pg.conf -a EMPLOYEES
```

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

### Process SQLPlus Scripts as a Unit

Source: https://context7.com/darold/ora2pg/llms.txt

Utilize the SCRIPT action to process an entire SQLPlus script for migration. Specify input and output files, along with the configuration.

```bash
ora2pg -t SCRIPT -p -i ddl_script.sql -o pg_ddl_script.sql \
  -c /etc/ora2pg/ora2pg.conf
```

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

### Convert Oracle SQL Queries to PostgreSQL Format

Source: https://context7.com/darold/ora2pg/llms.txt

Use the QUERY type to convert application SQL queries from a file. Ensure the configuration file is specified.

```bash
ora2pg -t QUERY -p -i app_queries.sql -o pg_queries.sql \
  -c /etc/ora2pg/ora2pg.conf
```

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

### Full Ora2Pg Object Instantiation with Overrides

Source: https://context7.com/darold/ora2pg/llms.txt

Instantiate Ora2Pg with numerous options to override config file settings, including schema, output directory, data limits, and exclusion patterns.

```perl
my $ora2pg = new Ora2Pg(
    config        => '/etc/ora2pg/ora2pg.conf',
    type          => 'TABLE,SEQUENCE',
    debug         => 0,
    schema        => 'HR',
    output        => 'hr_schema.sql',
    output_dir    => '/var/tmp/migration',
    plsql_pgsql   => 1,
    data_limit    => 5000,
    thread_count  => 4,
    exclude       => 'TMP_.*',
    allow         => 'EMPLOYEES DEPARTMENTS JOBS LOCATIONS',
);
$ora2pg->export_schema();
```

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

### Export Data with SCN for Point-in-Time Consistency

Source: https://context7.com/darold/ora2pg/llms.txt

Use the --scn option with the COPY type to export data at a specific System Change Number for point-in-time consistency. A configuration file is required.

```bash
ora2pg -t COPY -c ora2pg.conf --scn 16605281 -o data_scn.sql
```

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

### Batch Database Assessment with ora2pg_scanner CLI

Source: https://context7.com/darold/ora2pg/llms.txt

Scan multiple databases from a CSV file and generate assessment reports. Supports HTML, JSON output, connection testing, and custom configurations.

```bash
# Create the CSV input file
cat > databases.csv << 'EOF'
"type","schema/database","dsn","user","password"
"ORACLE","HR","dbi:Oracle:host=oracle1.mycompany.com;sid=PROD;port=1521","system","manager"
"ORACLE","SCOTT","dbi:Oracle:host=oracle2.mycompany.com;sid=DEV;port=1521","system","devpass"
"MYSQL","sakila","dbi:mysql:host=192.168.1.10;database=sakila;port=3306","root","secret"
"MSSQL","HR","dbi:ODBC:driver=msodbcsql18;server=sqlsrv.company.com;database=HRdb","sa","Passw0rd"
EOF

# Scan all databases and generate HTML reports in ./output/
ora2pg_scanner -l databases.csv -o /var/tmp/assessment_reports

# Use JSON output format instead of HTML
ora2pg_scanner -l databases.csv -o /var/tmp/reports -f json

# Test all connections first without running reports
ora2pg_scanner -l databases.csv -t

# Custom cost unit (10 min per unit for first-time migrations)
ora2pg_scanner -l databases.csv -o /var/tmp/reports -u 10

# Use a custom ora2pg config as base
ora2pg_scanner -l databases.csv -o /var/tmp/reports -c /etc/ora2pg/ora2pg.conf

# Scan all schemas in an Oracle instance (leave schema field empty)
cat > all_schemas.csv << 'EOF'
"type","schema/database","dsn","user","password"
"ORACLE","","dbi:Oracle:host=oracle1.mycompany.com;sid=PROD;port=1521","system","manager"
EOF
ora2pg_scanner -l all_schemas.csv -o /var/tmp/all_schema_reports

# Output for each database:
# /var/tmp/assessment_reports/HR_oracle1_PROD.html
# /var/tmp/assessment_reports/SCOTT_oracle2_DEV.html
# /var/tmp/assessment_reports/sakila_192.168.1.10.html
# /var/tmp/assessment_reports/assessment_summary.csv  (one line per DB)
```

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

### Execute Ora2Pg Export to Gzip-Compressed File

Source: https://context7.com/darold/ora2pg/llms.txt

Provide a filename with a .gz extension to the export_schema() method to automatically create a gzip-compressed output file.

```perl
# Write to a gzip-compressed file
$ora2pg->export_schema('my_functions.sql.gz');
```

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

### List Tables with Row Counts

Source: https://context7.com/darold/ora2pg/llms.txt

Lists all tables in the Oracle database along with their row counts. For accurate counts, use the --count_rows option, which may be slower.

```bash
ora2pg -t SHOW_TABLE -c ora2pg.conf
```

```bash
ora2pg -t SHOW_TABLE -c ora2pg.conf --count_rows
```

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

### Export Data with Flashback Timestamp

Source: https://context7.com/darold/ora2pg/llms.txt

Export data using a flashback timestamp by providing a TO_TIMESTAMP string to the --scn option. A configuration file is necessary.

```bash
ora2pg -t COPY -c ora2pg.conf \
  --scn "TO_TIMESTAMP('2024-01-15 00:00:00','YYYY-MM-DD HH:MI:SS')" \
  -o data_flashback.sql
```

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

### Validate Migration: Full Diff

Source: https://context7.com/darold/ora2pg/llms.txt

Performs a full difference check between Oracle and PostgreSQL databases, including object and row counts. Redirect output to a file for review.

```bash
ora2pg -t TEST -c ora2pg.conf \
  --pg_dsn "dbi:Pg:dbname=mydb;host=pghost;port=5432" \
  --pg_user pgadmin --pg_pwd pgpassword \
  > migration_diff.txt
```

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

### CDC: Export with SCN Registration per Table

Source: https://context7.com/darold/ora2pg/llms.txt

Enable Change Data Capture (CDC) by using the --cdc_ready option with the COPY type. SCNs will be logged to TABLES_SCN.log.

```bash
ora2pg -t COPY -c ora2pg.conf --cdc_ready -o data.sql
```

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

### Export Data with Compressed Output

Source: https://context7.com/darold/ora2pg/llms.txt

This command exports data with compressed output. You can specify the output file name with a .gz extension or configure it in ora2pg.conf.

```bash
ora2pg -t COPY -c ora2pg.conf -o data.sql.gz
```

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

### Ora2Pg Perl Module API - new()

Source: https://context7.com/darold/ora2pg/llms.txt

Instantiate the Ora2Pg object programmatically, passing configuration directives as constructor arguments.

```APIDOC
## `Ora2Pg` Perl Module API — `new()`

Instantiate the Ora2Pg object programmatically. All `ora2pg.conf` directives can be passed in lowercase as constructor arguments.

```perl
use Ora2Pg;

# Minimal usage with a config file
my $ora2pg = new Ora2Pg(
    config => '/etc/ora2pg/ora2pg.conf',
    type   => 'TABLE',
);
$ora2pg->export_schema('tables.sql');

# Full example: TABLE + SEQUENCE export with overrides
my $ora2pg = new Ora2Pg(
    config        => '/etc/ora2pg/ora2pg.conf',
    type          => 'TABLE,SEQUENCE',
    debug         => 0,
    schema        => 'HR',
    output        => 'hr_schema.sql',
    output_dir    => '/var/tmp/migration',
    plsql_pgsql   => 1,
    data_limit    => 5000,
    thread_count  => 4,
    exclude       => 'TMP_.*',
    allow         => 'EMPLOYEES DEPARTMENTS JOBS LOCATIONS',
);
$ora2pg->export_schema();

# MySQL migration via Perl API
my $ora2pg = new Ora2Pg(
    config      => '/etc/ora2pg/ora2pg.conf',
    type        => 'TABLE',
    is_mysql    => 1,
    datasource  => 'dbi:mysql:host=192.168.1.10;database=sakila;port=3306',
    user        => 'root',
    password    => 'secret',
    output      => 'mysql_tables.sql',
);
$ora2pg->export_schema('mysql_tables.sql');

# COPY data export directly into PostgreSQL
my $ora2pg = new Ora2Pg(
    config    => '/etc/ora2pg/ora2pg.conf',
    type      => 'COPY',
    pg_dsn    => 'dbi:Pg:dbname=mydb;host=pghost;port=5432',
    pg_user   => 'pgadmin',
    pg_pwd    => 'pgpassword',
    jobs      => 4,
);
$ora2pg->export_schema();
```
```

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

### MySQL Migration Export

Source: https://context7.com/darold/ora2pg/llms.txt

Commands for migrating from MySQL. Use -m for MySQL migration. The first command exports table structures, and the second exports data using the COPY method.

```bash
ora2pg -m -t TABLE -c ora2pg.conf -o tables.sql
```

```bash
ora2pg -m -t COPY  -c ora2pg.conf -o data.sql
```

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

### ora2pg_scanner CLI - Batch Migration Assessment

Source: https://context7.com/darold/ora2pg/llms.txt

Scans multiple databases from a CSV list and generates assessment reports.

```APIDOC
## ora2pg_scanner

### Description
Assesses multiple databases for migration readiness by scanning them based on a provided CSV list and generating reports.

### Usage
`ora2pg_scanner [options] -l <database_list.csv> -o <output_directory>`

### Options
- `-l, --list <file>`: Path to the CSV file containing database connection details.
- `-o, --output <dir>`: Directory where assessment reports will be saved.
- `-f, --format <format>`: Output format for reports (e.g., `html`, `json`). Defaults to HTML.
- `-t, --test-connections`: Test database connections without generating reports.
- `-u, --cost-unit <minutes>`: Custom cost unit in minutes for migration effort estimation.
- `-c, --config <file>`: Path to a custom `ora2pg.conf` file to use as a base for scanning.

### CSV File Format
The CSV file should contain columns: `"type","schema/database","dsn","user","password"`.
```

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

### Show Encoding Settings

Source: https://context7.com/darold/ora2pg/llms.txt

Displays the current encoding settings for the Oracle database. This is useful for identifying potential character set issues during migration.

```bash
ora2pg -t SHOW_ENCODING -c ora2pg.conf
```

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

### Estimate Migration Cost of PL/SQL Code

Source: https://context7.com/darold/ora2pg/llms.txt

Employ the FUNCTION type with the --estimate_cost flag to assess the migration cost of PL/SQL code from a file. A configuration file is required.

```bash
ora2pg -t FUNCTION -p -i oracle_functions.sql --estimate_cost \
  -c /etc/ora2pg/ora2pg.conf
```

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

### Generate Migration Report in Different Formats

Source: https://context7.com/darold/ora2pg/llms.txt

Generates migration assessment reports in HTML, JSON, or CSV formats. Use the -o option to specify the output file name. Multiple formats can be generated simultaneously using --dump_as_file_prefix.

```bash
ora2pg -t SHOW_REPORT -c ora2pg.conf --estimate_cost --dump_as_html -o report.html
```

```bash
ora2pg -t SHOW_REPORT -c ora2pg.conf --estimate_cost --dump_as_json -o report.json
```

```bash
ora2pg -t SHOW_REPORT -c ora2pg.conf --estimate_cost --dump_as_csv -o report.csv
```

```bash
ora2pg -t SHOW_REPORT -c ora2pg.conf --estimate_cost \
  --dump_as_html --dump_as_json --dump_as_file_prefix migration_report
```

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

### SQL Server Migration Export

Source: https://context7.com/darold/ora2pg/llms.txt

Commands for migrating from SQL Server. Use -M for SQL Server migration. This command exports table structures.

```bash
ora2pg -M -t TABLE -c ora2pg.conf -o tables.sql
```

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

### Generate Migration Assessment Report

Source: https://context7.com/darold/ora2pg/llms.txt

Generates a migration assessment report in text format. Use --estimate_cost to include cost estimations, and specify --cost_unit_value for custom units.

```bash
ora2pg -t SHOW_REPORT -c ora2pg.conf
```

```bash
ora2pg -t SHOW_REPORT -c ora2pg.conf --estimate_cost
```

```bash
ora2pg -t SHOW_REPORT -c ora2pg.conf --estimate_cost --cost_unit_value 10
```

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

### Export Data Directly to PostgreSQL

Source: https://context7.com/darold/ora2pg/llms.txt

Use this command to export data directly to a PostgreSQL database, bypassing file generation. Ensure your PostgreSQL connection details are correctly configured.

```bash
ora2pg -t COPY -c ora2pg.conf \
  --pg_dsn "dbi:Pg:dbname=mydb;host=pghost;port=5432" \
  --pg_user pgadmin \
  --pg_pwd pgpassword
```

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

### Ora2Pg CLI - BLOB / Large Object Export

Source: https://context7.com/darold/ora2pg/llms.txt

Demonstrates exporting Oracle BLOB columns as PostgreSQL large objects, including single-pass, two-pass, and SCN-based exports.

```APIDOC
## `ora2pg` CLI — BLOB / Large Object Export

Export Oracle BLOB columns as PostgreSQL large objects instead of bytea.

```bash
# Export BLOBs as large objects (Oid column type)
ora2pg -t INSERT -c ora2pg.conf --blob_to_lo -o data_with_lo.sql

# Two-pass export for BLOBs larger than 1 GB
# Pass 1: export data (sets Oid column to 0, saves BLOB to separate files)
ora2pg -t COPY -c ora2pg.conf --blob_to_lo --lo_import -o data.sql

# Pass 2: run generated lo_import scripts after loading data
export PGDATABASE=mydb
export PGHOST=pghost
./lo_import-MYTABLE.sh

# Export with SCN (System Change Number) for point-in-time consistency
ora2pg -t COPY -c ora2pg.conf --scn 16605281 -o data_scn.sql

# Export with flashback timestamp
ora2pg -t COPY -c ora2pg.conf \
  --scn "TO_TIMESTAMP('2024-01-15 00:00:00','YYYY-MM-DD HH:MI:SS')" \
  -o data_flashback.sql

# CDC: export with SCN registration per table for Change Data Capture
ora2pg -t COPY -c ora2pg.conf --cdc_ready -o data.sql
# SCNs written to TABLES_SCN.log:
# EMPLOYEES:16605281
# DEPARTMENTS:16605290
```
```

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

### Generate Kettle XML Transformation Files

Source: https://context7.com/darold/ora2pg/llms.txt

The KETTLE type generates Kettle XML transformation files. Use the -j option to specify the number of parallel insert threads.

```bash
ora2pg -t KETTLE -j 12 -c ora2pg.conf -a EMPLOYEES -o load_employees.sh
```

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

### Ora2Pg CLI: Schema Export

Source: https://context7.com/darold/ora2pg/llms.txt

Commands to export various schema objects like tables, views, sequences, functions, procedures, packages, triggers, partitions, grants, types, materialized views, and DB links.

```bash
# Export table DDL (tables, indexes, constraints)
ora2pg -t TABLE -c /etc/ora2pg/ora2pg.conf -o tables.sql

# Export specific tables only
ora2pg -t TABLE -c ora2pg.conf -a 'EMPLOYEES DEPARTMENTS' -o schema.sql

# Export views
ora2pg -t VIEW -c ora2pg.conf -o views.sql

# Export sequences
ora2pg -t SEQUENCE -c ora2pg.conf -o sequences.sql

# Export stored procedures and functions with PL/SQL conversion
ora2pg -t FUNCTION -p -c ora2pg.conf -o functions.sql
ora2pg -t PROCEDURE -p -c ora2pg.conf -o procedures.sql

# Export packages
ora2pg -t PACKAGE -p -c ora2pg.conf -o packages.sql

# Export triggers
ora2pg -t TRIGGER -p -c ora2pg.conf -o triggers.sql

# Export partitions
ora2pg -t PARTITION -c ora2pg.conf -o partitions.sql

# Export grants/roles/users
ora2pg -t GRANT -c ora2pg.conf -o grants.sql

# Export types
ora2pg -t TYPE -p -c ora2pg.conf -o types.sql

# Export materialized views
ora2pg -t MVIEW -c ora2pg.conf -o mviews.sql

# Export DB links as FDW
ora2pg -t DBLINK -c ora2pg.conf -o dblinks.sql

# Export synonyms as views
ora2pg -t SYNONYM -c ora2pg.conf -o synonyms.sql
```

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

### Ora2Pg Perl Module API - export_schema()

Source: https://context7.com/darold/ora2pg/llms.txt

Execute the configured export and write output to a file or STDOUT using the export_schema method.

```APIDOC
## `Ora2Pg` Perl Module API — `export_schema()`

Execute the configured export and write output to a file or STDOUT.

```perl
use Ora2Pg;

my $ora2pg = new Ora2Pg(
    config => '/etc/ora2pg/ora2pg.conf',
    type   => 'FUNCTION',
    plsql_pgsql => 1,
    schema => 'HR',
);

# Write to the file specified in config (OUTPUT directive)
$ora2pg->export_schema();

# Write to an explicit filename
$ora2pg->export_schema('my_functions.sql');

# Write to a gzip-compressed file
$ora2pg->export_schema('my_functions.sql.gz');
```
```

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

### Execute Ora2Pg Export to Configured Output

Source: https://context7.com/darold/ora2pg/llms.txt

Call the export_schema() method on an instantiated Ora2Pg object to perform the configured export. If no filename is provided, it uses the OUTPUT directive from the config.

```perl
use Ora2Pg;

my $ora2pg = new Ora2Pg(
    config => '/etc/ora2pg/ora2pg.conf',
    type   => 'FUNCTION',
    plsql_pgsql => 1,
    schema => 'HR',
);

# Write to the file specified in config (OUTPUT directive)
$ora2pg->export_schema();
```

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

### Validate Migration: Data Content

Source: https://context7.com/darold/ora2pg/llms.txt

Compares data content for tables between Oracle and PostgreSQL. By default, it compares the first 10000 rows per table. Requires PostgreSQL connection details.

```bash
ora2pg -t TEST_DATA -c ora2pg.conf \
  --pg_dsn "dbi:Pg:dbname=mydb;host=pghost" \
  --pg_user pgadmin --pg_pwd pgpassword
```

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

### Convert Oracle PL/SQL Package Body to PL/PGSQL

Source: https://context7.com/darold/ora2pg/llms.txt

Converts Oracle PL/SQL package body code from a file to PostgreSQL's PL/PGSQL format without a database connection. Use -i for input and -o for output files.

```bash
ora2pg -t PACKAGE -p -i oracle_packages.sql -o pg_packages.sql \
  -c /etc/ora2pg/ora2pg.conf
```

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

### Export BLOB Columns as PostgreSQL Large Objects

Source: https://context7.com/darold/ora2pg/llms.txt

Use the --blob_to_lo option with the INSERT type to export BLOB columns as PostgreSQL large objects (Oid column type). A configuration file is necessary.

```bash
ora2pg -t INSERT -c ora2pg.conf --blob_to_lo -o data_with_lo.sql
```

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

### Export Oracle Tables as oracle_fdw Foreign Tables

Source: https://context7.com/darold/ora2pg/llms.txt

Use the FDW type to export Oracle tables as oracle_fdw foreign tables. Specify the configuration file and output file.

```bash
ora2pg -t FDW -c ora2pg.conf -o foreign_tables.sql
```

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

### Ora2Pg CLI: Data Export

Source: https://context7.com/darold/ora2pg/llms.txt

Commands for exporting data using either COPY statements (faster bulk load) or INSERT statements. Supports filtering data with WHERE clauses and parallel export.

```bash
# Export data as COPY statements (fastest bulk load)
ora2pg -t COPY -c ora2pg.conf -o data.sql

# Export data as INSERT statements
ora2pg -t INSERT -c ora2pg.conf -o data.sql

# Export data with a WHERE filter for all tables
ora2pg -t COPY -c ora2pg.conf -W "DATE_CREATE > '2023-01-01'" -o data.sql

# Export data from specific tables with per-table WHERE clauses
ora2pg -t COPY -c ora2pg.conf \
  -W "EMPLOYEES[DEPARTMENT_ID=10]" \
  -W "DEPARTMENTS[LOCATION_ID=1700]" \
  -o data.sql

# Parallel data export (4 PostgreSQL writers, 2 Oracle connections)
ora2pg -t COPY -c ora2pg.conf -j 4 -J 2 -o data.sql
```

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

### Apply Row Filters and Pre-Import Deletions with Ora2Pg

Source: https://context7.com/darold/ora2pg/llms.txt

Use set_where_clause and set_delete_clause to filter rows during data export or to delete existing data before import.

```perl
use Ora2Pg;

my $ora2pg = new Ora2Pg(
    config => '/etc/ora2pg/ora2pg.conf',
    type   => 'COPY',
);

# Apply a WHERE clause globally to all tables
$ora2pg->set_where_clause("DATE_CREATE > '2023-01-01'");

# Apply per-table WHERE clauses (overrides global for those tables)
$ora2pg->set_where_clause(
    '',   # no global clause
    'EMPLOYEES'   => "DEPARTMENT_ID = 10",
    'ORDERS'      => "STATUS = 'ACTIVE' AND ORDER_DATE > '2023-01-01'",
);

# Apply a DELETE clause before INSERT/COPY to replace changed data
$ora2pg->set_delete_clause(
    "DATE_MODIFIED > '2023-01-01'",   # global delete filter
    'EMPLOYEES'  => "DEPARTMENT_ID = 10",
);

$ora2pg->export_schema('filtered_data.sql');
```

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

### MySQL Migration via Perl API

Source: https://context7.com/darold/ora2pg/llms.txt

Configure Ora2Pg for MySQL migration by setting 'is_mysql' to 1 and providing the MySQL datasource connection details.

```perl
# MySQL migration via Perl API
my $ora2pg = new Ora2Pg(
    config      => '/etc/ora2pg/ora2pg.conf',
    type        => 'TABLE',
    is_mysql    => 1,
    datasource  => 'dbi:mysql:host=192.168.1.10;database=sakila;port=3306',
    user        => 'root',
    password    => 'secret',
    output      => 'mysql_tables.sql',
);
$ora2pg->export_schema('mysql_tables.sql');
```

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

### Direct PostgreSQL COPY Data Export via Perl API

Source: https://context7.com/darold/ora2pg/llms.txt

Configure Ora2Pg to export data directly into PostgreSQL using the COPY command. Provide PostgreSQL connection details via 'pg_dsn' and related parameters.

```perl
# COPY data export directly into PostgreSQL
my $ora2pg = new Ora2Pg(
    config    => '/etc/ora2pg/ora2pg.conf',
    type      => 'COPY',
    pg_dsn    => 'dbi:Pg:dbname=mydb;host=pghost;port=5432',
    pg_user   => 'pgadmin',
    pg_pwd    => 'pgpassword',
    jobs      => 4,
);
$ora2pg->export_schema();
```

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

### Customizing Data Type Mappings in ora2pg.conf

Source: https://context7.com/darold/ora2pg/llms.txt

Override default Oracle-to-PostgreSQL data type mappings by editing the ora2pg.conf configuration file.

```ini

```

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

### Custom Boolean Value Mappings

Source: https://context7.com/darold/ora2pg/llms.txt

Defines custom mappings for boolean values, allowing 'active' to map to 'inactive', 'on' to 'off', etc. This is useful for non-standard boolean representations.

```ini
# Custom boolean value mappings
BOOLEAN_VALUES  active:inactive on:off open:closed
```

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

### Replace Tables and Columns

Source: https://context7.com/darold/ora2pg/llms.txt

Renames Oracle tables and columns to new PostgreSQL names during the export process.

```APIDOC
## replace_tables() / replace_cols()

### Description
Renames tables and columns during export to match PostgreSQL naming conventions.

### Method
Perl method call

### Parameters for `replace_tables`:
- **tableNameMapping** (hashref) - Required - A hash where keys are Oracle table names and values are the desired PostgreSQL table names.

### Parameters for `replace_cols`:
- **columnNameMapping** (hashref) - Required - A hash where keys are Oracle table names. Each value is another hash where keys are Oracle column names and values are the desired PostgreSQL column names.
```

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

### Override Configuration Directives Inline

Source: https://context7.com/darold/ora2pg/llms.txt

Override any configuration directive directly on the command line using the -O option. This is useful for temporary changes or specific export tasks.

```bash
ora2pg -t TABLE -c ora2pg.conf -O "SCHEMA=SCOTT" -O "PG_VERSION=16"
```

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

### Validate Migration: Row Count Only

Source: https://context7.com/darold/ora2pg/llms.txt

Performs a faster validation by comparing only row counts between Oracle and PostgreSQL. Requires PostgreSQL connection details.

```bash
ora2pg -t TEST_COUNT -c ora2pg.conf \
  --pg_dsn "dbi:Pg:dbname=mydb;host=pghost" \
  --pg_user pgadmin --pg_pwd pgpassword
```

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

### Convert Oracle PL/SQL Function to PL/PGSQL

Source: https://context7.com/darold/ora2pg/llms.txt

Converts Oracle PL/SQL function code from a file to PostgreSQL's PL/PGSQL format without connecting to a database. Specify input and output files using -i and -o.

```bash
ora2pg -t FUNCTION -p -i oracle_functions.sql -o pg_functions.sql \
  -c /etc/ora2pg/ora2pg.conf
```

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

### Validate Migration: View Row Count

Source: https://context7.com/darold/ora2pg/llms.txt

Specifically validates row counts for views between Oracle and PostgreSQL. Requires PostgreSQL connection details.

```bash
ora2pg -t TEST_VIEW -c ora2pg.conf \
  --pg_dsn "dbi:Pg:dbname=mydb;host=pghost" \
  --pg_user pgadmin --pg_pwd pgpassword
```

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

### Customizing NUMBER Type Mapping

Source: https://context7.com/darold/ora2pg/llms.txt

Overrides the default mapping for NUMBER(*,0) to use 'bigint' instead of 'numeric(38)'. Useful for large integer types.

```ini
# Replace NUMBER(*,0) with bigint instead of numeric(38)
DATA_TYPE   NUMBER(*\,0):bigint
```

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

### Two-Pass BLOB Export for Large Objects (> 1 GB)

Source: https://context7.com/darold/ora2pg/llms.txt

This two-pass export handles BLOBs larger than 1 GB. Pass 1 exports data and saves BLOBs to separate files. Pass 2 runs the generated lo_import scripts after data loading.

```bash
# Pass 1: export data (sets Oid column to 0, saves BLOB to separate files)
ora2pg -t COPY -c ora2pg.conf --blob_to_lo --lo_import -o data.sql
```

```bash
# Pass 2: run generated lo_import scripts after loading data
export PGDATABASE=mydb
export PGHOST=pghost
./lo_import-MYTABLE.sh
```

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

### Execute Ora2Pg Export to Explicit Filename

Source: https://context7.com/darold/ora2pg/llms.txt

Specify an explicit filename as an argument to the export_schema() method to direct the output to a particular file.

```perl
# Write to an explicit filename
$ora2pg->export_schema('my_functions.sql');
```

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

### Automatic Boolean Conversion

Source: https://context7.com/darold/ora2pg/llms.txt

Enables automatic conversion of single-character or single-digit number columns to boolean types. Useful for simplifying boolean representation.

```ini
# Auto-convert char(1) and number(1) columns to boolean
REPLACE_AS_BOOLEAN  NUMBER:1 CHAR:1
```

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

### Rename Tables and Columns During Export with Ora2Pg

Source: https://context7.com/darold/ora2pg/llms.txt

Use replace_tables and replace_cols to rename Oracle objects to PostgreSQL equivalents during schema export.

```perl
use Ora2Pg;

my $ora2pg = new Ora2Pg(
    config => '/etc/ora2pg/ora2pg.conf',
    type   => 'TABLE',
);

# Rename Oracle tables to new PostgreSQL table names
$ora2pg->replace_tables(
    'EMP'         => 'employees',
    'DEPT'        => 'departments',
    'SAL_GRADES'  => 'salary_grades',
);

# Rename columns within specific tables
$ora2pg->replace_cols(
    'EMP'  => { 'EMPNO' => 'employee_id', 'ENAME' => 'name', 'DEPTNO' => 'department_id' },
    'DEPT' => { 'DEPTNO' => 'department_id', 'DNAME' => 'department_name' },
);

$ora2pg->export_schema('renamed_schema.sql');
```

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

### Set Delete Clause

Source: https://context7.com/darold/ora2pg/llms.txt

Specifies a DELETE clause to be executed before data import, useful for replacing changed data.

```APIDOC
## set_delete_clause()

### Description
Applies a DELETE clause before data import to remove existing records that match the criteria, effectively replacing changed data.

### Method
Perl method call

### Parameters
- **globalClause** (string) - Optional - A global DELETE clause applied to all tables. Use an empty string if no global clause is desired.
- **tableClauseMapping** (hashref) - Optional - A hash where keys are table names and values are specific DELETE clauses for those tables. These override the global clause for the specified tables.
```

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

### Oracle Spatial/PostGIS Export Configuration

Source: https://context7.com/darold/ora2pg/llms.txt

Configure Ora2Pg to export Oracle SDO_GEOMETRY columns as PostGIS geometry types. Options include internal extraction, WKT extraction, and SRID conversion.

```bash
# Export spatial tables (uses INTERNAL extraction by default)
# In ora2pg.conf:
# AUTODETECT_SPATIAL_TYPE  1
# GEOMETRY_EXTRACT_TYPE    INTERNAL
# CONVERT_SRID             1
# DEFAULT_SRID             4326
ora2pg -t TABLE -c ora2pg.conf -o spatial_schema.sql

# Force WKT extraction via Oracle functions
# GEOMETRY_EXTRACT_TYPE    WKT
ora2pg -t TABLE -c ora2pg.conf -o spatial_schema_wkt.sql

# Export spatial data
ora2pg -t COPY -c ora2pg.conf -o spatial_data.sql

# For ArcSDE with custom function names, in ora2pg.conf:
# ST_SRID_FUNCTION         sde.st_srid
# ST_DIMENSION_FUNCTION    sde.st_dimension
# ST_ASBINARY_FUNCTION     sde.st_asbinary
# ST_ASTEXT_FUNCTION       sde.st_astext
# ST_GEOMETRYTYPE_FUNCTION sde.st_geometrytype

# Result: Oracle SDO_GEOMETRY -> PostgreSQL geometry(POLYGON, 4326)
# Before: shape SDO_GEOMETRY
# After:  shape geometry(POLYGON, 4326)
```

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

### Modify Table Structure

Source: https://context7.com/darold/ora2pg/llms.txt

Allows specifying which columns to export for a given table.

```APIDOC
## modify_struct()

### Description
Exports only specific columns from a table.

### Method
Perl method call

### Parameters
- **tableName** (string) - Required - The name of the table to modify.
- **columnNames** (string) - Required - One or more column names to include in the export.
```

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

### Oracle Spatial / PostGIS Export

Source: https://context7.com/darold/ora2pg/llms.txt

Exports Oracle SDO_GEOMETRY columns and converts them to PostGIS geometry types.

```APIDOC
## Oracle Spatial to PostGIS Export

### Description
Configures Ora2Pg to export Oracle Spatial data (SDO_GEOMETRY) into a format compatible with PostgreSQL's PostGIS extension.

### Configuration Options (in `ora2pg.conf`)
- `AUTODETECT_SPATIAL_TYPE`: Set to `1` to enable automatic detection of spatial types.
- `GEOMETRY_EXTRACT_TYPE`: Specifies the method for extracting geometry data. Options include `INTERNAL` (default) or `WKT` (using Oracle functions).
- `CONVERT_SRID`: Set to `1` to convert Spatial Reference Identifiers (SRIDs).
- `DEFAULT_SRID`: The default SRID to use if conversion is not possible or specified (e.g., `4326`).
- `ST_SRID_FUNCTION`, `ST_DIMENSION_FUNCTION`, `ST_ASBINARY_FUNCTION`, `ST_ASTEXT_FUNCTION`, `ST_GEOMETRYTYPE_FUNCTION`: Custom function names for ArcSDE environments.
```

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

### Increasing VARCHAR Limits

Source: https://context7.com/darold/ora2pg/llms.txt

Sets a limit for increasing VARCHAR sizes, particularly for multi-byte character encodings. Adjust the value based on your character set requirements.

```ini
# Increase varchar limits for multi-byte character encoding
DOUBLE_MAX_VARCHAR  1
```

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

### Forcing Specific Column Types

Source: https://context7.com/darold/ora2pg/llms.txt

Allows overriding the inferred data type for specific table columns. Use this to enforce precise type conversions.

```ini
# Force specific table columns to a different type
MODIFY_TYPE EMPLOYEES:SALARY:numeric(12\,2),EMPLOYEES:AGE:smallint
```

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

### Set Where Clause

Source: https://context7.com/darold/ora2pg/llms.txt

Applies row filters to control which data is exported from tables.

```APIDOC
## set_where_clause()

### Description
Applies a WHERE clause to filter rows during data export. Can be set globally or per table.

### Method
Perl method call

### Parameters
- **globalClause** (string) - Optional - A global WHERE clause applied to all tables. Use an empty string if no global clause is desired.
- **tableClauseMapping** (hashref) - Optional - A hash where keys are table names and values are specific WHERE clauses for those tables. These override the global clause for the specified tables.
```

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

### Export Specific Columns and Exclude Sensitive Columns with Ora2Pg

Source: https://context7.com/darold/ora2pg/llms.txt

Control which columns are exported from a table or exclude sensitive columns from all exports.

```perl
$ora2pg->modify_struct('EMPLOYEES', 'employee_id', 'first_name', 'last_name', 'salary');

# Exclude sensitive columns from all exports
$ora2pg->exclude_columns('EMPLOYEES', 'SSN', 'BANK_ACCOUNT', 'PASSWORD_HASH');
$ora2pg->exclude_columns('CUSTOMERS', 'CREDIT_CARD_NUMBER');

$ora2pg->export_schema('employees_data.sql');
```

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

### Default Type Mapping in Ora2Pg

Source: https://context7.com/darold/ora2pg/llms.txt

Defines the default mapping of Oracle data types to PostgreSQL types. Customize this section in ora2pg.conf as needed for your migration.

```ini
# Default type mapping (customize as needed)
DATA_TYPE   VARCHAR2:varchar,NVARCHAR2:varchar,DATE:timestamp(0),
            CLOB:text,BLOB:bytea,NUMBER:numeric,FLOAT:double precision,
            XMLTYPE:xml,RAW(16):uuid,RAW(32):uuid
```

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

### Ora2Pg Perl Module API - modify_struct() / exclude_columns()

Source: https://context7.com/darold/ora2pg/llms.txt

Control which columns are exported per table programmatically using modify_struct or exclude_columns.

```APIDOC
## `Ora2Pg` Perl Module API — `modify_struct()` / `exclude_columns()`

Control which columns are exported per table programmatically.

```perl
use Ora2Pg;

my $ora2pg = new Ora2Pg(
    config => '/etc/ora2pg/ora2pg.conf',
    type   => 'COPY',
);

# Example usage (specific methods not shown in source, only their existence)
# $ora2pg->modify_struct(...);
# $ora2pg->exclude_columns(...);
```
```

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

### Exclude Columns

Source: https://context7.com/darold/ora2pg/llms.txt

Excludes specified columns from all exports or for specific tables.

```APIDOC
## exclude_columns()

### Description
Excludes sensitive or unnecessary columns from exports.

### Method
Perl method call

### Parameters
- **tableName** (string) - Required - The name of the table from which to exclude columns. Can be an empty string to apply globally.
- **columnNames** (string) - Required - One or more column names to exclude.
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.