Automating Oracle Database Object Export and Import with Jenkins

In this blog post, I’ll walk you through a detailed, real-world approach for automating the export and import of Oracle database objects between development and production environments using Jenkins. This method is ideal for teams needing to frequently synchronize database objects—such as tables, views, triggers, and packages—across different systems with full control and traceability.

Project Overview

We have a setup involving three main servers:

Development Server (DEV): Hosts the up-to-date database objects and applications.
Production Server (PROD): The target environment where exported objects will be imported.
Jenkins Server: Orchestrates the entire process, stores intermediate files, and provides automation.

All necessary access credentials (server login, database schema user, passwords, etc.) are prepared in advance for secure, automated operations.

Jenkins Pipeline and Parameters

The Jenkins pipeline is parameterized, allowing you to execute and test each step individually or automate the entire workflow. The key parameters include:

A1_START_EXPORT_AND_DELETE_OLD_FILES: Initiates export and cleans up old files.
A1_1_DEPLOY_EXPORT_SEQUENCES to A1_6_DEPLOY_EXPORT_PACKAGES: Export specific database object types.
A2_COPY_FROM_DB_TO_JENKINS: Copies export files from DEV to Jenkins.
B1_START_IMPORT_AND_DELETE_OLD_FILES: Starts import and cleans up old files on PROD.
B1_1_DEPLOY_IMPORT_SEQUENCES to B1_6_DEPLOY_IMPORT_PACKAGES: Import specific object types into PROD.
C1_DEPLOY_EXPORT_APPS and C2_COPY_APPS_TO_JENKINS: Handle app export and transfer.
D1_COPY_APPS_TO_PROD and D2_INSTALL_APPS_IN_PROD: Transfer and install apps on PROD.

This modular approach allows for granular control, making it easy to test or rerun any part of the process as needed.

Detailed Step-by-Step Process

Script 1: Export Database Objects from DEV

Purpose:
Export all relevant database objects from the development environment and save them both on the Oracle server and the Jenkins server for backup and further processing.

Detailed Steps:

Initiate Export: The script connects to the DEV Oracle database using the provided schema credentials.
Export Objects: It exports sequences, tables, columns, views, triggers, and packages using Oracle’s export utilities (e.g., expdp).
Save to Oracle Directory: The exported files are saved in a predefined Oracle directory on the DEV server.
Copy to Jenkins: The script then securely copies these export files from the DEV server to the Jenkins server using protocols like SCP or SFTP.
Cleanup: Optionally, old export files are deleted to save space and avoid confusion.

Script 1:

Bash

xxxxxxxxxx
 
#!/bin/bash
set -e
​
#############################################
# Remote Server & DB Config
#############################################
# enter here dev server ip
V_REMOTE_SERVER_IP="12.345.67.89"
​
# enter here the dever server database schema name + password
export V_DB_SCHEMA_NAME="MYSTORELOCATOR"
export V_DB_SCHEMA_PASS="1*2*3*4*"
​
V_USER_NAME="root"
V_USER_PASS="1*2*3*4*"
​
export DB_NAME="ORCLPDB"
​
# Remote Export Directory (im Docker-Volume) - go over moba exterm, enter the folder, wehre all the scripts
REMOTE_EXPORT_PATH="/var/lib/docker/volumes/db_oracle-19c-data/_data/deploy/mystorelocator/MYSTORELOCATOR_DEPLOYMENT_EXPORT/"
​
EXPORT_ID=""   # wird gleich aus DB geholt
​
#############################################
# STEP 1: Export starten?  DO NOT CHANGE ----------------------------------------
#############################################
if [ "$A1_START_EXPORT_AND_DELETE_OLD_FILES" = "true" ]; then
    echo ">>> A1_START_EXPORT_AND_DELETE_OLD_FILES = true → altes Zielverzeichnis wird geleert."
    sshpass -p "$V_USER_PASS" ssh -o StrictHostKeyChecking=no "$V_USER_NAME@$V_REMOTE_SERVER_IP" "rm -f $REMOTE_EXPORT_PATH/*"
​
    echo ">>> Verbinde zu Remote Server und starte Deployment ..."
​
    # komplette SQL-Ausgabe sichern (inkl. DBMS_OUTPUT)
    SQL_OUTPUT=$(sshpass -p "$V_USER_PASS" ssh -o StrictHostKeyChecking=no "$V_USER_NAME@$V_REMOTE_SERVER_IP" << EOF
      docker exec -i database bash -c "
        sqlplus -s $V_DB_SCHEMA_NAME/$V_DB_SCHEMA_PASS@$DB_NAME <<SQL
          SET SERVEROUTPUT ON SIZE UNLIMITED
          SET HEADING OFF
          SET FEEDBACK OFF
          SET PAGESIZE 0
​
          DECLARE
              v_EXRU_ID NUMBER;
          BEGIN
              deb_deployment.pr_start_deployment(v_EXRU_ID);
​
              -- Conditional Execution je nach Jenkins-Parametern
              IF '$A1_1_DEPLOY_EXPORT_SEQUENCES' = 'true' THEN
                  deb_deployment.pr_process_sequences(v_EXRU_ID);
                  DBMS_OUTPUT.PUT_LINE('→ Sequences exported.');
              END IF;
​
              IF '$A1_2_DEPLOY_EXPORT_TABLES' = 'true' THEN
                  deb_deployment.pr_process_tables(v_EXRU_ID);
                  DBMS_OUTPUT.PUT_LINE('→ Tables exported.');
              END IF;
​
              IF '$A1_3_DEPLOY_EXPORT_COLUMNS' = 'true' THEN
                  deb_deployment.pr_process_columns(v_EXRU_ID);
                  DBMS_OUTPUT.PUT_LINE('→ Columns exported.');
              END IF;
​
              IF '$A1_4_DEPLOY_EXPORT_VIEWS' = 'true' THEN
                  deb_deployment.pr_process_views(v_EXRU_ID);
                  DBMS_OUTPUT.PUT_LINE('→ Views exported.');
              END IF;
​
              IF '$A1_5_DEPLOY_EXPORT_TRIGGERS' = 'true' THEN
                  deb_deployment.pr_process_trigger(v_EXRU_ID);
                  DBMS_OUTPUT.PUT_LINE('→ Triggers exported.');
              END IF;
​
              IF '$A1_6_DEPLOY_EXPORT_PACKAGES' = 'true' THEN
                  deb_deployment.pr_export_packages_to_file(v_EXRU_ID);
                  DBMS_OUTPUT.PUT_LINE('→ Packages exported.');
              END IF;
​
              deb_deployment.pr_finish_deployment(v_EXRU_ID, 'SUCCESS');
              DBMS_OUTPUT.PUT_LINE('Deployment run ' || v_EXRU_ID || ' finished successfully.');
              DBMS_OUTPUT.PUT_LINE('EXPORT_ID=' || v_EXRU_ID);
​
          EXCEPTION
              WHEN OTHERS THEN
                  deb_deployment.pr_finish_deployment(v_EXRU_ID, 'FAILED');
                  DBMS_OUTPUT.PUT_LINE('Deployment failed: ' || SQLERRM);
                  DBMS_OUTPUT.PUT_LINE('EXPORT_ID=' || NVL(v_EXRU_ID,-1));
                  RAISE;
          END;
          /
          EXIT;
SQL
      "
EOF
)
​
    # gesamte SQL-Ausgabe ins Jenkins-Log schreiben
    echo "$SQL_OUTPUT"
​
    # nur die EXPORT_ID extrahieren
    EXPORT_ID=$(echo "$SQL_OUTPUT" | grep "EXPORT_ID=" | sed 's/EXPORT_ID=//')
    echo ">>> Deployment Export ID = $EXPORT_ID"
​
else
    echo ">>> A1_START_EXPORT_AND_DELETE_OLD_FILES = false → kein Deployment gestartet."
fi
​
#############################################
# STEP 2: Files vom Remote nach Jenkins holen?
#############################################
if [ "$A2_COPY_FROM_DB_TO_JENKINS" = "true" ]; then
    if [ -z "$EXPORT_ID" ]; then
        EXPORT_ID=$(date +%Y%m%d_%H%M%S) # fallback falls keine ID gefunden
    fi
​
    WORKSPACE_DIR="$WORKSPACE/export_run_$EXPORT_ID"
    mkdir -p "$WORKSPACE_DIR"
​
    echo ">>> Kopiere Dateien von $V_REMOTE_SERVER_IP:$REMOTE_EXPORT_PATH nach $WORKSPACE_DIR ..."
    sshpass -p "$V_USER_PASS" scp -o StrictHostKeyChecking=no -r \
        "$V_USER_NAME@$V_REMOTE_SERVER_IP:$REMOTE_EXPORT_PATH/*" "$WORKSPACE_DIR/"
​
    echo ">>> Dateien erfolgreich kopiert!"
    echo ">>> Workspace-Pfad: $WORKSPACE_DIR"
else
    echo ">>> A2_COPY_FROM_DB_TO_JENKINS = false → keine Dateien kopiert."
fi

Script 2: Import Database Objects into PROD

Purpose:
Transfer the exported database object files from Jenkins to the production server and import them into the production Oracle schema.

Detailed Steps:

Transfer Files: The script copies the latest export files from the Jenkins server to the PROD server.
Initiate Import: Using the Oracle import utility (e.g., impdp), it imports the database objects into the target schema on PROD.
Object Type Control: The process can be tailored to import only certain object types (sequences, tables, etc.) based on Jenkins parameters.
Cleanup: Old import files on the PROD server can be deleted to maintain a clean environment.

Script 2 :

Bash

xxxxxxxxxx
 
#!/bin/bash
set -e
​
#############################################
# PROD Server & DB Config
#############################################
# enter prod server ip
V_REMOTE_SERVER_IP="12.345.67.89"
​
# enter prod db schema name + pass
export V_DB_SCHEMA_NAME="MYSTORELOCATOR_PROD"
export V_DB_SCHEMA_PASS="1*2*3*4*"
​
V_USER_NAME="root"
V_USER_PASS="1*2*3*4*"
​
export DB_NAME="ORCLPDB"
​
# Pfad auf dem Host (für Jenkins scp)
# open mobaexterm and enter the host path
PROD_EXPORT_PATH_HOST="/var/lib/docker/volumes/db_oracle-19c-data/_data/deploy/mystorelocator_prod/MYSTORELOCATOR_PROD_DEPLOYMENT_EXPORT"
​
# Pfad im Container (für sqlplus)
PROD_EXPORT_PATH_CONTAINER="/opt/oracle/oradata/deploy/mystorelocator_prod/MYSTORELOCATOR_PROD_DEPLOYMENT_EXPORT"
​
#############################################
# Lokale Dateien (vom letzten Export in Jenkins)
#############################################
LOCAL_EXPORT_DIR=$(ls -td "$WORKSPACE"/export_run_* | head -1)
​
if [ ! -d "$LOCAL_EXPORT_DIR" ]; then
    echo "❌ Fehler: Kein Export-Ordner im Jenkins Workspace gefunden!"
    exit 1
fi
​
echo ">>> Lokaler Export-Ordner gefunden: $LOCAL_EXPORT_DIR"
​
#############################################
# STEP 1: Import starten?
#############################################
if [ "$B1_START_IMPORT_AND_DELETE_OLD_FILES" = "true" ]; then
    echo ">>> B1_START_IMPORT_AND_DELETE_OLD_FILES = true → Zielverzeichnis in PROD wird geleert."
    sshpass -p "$V_USER_PASS" ssh -o StrictHostKeyChecking=no "$V_USER_NAME@$V_REMOTE_SERVER_IP" "rm -f $PROD_EXPORT_PATH_HOST/*"
​
    echo ">>> Kopiere Dateien von Jenkins ($LOCAL_EXPORT_DIR) nach PROD ($PROD_EXPORT_PATH_HOST) ..."
    sshpass -p "$V_USER_PASS" scp -o StrictHostKeyChecking=no -r \
        "$LOCAL_EXPORT_DIR/"* "$V_USER_NAME@$V_REMOTE_SERVER_IP:$PROD_EXPORT_PATH_HOST/"
​
    echo ">>> Dateien erfolgreich nach PROD kopiert!"
    echo ">>> Remote-Verzeichnis: $PROD_EXPORT_PATH_HOST"
​
fi
​
#############################################
# STEP 2: SQL-Skripte in PROD ausführen  --- DO NOT CHANGE FROM HERE -----------
#############################################
echo ">>> Starte Importprozess in PROD ..."
sshpass -p "$V_USER_PASS" ssh -o StrictHostKeyChecking=no "$V_USER_NAME@$V_REMOTE_SERVER_IP" << EOF
  docker exec -i database bash -c "
    cd $PROD_EXPORT_PATH_CONTAINER && \
    export NLS_LANG=.AL32UTF8 && \
    sqlplus -s $V_DB_SCHEMA_NAME/$V_DB_SCHEMA_PASS@$DB_NAME <<SQL
      SET SERVEROUTPUT ON
      SET DEFINE OFF
      WHENEVER SQLERROR EXIT SQL.SQLCODE
​
      -- 1) Import Run starten
      BEGIN
          DEB_DEPLOYMENT.pr_create_DEB_IMPORT_RUNS;
          DBMS_OUTPUT.PUT_LINE('Import Run gestartet.');
      END;
      /
​
      -- 2) Conditional Execution je nach Jenkins-Parametern
      $(if [ "$B1_1_DEPLOY_IMPORT_SEQUENCES" = "true" ]; then
        echo "@seq_run_all.sql"
      fi)
​
      $(if [ "$B1_2_DEPLOY_IMPORT_TABLES" = "true" ]; then
        echo "@create_table_run_all.sql" 
      fi)
​
      $(if [ "$B1_3_DEPLOY_IMPORT_COLUMNS" = "true" ]; then
        echo "@alter_table_run_all.sql"
      fi)
​
      $(if [ "$B1_4_DEPLOY_IMPORT_VIEWS" = "true" ]; then
        echo "@view_run_all.sql"
      fi)
​
      $(if [ "$B1_5_DEPLOY_IMPORT_TRIGGERS" = "true" ]; then
        echo "@trigger_run_all.sql"
      fi)
​
      $(if [ "$B1_6_DEPLOY_IMPORT_PACKAGES" = "true" ]; then
        echo "@packages_run_all.sql" 
      fi)
​
      -- 3) Import Run beenden
      BEGIN
          DEB_DEPLOYMENT.pr_stop_import_runs;
          DBMS_OUTPUT.PUT_LINE('Import Run beendet.');
      END;
      /
​
      EXIT;
SQL
  "
EOF
​
echo ">>> SQL-Prozess in PROD erfolgreich abgeschlossen."

Script 3: Export and Transfer Applications

Purpose:
Export application definitions (such as Oracle APEX apps) from the development environment and store them on Jenkins for safe keeping and later deployment.

Detailed Steps:

Export Applications: The script connects to the DEV environment and exports the desired applications, typically using Oracle APEX export tools or custom scripts.
Save to Directory: The exported app files are stored in the Oracle directory.
Copy to Jenkins: These files are then securely transferred to the Jenkins server for backup and staging.
Format Conversion: If necessary, the script can convert or package the app files into formats suitable for import on PROD.

Script 3:

Bash

xxxxxxxxxx
 
#!/bin/bash
set -e
​
#############################################
# Remote Server & DB Config
#############################################
V_REMOTE_SERVER_IP="12.345.67.89"
​
# DEV Schema
export V_DB_SCHEMA_NAME="MYSTORELOCATOR"
export V_DB_SCHEMA_PASS="1*2*3*4*"
​
V_USER_NAME="root"
V_USER_PASS="1*2*3*4*"
​
export DB_NAME="ORCLPDB"
​
# Remote Export Directory (Docker-Volume, schon als DIRECTORY gemappt)
REMOTE_EXPORT_PATH="/var/lib/docker/volumes/db_oracle-19c-data/_data/deploy/mystorelocator/MYSTORELOCATOR_DEPLOYMENT_EXPORT/"
​
#############################################
# App-Liste (App-ID:App-Name)
# -> Einfach erweitern, wenn mehr Apps dazukommen
#############################################
APPS=(
  "400:Mylocator_Management"
  "410:My_Store_Locator"
  "420:Debug"
)
#############################################
# STEP 1: Export APEX Apps inkl. Übersetzungen
#############################################
if [ "$C1_DEPLOY_EXPORT_APPS" = "true" ]; then
    echo ">>> C1_DEPLOY_EXPORT_APPS = true → APEX Apps werden exportiert."
    # alte Files löschen
    sshpass -p "$V_USER_PASS" ssh -o StrictHostKeyChecking=no \
        "$V_USER_NAME@$V_REMOTE_SERVER_IP" "rm -f $REMOTE_EXPORT_PATH/f*.sql"
​
    for app in "${APPS[@]}"; do
        APP_ID=$(echo $app | cut -d':' -f1)
        APP_NAME=$(echo $app | cut -d':' -f2)
​
        echo ">>> Exportiere App $APP_ID ($APP_NAME) inkl. Übersetzungen …"
​
        sshpass -p "$V_USER_PASS" ssh -o StrictHostKeyChecking=no "$V_USER_NAME@$V_REMOTE_SERVER_IP" "
docker exec -i database bash -c \"
  sqlplus -s $V_DB_SCHEMA_NAME/$V_DB_SCHEMA_PASS@$DB_NAME <<SQL
    SET SERVEROUTPUT ON
    WHENEVER SQLERROR EXIT SQL.SQLCODE
​
    DECLARE
        l_files apex_t_export_files;
    BEGIN
        -- Haupt-App inkl. Übersetzungen exportieren
        l_files := apex_export.get_application(
            p_application_id    => $APP_ID,
            p_with_translations => true
        );
        IF l_files.count > 0 THEN
            dbms_xslprocessor.clob2file(
                cl        => l_files(1).contents,
                flocation => 'MYSTORELOCATOR_DEPLOYMENT_EXPORT',
                fname     => 'f${APP_ID}_${APP_NAME}.sql',
                csid      => nls_charset_id('AL32UTF8')
            );
            dbms_output.put_line('App $APP_ID exportiert: f${APP_ID}_${APP_NAME}.sql');
        END IF;
 
    END;
    /
    EXIT;
SQL
\"
"
    done
else
    echo ">>> C1_DEPLOY_EXPORT_APPS = false → keine APEX-Apps exportiert."
fi
​
​
#############################################
# STEP 2: Files vom Remote nach Jenkins holen
#############################################
if [ "$C2_COPY_APPS_TO_JENKINS" = "true" ]; then
    EXPORT_ID=$(date +%Y%m%d_%H%M%S)
    WORKSPACE_DIR="$WORKSPACE/export_apps_$EXPORT_ID"
    mkdir -p "$WORKSPACE_DIR"
​
    echo ">>> Kopiere App-Files von $V_REMOTE_SERVER_IP:$REMOTE_EXPORT_PATH nach $WORKSPACE_DIR ..."
    sshpass -p "$V_USER_PASS" scp -o StrictHostKeyChecking=no \
        "$V_USER_NAME@$V_REMOTE_SERVER_IP:$REMOTE_EXPORT_PATH/f*.sql" "$WORKSPACE_DIR/"
​
    echo ">>> Alle Apps erfolgreich kopiert!"
    echo ">>> Workspace-Pfad: $WORKSPACE_DIR"
else
    echo ">>> C2_COPY_APPS_TO_JENKINS = false → keine App-Files kopiert."
fi

Script 4: Deploy and Publish Applications on PROD

Purpose:
Transfer the latest application export files from Jenkins to the production server, install them, and publish so they are immediately available to end users.

Detailed Steps:

Copy Files to PROD: The script fetches the most recent app export files from Jenkins and transfers them to the PROD server.
Install Applications: It runs the necessary commands to install or update the applications in the production environment.
Publish and Verify: The script ensures that all language versions and configurations are correctly published and visible to users.
Post-Deployment Checks: Optionally, it can trigger automated tests or notifications to confirm successful deployment.

Script 4:

Bash

xxxxxxxxxx
 
#!/bin/bash
set -e
​
#############################################
# PROD Server & DB Config
#############################################
V_REMOTE_SERVER_IP="12.345.67.89"
​
export V_DB_SCHEMA_NAME="MYSTORELOCATOR_PROD"
export V_DB_SCHEMA_PASS="1*2*3*4*"
​
V_USER_NAME="root"
V_USER_PASS="1*2*3*4*"
​
export DB_NAME="ORCLPDB"
​
# Host-Pfad (für scp)
PROD_EXPORT_PATH_HOST="/var/lib/docker/volumes/db_oracle-19c-data/_data/deploy/mystorelocator_prod/MYSTORELOCATOR_PROD_DEPLOYMENT_EXPORT"
​
# Container-Pfad (für sqlplus)
PROD_EXPORT_PATH_CONTAINER="/opt/oracle/oradata/deploy/mystorelocator_prod/MYSTORELOCATOR_PROD_DEPLOYMENT_EXPORT"
​
#############################################
# Mapping-Liste: Datei → Ziel-ID
#############################################
APPS=(
  "f400_Mylocator_Management.sql:401"
  "f410_My_Store_Locator.sql:411"
  "f420_Debug.sql:421"
)
​
#############################################
# Lokale Dateien (vom letzten Export in Jenkins)
#############################################
LOCAL_EXPORT_DIR=$(ls -td "$WORKSPACE"/export_apps_* 2>/dev/null | head -1)
​
if [ ! -d "$LOCAL_EXPORT_DIR" ]; then
    echo "❌ Fehler: Kein APEX Export-Ordner im Jenkins Workspace gefunden!"
    exit 1
fi
​
echo ">>> Lokaler APEX Export-Ordner gefunden: $LOCAL_EXPORT_DIR"
​
#############################################
# STEP D1: APEX App Files nach PROD kopieren
#############################################
if [ "$D1_COPY_APPS_TO_PROD" = "true" ]; then
    echo ">>> D1_COPY_APPS_TO_PROD = true → Zielverzeichnis in PROD wird geleert."
    sshpass -p "$V_USER_PASS" ssh -o StrictHostKeyChecking=no \
        "$V_USER_NAME@$V_REMOTE_SERVER_IP" "rm -f $PROD_EXPORT_PATH_HOST/f*.sql"
​
    for app in "${APPS[@]}"; do
        FILE=$(echo $app | cut -d':' -f1)
​
        if [ -f "$LOCAL_EXPORT_DIR/$FILE" ]; then
            echo ">>> Kopiere $FILE nach PROD ..."
            sshpass -p "$V_USER_PASS" scp -o StrictHostKeyChecking=no \
                "$LOCAL_EXPORT_DIR/$FILE" "$V_USER_NAME@$V_REMOTE_SERVER_IP:$PROD_EXPORT_PATH_HOST/"
        else
            echo "⚠️  Datei fehlt im Workspace: $LOCAL_EXPORT_DIR/$FILE"
            exit 1
        fi
    done
​
    echo ">>> Alle definierten App-Files erfolgreich nach PROD kopiert!"
else
    echo ">>> D1_COPY_APPS_TO_PROD = false → keine App-Files kopiert."
fi
#############################################
# STEP D2: APEX Apps in PROD installieren (Replace)
#############################################
echo ">>> Debug: D2_INSTALL_APPS_IN_PROD = '$D2_INSTALL_APPS_IN_PROD'"
​
if [ "$D2_INSTALL_APPS_IN_PROD" = "true" ]; then
    echo ">>> D2_INSTALL_APPS_IN_PROD = true → APEX Apps werden in PROD installiert (Replace-Modus) ..."
​
    SQL_CMDS=""
    for app in "${APPS[@]}"; do
        FILE=$(echo $app | cut -d':' -f1)
        TARGET_ID=$(echo $app | cut -d':' -f2)
​
        echo ">>> Preparing SQL command for $FILE → Ziel-App-ID $TARGET_ID ..."
        SQL_CMDS="$SQL_CMDS
PROMPT Installing $FILE as App $TARGET_ID ...
-- Neue App importieren
BEGIN
    apex_application_install.set_workspace('MYSTORELOCATOR_PROD');
    apex_application_install.set_schema('MYSTORELOCATOR_PROD');
    apex_application_install.set_application_id($TARGET_ID);
    apex_application_install.generate_offset; -- wichtig für Replace
END;
/
@$FILE
​
-- Übersetzung auf Deutsch publizieren
BEGIN
    apex_lang.publish_application(
        p_application_id => $TARGET_ID,
        p_language       => 'de'
    );
    DBMS_OUTPUT.put_line('App: Deutsche Übersetzung veröffentlicht.');
END;
/
"
    done
​
    echo ">>> Debug: Final SQL commands to run:"
    echo "$SQL_CMDS"
​
    sshpass -p "$V_USER_PASS" ssh -o StrictHostKeyChecking=no "$V_USER_NAME@$V_REMOTE_SERVER_IP" << EOF
      docker exec -i database bash -c "
        cd $PROD_EXPORT_PATH_CONTAINER && ls -lh f*.sql && \
        export NLS_LANG=.AL32UTF8 && \
        sqlplus -s $V_DB_SCHEMA_NAME/$V_DB_SCHEMA_PASS@$DB_NAME <<SQL
          SET SERVEROUTPUT ON
          SET DEFINE OFF
          WHENEVER SQLERROR EXIT SQL.SQLCODE
​
          BEGIN
            DBMS_OUTPUT.PUT_LINE('Starte APEX App Installationen (Replace-Modus) ...');
          END;
          /
​
$SQL_CMDS
​
          BEGIN
            DBMS_OUTPUT.PUT_LINE('Alle APEX Apps erfolgreich installiert.');
          END;
          /
​
          EXIT;
SQL
      "
EOF
​
    echo ">>> Installation der APEX Apps in PROD abgeschlossen (Replace)."
else
    echo ">>> D2_INSTALL_APPS_IN_PROD = false → keine APEX Apps installiert."
fi

Additional Features

Debug App: For the export process, a debug application is available, providing a graphical interface to monitor and control the export steps, making troubleshooting and manual interventions easier.

Summary

This Jenkins-based solution automates the entire lifecycle of exporting and importing Oracle database objects and applications between DEV and PROD. The process is modular, secure, and easily testable. By storing interim files on Jenkins, you gain an additional layer of backup and flexibility.

Get Started: Use this blueprint to set up your own automated Oracle database deployment pipeline with Jenkins.
Need Help or Have Questions? Leave a comment or reach out for support.
Share: Spread the word and help your team streamline database deployments!

{fullWidth}

License Key

Automating Oracle Database Object Export and Import with Jenkins DEV to PROD Release

Automating Oracle Database Object Export and Import with Jenkins

Project Overview

Jenkins Pipeline and Parameters

Detailed Step-by-Step Process

Script 1: Export Database Objects from DEV

Script 2: Import Database Objects into PROD

Script 3: Export and Transfer Applications

Script 4: Deploy and Publish Applications on PROD

Additional Features

Summary

How to install Oracle Apex 24.1 and Ords 24.1 with Database 19c on Windows

Explore

Latest Posts

Popular Posts

How to install Oracle Apex 24.1 and Ords 24.1 with Database 19c on Windows

How to install Apex 24.1 & Ords 24.3 with Database 21c on Windows Like a Pro!

How to install Oracle Apex 24.1 with Apache Tomcat and Ords 24.1 in Database 19c on Windows

Oracle Apex Login Page Design Trends You Need to Know About - Template 1

نموذج الاتصال