ORACLE PLSQL - Report Formatting

Changing a Column Heading

COLUMN LAST_NAME        HEADING 'LAST NAME'
COLUMN SALARY           HEADING 'MONTHLY SALARY'
COLUMN COMMISSION_PCT   HEADING COMMISSION
SELECT LAST_NAME, SALARY, COMMISSION_PCT  FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN';
LAST NAME                 MONTHLY SALARY COMMISSION
------------------------- -------------- ----------
Russell                            14000         .4
Partners                           13500         .3

Splitting a Column Heading

COLUMN SALARY HEADING 'MONTHLY|SALARY'
COLUMN LAST_NAME HEADING 'LAST|NAME'

LAST                         MONTHLY
NAME                          SALARY COMMISSION
------------------------- ---------- ----------
Russell                        14000         .4
Partners                       13500         .3

Setting the Underline Character

SET UNDERLINE =

/

LAST                         MONTHLY

NAME                          SALARY COMMISSION

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

Russell                        14000         .4

Partners                       13500         .3

Formatting a NUMBER Column

COLUMN SALARY FORMAT $99,990

LAST                       MONTHLY

NAME                        SALARY COMMISSION

------------------------- -------- ----------

Russell                    $14,000         .4

Partners                   $13,500         .3

Formatting a Character Column

LAST  MONTHLY

NAME   SALARY COMMISSION

---- -------- ----------

Russ  $14,000         .4

ell

Resetting Column Display Attributes to their Defaults

CLEAR COLUMNS

Suppressing Duplicate Values in a Break Column

BREAK ON DEPARTMENT_ID;

BREAK ON DEPARTMENT_ID SKIP 1

SELECT DEPARTMENT_ID, LAST_NAME, SALARY  

FROM EMP_DETAILS_VIEW

WHERE SALARY > 12000 

ORDER BY DEPARTMENT_ID;

DEPARTMENT_ID LAST_NAME                     SALARY

------------- ------------------------- ----------

           20 Hartstein                      13000

           80 Russell                        14000

              Partners                       13500

           90 King                           24000

              Kochhar                        17000

Combining Spacing Techniques

BREAK ON DEPARTMENT_ID SKIP PAGE ON JOB_ID SKIP 1

TTITLE COL 35 FORMAT 9 'Page:' SQL.PNO

Page: 1
DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------
           20 MK_MAN     Hartstein                      13000

Page: 2
DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------
           80 SA_MAN     Russell                        14000
                         Partners                       13500

Computing and Printing Subtotals

break on DEPARTMENT_ID page  nodup on JOB_ID skip 1 nodup

COMPUTE SUM OF SALARY ON DEPARTMENT_ID




DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------
           20 MK_MAN     Hartstein                      13000
************* **********                           ----------
sum                                                     13000

DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------
           80 SA_MAN     Russell                        14000
                         Partners                       13500

************* **********                           ----------
sum                                                     27500




Example

COLUMN DUMMY NOPRINT;
COMPUTE SUM OF SALARY ON DUMMY;
BREAK ON DUMMY SKIP 1;
SELECT DEPARTMENT_ID DUMMY,DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW WHERE SALARY>12000 ORDER BY DEPARTMENT_ID;



DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           20 Hartstein                      13000
                                        ----------
                                             13000

           80 Russell                        14000
           80 Partners                       13500
                                        ----------
                                             27500

           90 King                           24000
           90 Kochhar                        17000
           90 De Haan                        17000
                                        ----------
                                             58000








Computing Multiple Summary Lines on the Same Break Column



BREAK ON DEPARTMENT_ID
COMPUTE AVG SUM OF SALARY ON DEPARTMENT_ID

SELECT DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW WHERE DEPARTMENT_ID = 30
ORDER BY DEPARTMENT_ID, SALARY;




DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           30 Colmenares                      2500
              Himuro                          2600
              Tobias                          2800
              Baida                           2900
              Khoo                            3100
              Raphaely                       11000
*************                           ----------
avg                                           4150
sum                                          24900








Listing and Removing COMPUTE Definitions



CLEAR BREAKS

CLEAR COMPUTES


Setting the Top and Bottom Titles and Headers and Footers

TTITLE ON
BTITLE ON


TTITLE CENTER - "ACME SALES DEPARTMENT PERSONNEL REPORT"
BTITLE CENTER "COMPANY CONFIDENTIAL"
REPHEADER PAGE CENTER 'PERFECT WIDGETS'
RIGHT 'SALES DEPARTMENT' SKIP 2
SET LINESIZE 60

ACME SALES DEPARTMENT PERSONNEL REPORT
                            PERFECT WIDGETS

                         COMPANY CONFIDENTIAL

                ACME SALES DEPARTMENT PERSONNEL REPORT
DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           30 Colmenares                      2500
           30 Himuro                          2600
           30 Tobias                          2800
           30 Baida                           2900
           30 Khoo                            3100
           30 Raphaely                       11000

                         COMPANY CONFIDENTIAL

Setting Page Dimensions

To set the number of lines between the beginning of each page and the top title, use the NEWPAGE variable of the SET command:
SET NEWPAGE number_of_lines




To set the number of lines on a page, use the PAGESIZE variable of the SET command:
SET PAGESIZE number_of_lines




You can change the line width using the LINESIZE variable of the SET command:
SET LINESIZE number_of_characters




SET PAGESIZE 66
SET NEWPAGE 0
SET LINESIZE 70



Creating a Flat File
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF SPOOL OFF

To store the results of a query in a file—and still display them on the screen—enter the SPOOL command in the following form:

SPOOL file_name


Sending Query Results to a Printer
First, use EDIT to create the script with your operating system text editor.


EDIT EMPRPT

Next, enter the following commands into the file, using your text editor:


SPOOL TEMP
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN DEPARTMENT_ID HEADING DEPARTMENT
COLUMN LAST_NAME HEADING 'LAST NAME'
COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999

BREAK ON DEPARTMENT_ID SKIP 1 ON REPORT
COMPUTE SUM OF SALARY ON DEPARTMENT_ID
COMPUTE SUM OF SALARY ON REPORT

SET PAGESIZE 24
SET NEWPAGE 0
SET LINESIZE 70

TTITLE CENTER 'A C M E  W I D G E T' SKIP 2 -
LEFT 'EMPLOYEE REPORT' RIGHT 'PAGE:' -
FORMAT 999 SQL.PNO SKIP 2
BTITLE CENTER 'COMPANY CONFIDENTIAL'

SELECT DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000
ORDER BY DEPARTMENT_ID;

SPOOL OFF

If you do not want to see the output on your screen, you can also add SET TERMOUT OFF to the beginning of the file and SET TERMOUT ON to the end of the file.

run the script EMPRPT:


@EMPRPT

SQL*Plus displays the output on your screen (unless you set TERMOUT to OFF), and spools it to the file TEMP:


A C M E  W I D G E T

EMPLOYEE REPORT                                              PAGE: 1

DEPARTMENT LAST NAME                 MONTHLY SALARY
---------- ------------------------- --------------
        20 Hartstein                        $13,000
**********                           --------------
sum                                         $13,000

        80 Russell                          $14,000
           Partners                         $13,500
**********                           --------------
sum                                         $27,500

        90 King                             $24,000
           Kochhar                          $17,000
           De Haan                          $17,000
**********                           --------------
sum                                         $58,000

                                     --------------
sum                                         $98,500
                         COMPANY CONFIDENTIAL




Ref: http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b12170/ch7.htm