SQL*Plus Report Syntax and Code

========================================================

Example-1

SET PAGESIZE 30;
SET LINESIZE 256;
SET PAUSE ON
SET PAUSE ‘Please Enter Any key to Continue..’;
TTITLE CENTER ‘Employee detail Report’;
BTITLE ‘End Of the Report’;
COLUMN JOB FORMAT A3 TRUNC;
SELECT EMPNO, ENAME, JOB, HIREDATE FROM SCOTT.EMP;
TTITLE OFF;
BTITLE OFF;
CLEAR COLUMN;
SET PAGESIZE 24;
SET LINESIZE 80;

=========================================================Example-2

SET PAGESIZE 30;
SET LINESIZE 256;
SET PAUSE ON
SET PAUSE ‘Please Enter Any key to Continue..’;
TTITLE CENTER ‘Employee detail Report’ SKIP 1 –
RIGHT ‘Page_No’ FORMAT 99 SQL.PNO SKIP 2 ;
BTITLE ‘End Of the Report’;
COLUMN invoice_date FORMAT A6 TRUNC;
COLUMN invoice_num HEADING Invoice_number;
COLUMN invoice_amount FORMAT $999999.99;
select invoice_num, invoice_date, invoice_amount, amount_paid FROM ap_invoices_all where rownum < 25;
TTITLE OFF;
BTITLE OFF;
CLEAR COLUMN;
SET PAGESIZE 24;
SET LINESIZE 80;

=========================================================Example-3

SET PAGESIZE 30;
SET LINESIZE 256;
TTITLE LEFT ‘Employee detail Report’ SKIP 1 –
LEFT ‘Page_No’ FORMAT 99 SQL.PNO SKIP 2 ;
BREAK ON invoice_date SKIP 2
COMPUTE SUM LABEL ‘Total’ OF INVOICE_AMOUNT ON invoice_date;
BTITLE ‘End Of the Report’;
COLUMN invoice_num FORMAT A10 TRUNC;
COLUMN invoice_date FORMAT A6 TRUNC;
select invoice_num, invoice_date, invoice_amount, amount_paid FROM ap_invoices_all where rownum < 25;
TTITLE OFF;
BTITLE OFF;
CLEAR COLUMN;
SET PAGESIZE 24;
SET LINESIZE 80;

=========================================================Example-4

SPOOL D:\Report.csv
SET FEEDBACK OFF;
SET HEADING OFF;
SET PAGESIZE 30;
SET LINESIZE 256;
COLUMN JOB FORMAT A3 TRUNC;
SELECT ‘EMPNO.ENAME.JOB.HIREDATE’ FROM SCOTT.EMP
UNION ALL
SELECT EMPNO||’.’||ENAME||’.’||JOB||’.’||HIREDATE FROM SCOTT.EMP;
CLEAR COLUMN;
SET PAGESIZE 24;
SET LINESIZE 80;
SPOOL OFF;