A sample UNIX script for Oracle is as follows. The key components of the script are described below:
Note
Parameters can also be added to Scripts to facilitate deployment processes or environment changes without the need to regenerate scripts. Parameters can be added to scripts of Load and Export tables.
For example: add $P<ParameterName>$ to the script where $P$ is the parameter indicator as show below.
Before adding the Parameter to the script, create the desired parameter in Tools > Parameters> Add Parameter (context menu option).
The script makes use of two environmental variables called DSS_USER and DSS_PWD. These variables are established in the environment by the scheduler. If the script is to be executed outside of scheduler control then these two variables will need to be assigned after the LOAD_FILE variable.
The first section of the script defines the load_file variable. The second section provides a timed wait for the load file to arrive. By default, the WAITSECS variable is set to zero so that no wait occurs. This can be set to a number of seconds that the script is to wait for the file to arrive.
Once the wait has completed, either through a time expiry or through the location of the file, we check that the file is present, and if not found report back a warning. This warning can be changed to an error by changing the first echo statement to "-2". See the syntax section for more information.
When the file-based load is used instead of the script-based load, the scheduler looks for a trigger file if defined, and will exit with the specified status if the file is not found.
This section of the script is commented out by default. It is used when a trigger file is present. Such a file contains control information about the main file to be loaded and arrives after the main file to indicate that the main file transfer has completed and that it is OK to load.
This section loads the contents of the trigger file into the Parameters table, so that the table can be validated. See the section on Post Load procedures for an explanation on how trigger files are used to validate a load file.
This section calls sql*loader to load the file. It makes use of a temporary file to build as a control file and then calls sqlldr to load the data. Note that the load is actually in a for loop. Wild card file names can be used to load multiple files. Each file to be loaded must have the same format.
Note
The data being loaded is appended to the database table. As part of the scheduler run, the Load table is truncated if the property for truncation is set. In this way, multiple files can be loaded into the database table.
Tip
If this script is to be executed outside the control of the WhereScape RED scheduler, then a truncate statement may need to be performed on the database Load table. This would normally be placed before the 'for loop' and would look something like the following:
echo "truncate table load_budget_txt;" >/tmp/wsl2.sql echo "commit;" >>/tmp/wsl2.sql echo "exit" >>/tmp/wsl2.sql sqlplus $DSS_USER/$DSS_PWD @/tmp/wsl2.sql >/dev/null
Note
The first echo has a single output '>' whereas the subsequent lines have two '>>'. The first output command creates or re-creates the file, and the subsequent lines append to it. We put the output of the command to the null device as we do not want data in the output stream that does not match our syntax.
This section of the script checks the return code from the sql*loader command. Depending on the code it either identifies success, a warning or an error. In any non success situation it puts the log file out to the error stream '>&2'. This will result in the information ending up in the Detail/Error log when this script is executed through the scheduler.
This final section is largely commented out. It ends with the closing of the for loop that processes each file in a wild card file load.
The first block of commented out code renames the file and also the trigger file if appropriate. If this action is required, enter the rename path etc. and un-comment this code.
The break statement 3 lines from the end can be used if just the first file in a wild card file load is required. Simply uncomment this break statement and the script will end after the first file has been loaded.