How to set and configure any EBS sqlplus commands in a Post-Migration step to Alter table (4383099)

Return

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Title

How to set and configure any EBS sqlplus commands in a Post-Migration step to Alter table
Description

This article explains how a Stat Post‑Migration step can orchestrate an Oracle E‑Business Suite (EBS) ALTER TABLE by running a Custom Command on the Stat Central Agent that invokes a customer-managed script on the EBS application server via SSH.
Stat is not responsible for Oracle/EBS configuration or database changes; Stat only executes the configured operating system command and records the resulting output and exit code. The actual database operation is performed by the remote script in the EBS environment using SQL*Plus.
Cause

This behavior occurs because Stat Post‑Migration steps run as operating system commands under the Stat Central Agent execution context. That execution context is commonly non-interactive and may not load user profiles or environment variables needed for Oracle tools. As a result:

sqlplus may not be found (PATH not set / ORACLE_HOME not loaded)
EBS environment variables are not initialized (Apps environment not sourced)
the Central Agent host may not be the correct node to execute EBS-specific commands
interactive prompts (password prompts, host key prompts) cannot be answered, causing failure/hang

Using SSH to run the change on the EBS application server resolves these limitations because the script can explicitly source the EBS environment and run SQL*Plus in the correct runtime context.
Resolution
Use a Stat Post‑Migration Custom Command that runs on the Central Agent and invokes an EBS-hosted script via SSH. Then ensure the remote script loads the EBS Apps environment and runs the required DDL with proper error handling so Stat receives an accurate return code.
1) Configure the Stat Post‑Migration step

In Stat, create or edit the Post‑Migration step:
- Type: Custom Command
- Run: On Central Agent Machine
- Command: ssh -n -o BatchMode=yes oracle@10.250.70.3 /home/oracle/pmstep/alter_tab.sh
Why these options matter:
- -n prevents the command from waiting on standard input
- BatchMode=yes prevents interactive password/passphrase prompting (required for automation)
2) Create the remote script on the EBS application server
Place the script on the EBS app tier (example path shown below and make it executable: chmod +x /home/oracle/pmstep/alter_tab.sh

Example Script (EBS Server): alter_tab.sh (customer can adapt object names and environment file path):

#!/usr/bin/env bash

set -euo pipefail

# --- Source EBS Apps environment ---

APPS_ENV=/u01/install/APPS/19.0.0/EBSDB_db.env

if [ ! -f "$APPS_ENV" ]; then

echo "ERROR: Apps env not found: $APPS_ENV"

exit 1

fi

source "$APPS_ENV"

# --- Safety check: ensure sqlplus exists ---

if ! command -v sqlplus >/dev/null 2>&1; then

echo "ERROR: sqlplus not found after sourcing Apps env"

echo "ORACLE_HOME=$ORACLE_HOME"

exit 1

fi

# --- Run SQL ---

sqlplus -s APPS/APPS@EBSDB <<'EOF'

whenever sqlerror exit 1

alter table XXQUEST_TABLE add (AFTER_MIGR varchar2(20));

commit;

exit

EOF

exit 0

3) Validate successful execution in Stat

After the migration runs:

Open Migration Console and go to Pre and Post Migration Steps tab,
After it has run, click on View History.
Confirm:
Return code = 0
Output includes messages like:
Table altered.
Commit complete.
Additional Information

Responsibility / Ownership (Important) Stat / Stat Central Agent responsibilities: Runs the configured OS command. Captures output and the return code. Applies migration logic based on return code (stop/continue). Customer responsibilities (EBS Admin / DBA / System Admin): Provide and support the SSH connectivity and key authentication Maintain the remote script and its permissions Maintain EBS environment configuration and context files Ensure Oracle client tools exist on the target node (sqlplus) Provide secure credential handling and schema privileges Validate and support the SQL logic and its impact

Feedback Submitted

Did this article solve an issue for you?

Select Rating

Request a KB Article

How to set and configure any EBS sqlplus commands in a Post-Migration step to Alter table (4383099)

Title

Description

Cause

Resolution

1) Configure the Stat Post‑Migration step

2) Create the remote script on the EBS application server

3) Validate successful execution in Stat

Additional Information

Leave a Comment