Advanced 3 min · March 19, 2026

sqlplus Exit 0 Lies to AutoSys — Wrapper Pattern Fix

sqlplus returns exit 0 on SQL syntax errors while your database sits unchanged for months.

Naren · Founder

Plain-English first. Then code. Then the interview question.

⚡Quick Answer

SAP integration uses job_type: s and the SAPXPBP interface. Requires XBP user with S_XBP_ADM and S_BTCH_ADM authorisations.
Oracle EBS integration uses job_type: o to submit concurrent programs. Requires EBS agent and concurrent manager setup.
Oracle Database integration uses CMD + sqlplus. The trap: sqlplus exits 0 even on SQL errors. Add 'WHENEVER SQLERROR EXIT SQL.SQLCODE'.
The 5-step pattern: File Watcher → Database load → Stored proc → SAP job → Email report. AutoSys orchestrates across all systems.
Production failure: SAP XBP user locked after password expiry. Jobs stay PENDING. No error in AutoSys logs. Always involve Basis team in user management.

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.

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

thecodeforge.io

SAP and Oracle Integration

Autosys Integration Sap Oracle

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.

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.

ERP Integration Methods — At a Glance

Integration target	Job type code	What AutoSys can do	Failure detection	Prerequisite
SAP R/3 / S/4HANA	s (SAP)	Submit ABAP jobs, monitor completion via RFC	Silent — PENDING if XBP user fails	SAP agent + XBP user with S_XBP_ADM
Oracle EBS	o (Oracle)	Submit concurrent programs, monitor request ID	Partial — tracks submission, not execution	Oracle EBS agent installed
Oracle Database (sqlplus)	CMD (sqlplus)	Run SQL scripts, call stored procs	Broken — sqlplus returns 0 on errors	sqlplus client + wrapper script
Oracle Database (wrapper)	CMD (custom)	Run SQL with error checking	Full — exits non-zero on ORA-	Wrapper script + AutoSys std_out capture

Key Takeaways

SAP jobs (job_type: s) need XBP user with S_XBP_ADM. Locked user = PENDING forever. No error in AutoSys.
sqlplus wrapper is MANDATORY. Never call sqlplus directly. Check output for ORA- patterns and exit non-zero.
Oracle EBS job_type: 'o' tracks submission, not execution. Check concurrent manager logs separately.
Add validation jobs after every ERP call. Verify row counts, business keys, and completion statuses.
Capture stdout/stderr for all ERP integration jobs. That's where the real errors live.
Cross-system failures cascade silently. Each integration point needs independent error detection.

Common Mistakes to Avoid

Calling sqlplus directly without error checking wrapper
Symptom: AutoSys shows SUCCESS, but database didn't change. SQL errors are printed to stdout/stderr but ignored. Data inconsistency propagates for days or months.
Fix: Always wrap sqlplus in a shell script that grep's for ORA- and SP2- patterns. Make the wrapper exit non-zero on any database error. Enforce wrapper usage in code review.
Not coordinating SAP XBP user lifecycle with Basis team
Symptom: SAP jobs suddenly stop running. AutoSys shows PENDING. No errors in event_demon.log. The issue is an expired password or locked XBP user in SAP.
Fix: Set XBP user password to never expire (secure password with rotation via credential manager). Add monthly check with Basis team. Monitor PENDING duration as alert trigger.
Assuming Oracle EBS concurrent program success means business logic succeeded
Symptom: AutoSys shows SUCCESS. Concurrent program submitted. The program executed but failed internally (missing data, permission error). AutoSys has no visibility.
Fix: Add post-execution validation job that checks for expected outcomes in Oracle EBS (e.g., check request log for 'Completed with errors', verify output tables updated).
Not capturing sqlplus stdout/stderr in AutoSys files
Symptom: Job fails or succeeds incorrectly. No output to review. Engineer has to rerun job manually to see the error.
Fix: Always set std_out_file and std_err_file in JIL for sqlplus jobs. Capture everything. These files are the first place to look for ORA- errors.
Chaining SAP job directly after Oracle job without validation
Symptom: Oracle job 'succeeded' but processed 0 rows due to upstream empty file. SAP runs anyway on empty data set. Business process continues with missing data.
Fix: Add a validation job between Oracle and SAP that checks row count or business key existence. Only trigger SAP if validation passes.

Interview Questions on This Topic

QHow does AutoSys integrate with SAP?Mid-levelReveal
AutoSys integrates with SAP R/3 and S/4HANA through the SAP XBP (External Background Processing) interface using job_type: 's' in JIL. A specialised SAP agent on the AutoSys side communicates with SAP via RFC/BAPI calls to submit ABAP background jobs and monitor their completion. Prerequisites: SAP agent installed, XBP user with S_XBP_ADM and S_BTCH_ADM authorisations, RFC destination configured in SAP (SM59). Failure mode: If XBP user password expires or account locks, AutoSys jobs stay PENDING indefinitely with no error in AutoSys logs. Requires SAP Basis team intervention to resolve.
QWhat is the SAP XBP interface?SeniorReveal
XBP (External Background Processing) is an SAP interface that allows external schedulers like AutoSys to submit and monitor background jobs in SAP systems. It consists of function modules (XBP_* functions) exposed via RFC. AutoSys uses XBP to: - Submit ABAP programs with variants - Get job status and completion events - Retrieve spool output and job logs The XBP user requires specific authorisations (S_XBP_ADM for interface access, S_BTCH_ADM for background job management). The RZ11 profile parameter 'bgxbp_allowed' must be set to enable XBP in SAP.
QWhat job type code is used for SAP jobs in JIL?JuniorReveal
job_type: s (lowercase 's') for SAP background jobs. Example JIL snippet: ``insert_job: SAP_MONTH_CLOSE job_type: s sap_server_name: PRDSAP sap_report_name: Z_MONTH_CLOSE sap_report_variant: STANDARD sap_client: 100 machine: sap-agent-server`` Other ERP job types: 'o' for Oracle EBS concurrent programs, 'c' for PeopleSoft (legacy), and standard 'CMD' for database sqlplus calls.
QHow do you call an Oracle stored procedure from AutoSys?Mid-levelReveal
Primary method: CMD job calling sqlplus with a wrapper script. Example: ``bash #!/bin/bash sqlplus -s user/pass@DB <<EOF > sqlplus.out 2>&1 WHENEVER SQLERROR EXIT SQL.SQLCODE; BEGIN my_stored_proc(:param1, :param2); COMMIT; END; / EXIT; EOF if grep -qi 'ORA-' sqlplus.out; then echo "Database error detected" cat sqlplus.out exit 1 fi exit 0`` Critical: sqlplus returns exit code 0 even on SQL errors. Always wrap it with error detection. The wrapper must grep for ORA-, SP2-, PLS- patterns and exit non-zero when found.
QWhat is the gotcha with sqlplus and exit codes in AutoSys?SeniorReveal
sqlplus returns exit code 0 in almost all scenarios — including SQL syntax errors, runtime errors (ORA-), and even when a script fails to parse. It only returns non-zero for fatal errors like connection failure or missing SQL file. This means an AutoSys job calling sqlplus directly will show SUCCESS even when the SQL completely fails. The job log may contain ORA- errors, but AutoSys ignores them. Fix: Never call sqlplus directly. Always wrap it in a shell script that: 1. Captures output to a file 2. Greps for error patterns (ORA-, SP2-, PLS-) 3. Exits 1 if any error pattern found 4. Exits 0 only if no errors AND row count expectations met Additionally, include WHENEVER SQLERROR EXIT SQL.SQLCODE in SQL scripts, but note this only catches runtime errors, not syntax errors.
QHow do you handle cross-system failure cascades in an AutoSys workflow?SeniorReveal
Cross-system workflows (File system → Oracle → SAP → Email) have multiple failure domains. A failure in Oracle shouldn't trigger SAP. A failure in SAP shouldn't trigger the email. Pattern: Insert validation jobs between each integration point: 1. After file load: Verify row count > 0 before triggering Oracle. 2. After Oracle stored procedure: Run SELECT COUNT(*) to confirm data processed. Fail if zero rows. 3. Before SAP job: Check that XBP user is active (via RFC ping). Check that data validation flags are set. 4. After SAP job: Check SM37 in SAP that background job completed without errors (via RFC query). 5. Before email: Verify report generation actually created output. Each validation job should have its own alarm_if_fail and clear documentation. The workflow should stop at the first validation failure, and the alert should pinpoint exactly which system failed and why.

Frequently Asked Questions

How does AutoSys integrate with SAP?

AutoSys integrates with SAP R/3 and S/4HANA through the SAP XBP (External Background Processing) interface. A specialised SAP agent on the AutoSys side communicates with SAP via RFC/BAPI calls to submit ABAP background jobs and monitor their completion.

Job type: 's' in JIL. Prerequisites: SAP agent installed, XBP user with S_XBP_ADM and S_BTCH_ADM authorisations, RFC destination configured in SAP (transaction SM59).

Failure mode: If XBP user password expires, jobs stay PENDING with no AutoSys error. Coordinate user lifecycle with SAP Basis team.

What job type do you use for SAP jobs in AutoSys JIL?

Use job_type: s (lowercase 's') for SAP jobs. This job type requires the SAP agent to be installed on the specified machine and the SAP XBP interface to be configured in the SAP system.

Attributes specific to SAP jobs include: sap_server_name (SAP system ID), sap_report_name (ABAP program name), sap_report_variant (parameter set), sap_client (client number), and optionally sap_external_command for RFC callbacks.

How do you call an Oracle stored procedure from AutoSys?

The simplest method is a CMD job that invokes sqlplus via a wrapper script. Example wrapper:

``bash #!/bin/bash sqlplus -s user/pass@DB @/scripts/call_proc.sql > sqlplus.out 2>&1 if grep -qi 'ORA-' sqlplus.out; then exit 1 fi exit 0 ``

Critical: sqlplus returns exit code 0 even on SQL errors. Never call sqlplus directly. Always wrap it with error detection that checks output for ORA- patterns.

Why does sqlplus exit 0 even when SQL fails?

By design, sqlplus returns exit code 0 for successful parsing and execution attempts, even if SQL statements inside the script fail. It only returns non-zero for fatal errors like cannot connect to database, cannot open SQL file, or out of memory.

This means an AutoSys job calling sqlplus directly will show SUCCESS even when your stored procedure raises ORA-00942 (table not found).

Fix: Add WHENEVER SQLERROR EXIT SQL.SQLCODE to SQL scripts to catch runtime errors. For syntax errors, wrap sqlplus in a shell script that greps the output for error patterns. The wrapper pattern is mandatory for production jobs.

What permissions does the SAP user need for AutoSys integration?

The SAP user (typically a technical Basis user) needs XBP authorisations including: - S_XBP_ADM (XBP Administration — allows external scheduling) - S_BTCH_ADM (Background Processing Administration — allows job submission) - S_RFC (RFC access to XBP function modules)

The exact profile should be set up by your SAP Basis team following SAP Note guidance for XBP. The user should have password never expire (or secure rotation via credential manager) and should be monitored for lock status.

How do you debug an SAP job that stays PENDING in AutoSys?

Step-by-step debugging for PENDING SAP jobs:

Check SAP agent connectivity: autoping -m sap-agent-server. If INACTIVE, agent is down or network blocked.
Check XBP user status in SAP: transaction SUIM → User → Display. Look for locked or expired password.
Check SAP system availability: from agent machine, telnet sap-app-server 3300 (default RFC port).
Check SAP job scheduling log: In SAP transaction SM37, look for the scheduled background job. If it's not there, XBP submission failed.
Check AutoSys event_demon.log: grep SAP_JOB_NAME $AUTOUSER/out/event_demon.$AUTOSERV. Look for RFC error messages.

Most common issue: XBP user password expired. Work with Basis team to reset and unlock. Add monitoring for PENDING duration > 30 minutes to alert before business impact.

COMPLETE GUIDE

The Complete AutoSys Workload Automation Guide for Engineers →

JIL syntax, sendevent, autorep, box jobs, file watchers, scheduling, HA, security, cloud workload automation, and 22 interview questions — the definitive AutoSys reference for production engineers.

🔥

That's AutoSys. Mark it forged?

3 min read · try the examples if you haven't