Oracle Sql plus report example

set pages 64
set lines 80
set newpage 0
set verify off
--set termout off
set feedback off

COLUMN div_cd NOPRINT NEW_VALUE div_cd1
COLUMN gt_ps_ty NOPRINT NEW_VALUE gt_ps_var
COLUMN today NOPRINT NEW_VALUE date_var
col cc_desc format a20
col x noprint
define 1= &1
define 2= &2
define 3= &3
define 4= &4
define 5= &5
break on div_cd skip page on cc_cd skip 1 on report skip page

--spo c:\gaterep.lst
--spo /arch/gaterep.lst


ttitle center 'XXXXXX Ltd.' RIGHT date_var skip 1 -
center 'Material Gate Pass Report ' RIGHT 'Page:' FORMAT 999 SQL.PNO skip 1 -
center '~~~~~~~~~~~~~~~~~~~~~~~~~~' skip 2 -
LEFT ' Div cd : ' div_cd1 ' Gate Pass Type : ' gt_ps_var skip 1 -
LEFT ' From Date : '&3' To Date : '&4


select div_cd,CC_CD,cc_desc,MAT_OUT_DATE,
to_number(substr(gate_pass_no,5,6)) x ,
GATE_PASS_NO,EXPECTED_DATE_OF_RTRN,extend_dt_of_return,
decode(status,'NP','Not Pending','PP','Pending') Status,
-- decode(del_flag,'Y','Deleted') Del_flg,
sysdate today,
decode(gate_pass_type,'RR','Returnable',
'NR','Non-returnable') gt_ps_ty
from se_mat_gate_pass_hdr a,f_cc_mst@fin b
where div_cd='&&1'
and gate_pass_type=upper('&&2')
and a.cc_cd=b.cc_no
and trunc(gate_pass_dt) between to_date('&3','dd/mm/yyyy') and to_date('&4','dd/mm/yyyy')
and expected_date_of_rtrn< to_date('&4','dd/mm/yyyy')
and status=upper('&&5')
and nvl(extend_dt_of_return,'01-JAN-70')--this case extend_dt_of_return is null so we have converted null to some fictitious ---- date which is not exist in this column
and del_flag='N'
order by 1,2,5
/
--spo off
clear breaks
set head on
set verify on
set termout on
undefine 1
undefine 2
undefine 3
undefine 4
undefine 5
-- exit


to run this report ....

@asmtgrep PA RR 12/03/2003 12/02/2004 PP