How to create SQL script, include in job_list

• Article Type: General
• Product: Aleph
• Product Version: 20, 21, 22, 23

Description:
How can I create an SQL script and include it in the job_list?

Resolution:
1. Create a subdirectory in $alephe_root and "vi" the script. For example, here is $alephe_root/jerrys/test.sql which prints a list of yesterday's loans:

set echo off
set pause off
set term off
set show off
set verify off
set heading off
set feed off

spool test.lst
select to_char (sysdate-1,' "Date: "Day, Month dd yyyy') from dual;
set feed on
select z36_id, z36_rec_key from z36
where z36_loan_date = TO_CHAR (SYSDATE-1,'YYYYMMDD')
order by z36_id, z36_rec_key;
spool off
exit


2. To execute the script:

> cd $alephe_root/jerrys
> s+ usm50
SQL> @test.sql

The output will be written as test.lst in $alephe_root/jerrys.


3. To include the script in job_list:

W2 04:45:00 N testsqljs sqlplus USM50/USM50 @/exlibris/aleph/u20_2/alephe/jerrys/test.sql

The output will be written in $alephe_scratch.

For more sophisticated services see How To - Miscellaneous - Custom Services on the Doc Portal.

• Article last edited: 10/8/2013