SQLPLUS – HELP

Formatting Reports in SQL Statements

#Splitting Column Heading

COLUMN SALARY HEADING 'MONTHLY|SALARY'

Example:

LAST                         MONTHLY

NAME                          SALARY COMMISSION

#Changing 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';

#Setting UnderLing Character

SET UNDERLINE =

/

Example:

LAST                         MONTHLY

NAME                          SALARY COMMISSION

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

#Resetting Column’s Format

COLUMN column_name CLEAR

Or

CLEAR COLUMNS

#Formatting a NUMBER Column

Syntax: COLUMN column_name FORMAT model

Exmple:

COLUMN SALARY FORMAT $99,990

COLUMN SALARY FORMAT 99,990

The default width and format of unformatted DATE columns in SQL*Plus is determined by the database NLS_DATE_FORMAT parameter. Otherwise, the default format width is A9

#Inserting Space when a Break Column's Value Changes

BREAK ON DEPARTMENT_ID SKIP 1

Example Output:

/

DEPARTMENT_ID LAST_NAME                     SALARY ------------- ------------------------- ----------            20 Hartstein                      13000            80 Russell                        14000               Partners                       13500

#Inserting Space after Every Row

BREAK ON ROW SKIP n

#To skip a page after every row, use

BREAK ON ROW SKIP PAGE

#Turn off the top title display without changing its definition

TTITLE OFF

#To set the page size to 66 lines, clear the screen (or advance the printer to a new sheet of paper) at the start of each page, and set the line size to 70, enter the following commands:

SET PAGESIZE 66

SET NEWPAGE 0

SET LINESIZE 70

#To list the current values of these variables, use the SHOW command:

SHOW PAGESIZE

SHOW NEWPAGE

SHOW LINESIZE

#Storing and Printing Query Results

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

SQL*Plus continues to spool information to the file until you turn spooling off, using the following form of SPOOL:

SPOOL OFF

#Creating Flat File
To create a flat file with SQL*Plus, you first must enter the following SET commands:

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

After entering these commands, you use the SPOOL command as shown in the previous section to create the flat file.

#Running Scripts

To retrieve and run the command stored in SALES.SQL, enter

@SALES

Or

START SALES

System Variable	Affect on Substitution Variables
SET CONCAT	Defines the character that separates the name of a substitution variable or parameter from characters that immediately follow the variable or parameter—by default the period (.).
SET DEFINE	Defines the substitution character (by default the ampersand "&") and turns substitution on and off.
SET ESCAPE	Defines an escape character you can use before the substitution character. The escape character instructs SQL*Plus to treat the substitution character as an ordinary character rather than as a request for variable substitution. The default escape character is a backslash (\).
SET NUMFORMAT	Sets the default format for displaying numbers, including numeric substitution variables.
SET NUMWIDTH	Sets the default width for displaying numbers, including numeric substitution variables.
SET VERIFY ON	Lists each line of the script before and after substitution.

#To put titles at the top and bottom of each page of a report, enter

TTITLE CENTER -

"ACME SALES DEPARTMENT PERSONNEL REPORT"

BTITLE CENTER "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

6 rows selected.