-- report_skeleton.sql		Report_Skeleton_Description

--**Use this information and these scripts at your own risk. As a condition of using these scripts and information from this site, you agree to hold harmless both the University of Arkansas Cooperative Extension Service and Bruce Knox for any problems that they may cause or other situations that may arise from their use, and that neither the Extension Service nor I will be held liable for those consequences. The scripts and information are provided "as is" without warranty, implied or otherwise. Limitation of liability will be the amount paid to the University of Arkansas specifically for this information. (It was free:) 

SET ECHO OFF

-- change log
-- mm-dd-yy USER created using UNIX script report (Report Generator by bknox@uaex.edu)

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';

-- Set Printer Defaults - will be modified by get user's printer specs
SET NEWPAGE 0
SET PAGESIZE 43
DEFINE prnt_code = 'hp4000'
DEFINE prnt_form = 'Landscape'
DEFINE line_count = 43
DEFINE prnt_margin = .25

SET SCAN ON
-- get user's printer specs -- begin

   !/bin/clear
   !sed "s/string/report_skeleton/g" getprinter.sql > report_skeleton.rpt
   START report_skeleton.rpt
   !rm -f report_skeleton.rpt
   !echo
   !echo "Formatted for:"
   !echo
   !echo "    Printer: "&&prnt_code
   !echo "Orientation: "&&prnt_form
   !echo " Line Count: "&&line_count
   !echo "All Margins: "&&prnt_margin" inch"
   SET PAGESIZE &&line_count

-- get user's printer specs -- end

!echo " Paper Size: Letter"
SET LINESIZE 128

SET SHOWMODE OFF
SET TIMING OFF
SET FEEDBACK OFF
SET HEADING ON
SET TRIMSPOOL ON
SET VERIFY OFF
SET TERMOUT ON

-- SET NEWPAGE 0
-- SET ECHO OFF     Suppress Display of commands
-- SET FEEDBACK OFF Suppress Display of record counts
-- SET LINESIZE n   Set line length
-- SET PAGESIZE n   Set lines per page
-- SET SHOWMODE OFF Suppress Display of old and new setting of SQL*Plus system variables
-- SET NEWPAGE  0   Set no lines before TITLE and eject first page
-- SET TRIMSPOOL ON Trim trailing spaces from end of SPOOLed record
-- SET VERIFY   ON  Show each line of the file before and after substitution
-- SET TERMOUT  OFF Suppress Display of output

CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

--DEBUG
-- SET ECHO ON
-- SET SHOWMODE ON
-- SET FEEDBACK ON
-- SET VERIFY ON
-- SET TERMOUT ON
--DEBUG

-- Accept run options from operator - place holder

-- Time and Date

COLUMN today  NOPRINT NEW_VALUE datevar FORMAT A18 TRUNC
COLUMN time   NOPRINT NEW_VALUE timevar FORMAT A11 TRUNC
SELECT TO_CHAR(SYSDATE,'fmMonth DD, yyyy') today,
       TO_CHAR(SYSDATE,'HH:MI:SS am') time FROM DUAL;

-- use current date to get asofdate
COLUMN asofdate NOPRINT NEW_VALUE asofdate
SELECT TO_CHAR(SYSDATE, 'MM/DD/YYYY') asofdate FROM DUAL;

COLUMN asofdatealpha NOPRINT NEW_VALUE asofdatealphavar FORMAT A18 TRUNC
SELECT TO_CHAR(TO_DATE('&&asofdate', 'MM/DD/YYYY'), 'fmMonth DD, yyyy') asofdatealpha FROM DUAL;

COLUMN ces    NOPRINT NEW_VALUE cesvar
SELECT RTRIM(gubinst_name) ces FROM general.gubinst;

COLUMN DB_Instance NEW_VALUE DB_Instance FORMAT A11 TRUNC
SELECT SUBSTR(global_name,1,instr(global_name,'.')-1) DB_Instance
  FROM global_name;

!echo 'DB Instance: ' &&DB_Instance

SET NUMWIDTH 10

TTITLE ON
BTITLE ON

SET TERMOUT OFF

--                                           COL 56 | IS CENTER
TTITLE LEFT 'Report report_skeleton'         COL 42 cesvar -
       SKIP 2 LEFT 'DB Instance: ' DB_Instance COL 43                   'Report_Skeleton_Description' -
       SKIP 2

BTITLE LEFT SKIP 1 ' Run: ' datevar '  ' timevar COL 63 ' Page No.:  ' FORMAT 999 SQL.PNO SKIP 1

!rm -f report_skeleton.lst

!echo
!echo report_skeleton report beginning
!echo

SPOOL report_skeleton

SELECT 'Replace this place holder SELECT with your code' FROM DUAL;

SPOOL OFF

TTITLE OFF
BTITLE OFF
CLEAR COLUMNS
CLEAR BREAK
CLEAR COMPUTE
SET NUMWIDTH 10
SET HEADING ON

!echo
!echo report_skeleton report complete
!echo

-- Reset Date Format to Banner Standard
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YY';

-- This script is available "as is".  See Disclaimer http://www.uaex.edu/bknox/
SET TERMOUT ON