There are a number of conventions that must be followed if a host script is to be used by the WhereScape scheduler. These conventions are:
- The first line of data in 'standard out' must contain the resultant status of the script. Valid values are '1' to indicate success, '-1' to indicate a warning condition occurred but the result is considered a success, '-2' to indicate a handled error occurred and subsequent dependent tasks should be held, -3 to indicate an unhandled Failure and that subsequent dependent tasks should be held.
- The second line of data in 'standard out' must contain a resultant message of no more than 256 characters.
- Any subsequent lines in 'standard out' are considered informational and are recorded in the audit trail. The normal practice is to place a minimum of information in the audit trail. All bulk information should be output to 'standard error'.
- Any data output to 'standard error' is written to the error/detail log. Both the audit log and detail log can be viewed from the WhereScape RED tool under the Scheduler window.
Windows Example for Oracle:
In the following example, the first line '@echo off' prevents unwanted information from being reported to standard out. A Sql*loader script file is built up (echo statements). The sqlldr command is then executed with the 'silent-header,feedback' option once again to prevent unwanted output.
@echo off echo load data >c:\temp\wsl1.ctl echo infile 'C:\Temp\budget.txt' >>c:\temp\wsl1.ctl echo badfile 'c:\temp\wsl1.bad' >>c:\temp\wsl1.ctl echo into table load_budget_txt >>c:\temp\wsl1.ctl echo fields terminated by "," >>c:\temp\wsl1.ctl echo optionally enclosed by '^"' >>c:\temp\wsl1.ctl echo trailing nullcols >>c:\temp\wsl1.ctl echo ( >>c:\temp\wsl1.ctl echo product_code,>>c:\temp\wsl1.ctl echo customer_code,>>c:\temp\wsl1.ctl echo budget_quantity,>>c:\temp\wsl1.ctl echo budget_sales_value,>>c:\temp\wsl1.ctl echo budget_date date 'dd-mon-yyyy'>>c:\temp\wsl1.ctl echo ) >>c:\temp\wsl1.ctl sqlldr userid=dssadm/wsl@ORCL control=c:\temp\wsl1.ctl skip=1 silent=header,feedback log=c:\temp\wsl1.log goto answer%errorlevel% :answer0 echo 1 echo Load completed successfully type c:\temp\wsl1.log >&2 exit :answer2 echo -1 echo Not all rows loaded. See error trail type c:\temp\wsl1.log >&2 exit :answer1 echo -2 echo SQL*Loader execution exited with EX_FAIL, see error trail type c:\temp\wsl1.log >&2 exit :answer3 echo -3 echo SQL*Loader execution encountered a fatal error type c:\temp\wsl1.log >&2 exit