sqlplus Exit 0 Lies to AutoSys — Wrapper Pattern Fix

AutoSys orchestrates across SAP, Oracle, file systems, and databases in one workflow. That's the value proposition.

The mechanics are simple: special job types for SAP ('s') and Oracle EBS ('o'), plus regular CMD jobs for sqlplus. But the failure modes are not obvious.

sqlplus exits 0 even when your SQL has a syntax error. SAP XBP users expire and jobs silently wait. Oracle EBS concurrent programs return 'Success' even when the program logic failed. This article covers the integration patterns and the gotchas that don't appear in the docs.

Why sqlplus Exit 0 Is a Silent Saboteur in AutoSys SAP/Oracle Jobs

AutoSys integration with SAP and Oracle typically relies on sqlplus to run database scripts. The core mechanic: AutoSys treats exit code 0 as success. But sqlplus returns exit code 0 even when a SQL script fails — if the failure is inside the script (e.g., a PL/SQL exception caught by an exception handler, or a DML error that doesn't abort the session). This means a job can report success in AutoSys while the database operation actually failed.

In practice, sqlplus only returns non-zero for connection failures or fatal errors before script execution begins. Once the script runs, sqlplus exits 0 regardless of SQL errors unless you explicitly check SQLCODE or SQL%ROWCOUNT. This is not a bug — it's by design. The consequence: AutoSys sees exit 0, marks the job green, and downstream processes proceed with corrupted data or incomplete state.

Use this wrapper pattern when you need AutoSys to accurately reflect the true outcome of an Oracle operation. It matters in any SAP/Oracle integration where a failed SQL step must halt the workflow — financial reconciliations, inventory updates, or batch job chains. Without it, you get silent data drift that surfaces hours later as a production incident.

AutoSys and SAP — the SAPXPBP interface

AutoSys integrates with SAP R/3 and S/4HANA through the SAP XBP (External Background Processing) interface, also called SAPXPBP. This allows AutoSys to: - Submit SAP background jobs (ABAP programs, reports) - Monitor SAP job status and intercept completion events - Chain SAP jobs with non-SAP jobs in the same workflow

The SAP agent (a specialised AutoSys agent) handles the communication with the SAP application server. Behind the scenes, it uses RFC calls to the XBP function module.

Critical: The XBP user account must have specific authorisations (S_XBP_ADM, S_BTCH_ADM). If the password expires or the account locks, AutoSys jobs will stay PENDING forever with no error in AutoSys logs. The only signal is 'job not starting'.

/* SAP job type — triggers an ABAP program in SAP */
insert_job: PRD_SAP_FI_EOD_CLOSE
job_type: s                       /* 's' = SAP job type */
sap_server_name: PRDSAP           /* SAP system/instance name */
sap_report_name: RFBIBL00         /* ABAP report to run */
sap_report_variant: EOD_VARIANT   /* variant (parameter set) */
sap_client: 100                   /* SAP client number */
machine: sap-agent-server-01      /* machine with SAP agent installed */
owner: sapbatch
date_conditions: 1
days_of_week: mon-fri
start_times: "18:00"
condition: success(PRD_TRADING_LOAD_DAILY)  /* runs after trading load */
alarm_if_fail: 1
description: "SAP FI end-of-day period close"

/* SAP job with external command (optional) */
sap_external_command: Y_AUTOSYS_CALLBACK   /* RFC callback after completion */

AutoSys and Oracle — database job types

AutoSys can trigger Oracle stored procedures, SQL scripts, and Oracle E-Business Suite (EBS) concurrent programs. The approach depends on whether you're calling Oracle Database directly or Oracle ERP application layer.

For Oracle Database: The most common method is a CMD job calling sqlplus. But sqlplus has a fatal flaw: it returns exit code 0 even on SQL errors. You cannot trust its exit code alone.

For Oracle EBS: Use job_type: 'o' to submit concurrent programs directly. This sends the request to Oracle EBS concurrent manager. AutoSys tracks submission success, but not execution success. The concurrent program can fail after start and AutoSys will still show SUCCESS.

/* Method 1: CMD job calling sqlplus with proper error handling */
insert_job: PRD_ORA_RECONCILE_DAILY
job_type: CMD
command: "/usr/bin/sqlplus_wrapper.sh batchuser/pass@ORCL /scripts/oracle/reconcile.sql"
machine: db-server-01
owner: orabatch
condition: success(PRD_TRADING_LOAD_DAILY)
alarm_if_fail: 1
std_out_file: /logs/autosys/PRD_ORA_RECONCILE_DAILY.out
std_err_file: /logs/autosys/PRD_ORA_RECONCILE_DAILY.err

/* sqlplus_wrapper.sh content */
#!/bin/bash
sqlplus -s $1 @$2 > sqlplus.out 2>&1
if grep -qi 'ORA-\|SP2-\|PLS-' sqlplus.out; then
  echo "SQL error detected"
  cat sqlplus.out
  exit 1
fi
exit 0

/* Method 2: Oracle E-Business Suite concurrent program */
insert_job: PRD_ORA_EBS_PAYROLL
job_type: o                        /* 'o' = Oracle EBS job type */
oebs_server_name: PRDEBS
oebs_responsibility: GL_SUPER_USER
oebs_program_short_name: XLAFSNAPR
oebs_argument1: 2026
oebs_argument2: 03
machine: oebs-agent-server
owner: ebsbatch
alarm_if_fail: 1
std_out_file: /logs/autosys/PRD_ORA_EBS_PAYROLL.out

Practical integration patterns

The most common enterprise AutoSys flow that involves SAP and Oracle typically looks like this: 1. File Watcher detects upstream data file (trade file from external counterparty) 2. CMD jobs load data into staging database (Oracle external table or SQL*Loader) 3. Oracle stored procedure processes data, validates, transforms 4. SAP job runs the period-close or posting ABAP report (using validated data) 5. CMD job generates confirmation report and emails finance team

All five steps are orchestrated by AutoSys with dependency conditions between each step — if any step fails, everything downstream stops and the team is alerted.

Production pattern: Add a validation job after each ERP call. For SAP, check that the XBP user is active before submitting. For Oracle, verify that the stored procedure actually processed rows (check output table counts). Never assume success.

/* Complete 5-step enterprise workflow */

/* Step 1: File Watcher */
insert_job: FW_WATCH_TRADE_FILE
job_type: FW
watch_file: /data/inbound/trades_*.csv
min_file_size: 1024
machine: landing-server
run_window: "06:00 - 20:00"

/* Step 2: Load to staging */
insert_job: CMD_LOAD_STAGING
job_type: CMD
command: /scripts/load_trades_to_staging.sh
machine: db-server
condition: success(FW_WATCH_TRADE_FILE)

/* Step 3: Oracle stored procedure */
insert_job: ORA_VALIDATE_TRADES
job_type: CMD
command: /scripts/oracle_wrapper.sh @/sql/validate_trades.sql
machine: db-server
condition: success(CMD_LOAD_STAGING)
std_out_file: /logs/validate_trades.out

/* Step 4: SAP month-end close */
insert_job: SAP_MONTH_CLOSE
job_type: s
sap_server_name: PRDSAP
sap_report_name: Z_MONTH_CLOSE
sap_client: 100
machine: sap-agent
condition: success(ORA_VALIDATE_TRADES)

/* Step 5: Confirmation email */
insert_job: CMD_SEND_REPORT
job_type: CMD
command: /scripts/send_confirmation.sh
machine: mail-server
condition: success(SAP_MONTH_CLOSE)

alarm_if_fail: 1 on all jobs

The 'Dead Man's Trigger' Pattern — Why Your SAP Job Failed Silently (And How to Fix It)

Your AutoSys SAP job exits 0 but the data is trash. The warehouse report is wrong. Nobody knows until Monday. You just got burned by the dead man's trigger pattern.

SAP's CPI-C listener doesn't crash when the R/3 layer chokes. It holds the connection open and returns a clean termination code. AutoSys sees exit 0 and marks the job SUCCESS. No alert fires.

The fix is brutal but simple: wrap every SAPXPBP job with a control file that the post-processing step must validate. Before the SAP release, write a sentinel timestamp into a scratch table. When the batch completes, write another. Your downstream job checks that both exist and the delta is under 300 seconds.

Don't trust SAP's return code. Trust a handshake you control. I've seen this burn teams at three different SAP shops. On the fourth, I wrote the pattern below. It's held clean for 18 months.

// io.thecodeforge — devops tutorial

// Pre-job: write start sentinel
pre_sentinel:
  command: "sqlplus batch/sync@ORCL @write_sentinel.sql START"
  exit_codes: [0]
  timeout: 30

// Main SAP job via SAPXPBP
sap_extract:
  job_type: SAPXPBP
  sap_job_name: Z_DAILY_GEN_REPORT
  sap_server: PROD_AP_SERVER
  sap_client: "800"
  max_retries: 1
  ok_codes: [0, 1]  // trap partial exit

// Post job: validate handshake
post_sentinel:
  command: "sqlplus batch/sync@ORCL @check_sentinel_pulse.sql"
  exit_codes: [0]
  timeout: 60
  on_failure: "force_fail"

Oracle Stuck Sessions — AutoSys Can't Kill What It Can't See

Your AutoSys job times out after four hours. The Oracle session is still alive, holding a lock on S_BRANCH_TX. The next run deadlocks. Your only option is login, find the SID, kill it. That's a 3 AM wakeup.

AutoSys sees the OS child process die. But Oracle's listener is still talking to a zombie session on the DB side. The job agent reports 'cancelled by timeout', but the database server never got the signal.

Solution: embed a kill session guard in the job wrapper. Before the job starts, query v$session for any sessions with the same job name and module active for > 30 minutes. Kill them. Then run your script. On timeout, the wrapper logs the SID, captures the blocking chain, and kills it before exiting.

This pattern cut my pager duty incidents by 40% at a previous client. The wrapper is the bouncer. Never let a dead job's ghost session hold your tables hostage.

// io.thecodeforge — devops tutorial

// Pre-job cleanup — kill old sessions
pre_cleanup:
  command: |
    sqlplus -s admin/rover@pdb1 <<'EOF'
      BEGIN
        FOR r IN (
          SELECT sid, serial#
            FROM v$session
           WHERE module = 'BATCH_PAYROLL'
             AND status = 'INACTIVE'
             AND last_call_et > 1800
        ) LOOP
          EXECUTE IMMEDIATE 
            'ALTER SYSTEM KILL SESSION ''' || r.sid || ',' || r.serial# || ''' IMMEDIATE';
        END LOOP;
      END;
    /
  exit_codes: [0, 1]
  ignore_failure: true

main_job:
  command: "/scripts/run_payroll_pkg.sh"
  timeout: 3600
  on_timeout:
    - command: "sqlplus admin/rover@pdb1 @kill_blocking_sessions.sql"
    - command: "echo 'Timeout triggered. Cleanup executed.'"

Training That Doesn't Teach You How to Fail — AutoSys + SAP/Oracle Bootcamps Worth Your Time

Most training on AutoSys integration with SAP and Oracle is vendor fluff — click-ops tutorials that dodge the real failures. You don't need to know how to submit a job; you need to know why it silently died at 3 AM. That requires hands-on debugging of sqlplus exit codes, SAPXPBP interface quirks, and Oracle session kill logic.

Skip the generic AutoSys admin courses. Focus on training that includes a live SAP system with testable XPBP triggers and an Oracle RAC environment where you can reproduce stuck sessions. The best programs force you to trace job failures end-to-end — from the jil file to the database alert log — and explain the kernel-level behaviors behind each failure mode.

Look for training from Broadcom's official curriculum or vendor-neutral deep-dives from people who've run production SAP/Oracle for at least five years. If the instructor hasn't personally debugged a Dead Man's Trigger failure, walk away. The only training worth your time is the kind that breaks things first, then teaches you to fix them.

// io.thecodeforge — devops tutorial

criteria:
  - vendor: "Broadcom"
    course: "AutoSys Workload Automation for SAP"
    depth: "Covers SAPXPBP error handling, not just job submission"
  - live_lab: true
    includes:
      - Oracle RAC with stuck session scenarios
      - SAP system with intentional XPBP failure triggers
  - failure_debugging: true
    topics:
      - sqlplus exit codes beyond 0/1
      - Dead Man's Trigger pattern
      - session kill cascade failures
  - red_flags:
    - "No live Oracle database access"
    - "Only shows happy path job submission"
    - "Instructor has no production SAP/Oracle experience"

Certification That Actually Tells Recruiters You Can Fix a Dead Job — Not Just Click a GUI

The AutoSys certification market is crowded with paper certs that test your ability to memorize jil syntax. If you're integrating SAP and Oracle, you need the certification that proves you understand process chain interactions, not just job scheduling. Broadcom's AutoSys Workload Automation certification (exam 250-561) is the only one that tests on advanced scenarios like XPBP timeout handling and Oracle job type internals.

For SAP-specific credibility, pair it with the SAP Certified Application Associate — SAP S/4HANA or SAP BW/4HANA certification. That combo shows you can navigate SM37 to trace job failures and understand why an AutoSys-triggered SAP job returns a cryptic XPBP return code. For Oracle, get the Oracle Database 19c OCP. It forces you to know session management, which is exactly what you need when AutoSys gets stuck on a hanging Oracle job.

Don't waste money on generic ITIL or PMP certifications for this work. They won't help you debug a stuck Oracle session at 2 AM. The three certs that matter: Broadcom 250-561, SAP S/4HANA, and Oracle 19c OCP. Anything else is padding on your resume, not evidence of skill.

// io.thecodeforge — devops tutorial

certifications:
  - primary: "Broadcom 250-561"
    focus: "AutoSys Workload Automation — advanced SAP/Oracle job types"
    exam_topics:
      - XPBP interface failure handling
      - Oracle job type exit code analysis
      - Dead Man's Trigger configuration
  - sap: "SAP Certified Application Associate - S/4HANA"
    reason: "Can trace XPBP failures via SM37 and SM21"
  - database: "Oracle Database 19c OCP"
    why: "Teaches session management, lock troubleshooting, and kill operations"
  - skip:
    - "ITIL Foundation"
    - "PMP"
    - "Generic AutoSys Administrator (no SAP/Oracle focus)"