Oracle: Calling Oracle Procedure from AUTOSYS and Detecting Failures

Oracle provides Oracle Jobs feature to create and schedule long running Jobs to execute particular piece of SQL script at scheduled time of interval with event notifications in case of any failures and success. But in our case we were asked not to use Oracle Jobs as triggering and scheduling was responsibility of AutoSys (Workload Automation Tool) as per framework. We were using stored procedure to execute an long running job which processes million of records.

I don’t know if there is any “proper” way to configure Oracle in AutoSys but we were only aware of how to trigger long running Oracle procedure in a .NET Console Application.

While working on the script to trigger Oracle stored procedure to detect if there are any errors and let AutoSys know following errors –

  1. All “ORA-” errors returned by SQL*Plus
  2. All “SP2-” errors returned by SQL*Plus

 

Follow below steps to configure batch file –

PRE-REQUISITES –

  1. SQL*Plus is installed on App Server
  2. Required credentials to execute stored procedure successfully

USAGE –
Modify execute_script.bat (deployable) –

@echo off
sqlplus USER_NAME/******@MYTNSNAME @script.sql | findstr “ORA- SP2- ERROR”
if %errorlevel% == 0 (exit /b 1) else (exit /b 0)

 

Modify script.sql (deployable) –

begin
      Process();
end;
/
quit;

Checklist –
Replace highlighted call with required stored procedure (may need to include package name as well)
Make sure that calling procedure is raising error with RAISE statement in EXCEPTION block.
Note –
Do not remove ‘/’ and ‘quit;’ as these are required
Run Test Batch File.exe
For errors, you will see following output –


Exit Code: 1

AUTOSYS should show : Failure
Please press any key to exit !!!
For success,   you will see following output –


Exit Code: 0


AUTOSYS should show : Success
Please press any key to exit !!!

1 Comments

  1. Nice Article parag specially for the crowd who will be migrating their applictaion from SQL to Oracle and where the old autosys behaviour job needs to be compactible with the new DB.

    Reply

Leave a Comment.