How to Manage an Oracle database with SQLPlus?

Oracle database with SQLPlus

For Oracle Database developers and administrators, SQLPlus is a vital command-line tool that provides powerful features for data extraction and database management. In addition to its core function of running SQL statements, SQLPlus offers strong capabilities for scripting job automation, saving query results to a file, and customising output to generate prepared reports.

SQL*Plus Commands for Formatting

As an interactive report writer, SQL*Plus gives users control over details like column headings, titles, and text and numeric formatting. It is possible to accomplish this capability with a few precise commands.

Setting Page and Line Dimensions

Two fundamental commands govern the overall layout of your report output: SET LINESIZE and SET PAGESIZE.

SET LINESIZE: This command dictates the maximum number of characters permitted on a single line of the report. For standard letter-size paper, typical LINESIZE values range from 70 to 80 characters. If the data from your SQL query exceeds the set LINESIZE, SQL*Plus will wrap the additional columns onto the subsequent line, stacking them beneath each other. Importantly, LINESIZE also determines the centring of the top title (TTITLE) and the positioning of the date and page number in the report header. The distance between the initial character of the date and the final digit of the page number will always align with the LINESIZE you’ve specified.

SET PAGESIZE: This command controls the total number of lines SQL*Plus will display on each page, encompassing the top title, bottom title, column headings, and any blank lines. A common PAGESIZE for letter- or computer-size paper is 66 lines. As of Oracle Database 10g, the default PAGESIZE is 14, whereas in earlier versions it was 24. PAGESIZE works in conjunction with SET NEWPAGE, which specifies the number of blank lines preceding the top line of each page. Setting NEWPAGE to 0 introduces a top-of-form character, often used by modern printers to advance to the next page, providing a cleaner page control.

Report Titles

TTITLE (Top Title): The TTITLE command instructs SQLPlus to place a specified title at the top of every report page. The chosen title must be enclosed in single quotation marks. SQLPlus automatically centres this title based on the current LINESIZE. Additionally, it includes the weekday, month, and day the report was run in the upper-left corner, and the page number in the upper-right corner. You can create multi-line titles by embedding the headsep character (default |) within the title string.
BTITLE (Bottom Title): Similar to TTITLE, the BTITLE command positions a specified title at the bottom of each report page. Like TTITLE, the title text must be enclosed in single quotation marks.

Column Formatting

The COLUMN command provides SQL*Plus with a variety of guidelines on the heading, structure, and overall treatment of a column in the report.

FORMAT: This sub-command defines the display width and data type format for a column. For alphabetic (character) columns, you use a followed by the desired width (e.g., a20). For numeric columns, you use 9s to represent digits, a decimal point . for precision, and commas , for thousands separators (e.g., 999.99 or $999,999.99). By default, numeric column headings are right-justified, while character column headings are left-justified.
HEADING: This sub-command relabels the column heading that appears in the report. You can also use the headsep character within the HEADING text to create multi-line column headings.
WORD_WRAPPED: This option, typically used with FORMAT for character columns, ensures that if a column’s text content exceeds its defined display width, it will wrap onto subsequent lines within that column’s allocated space.

Grouping and Calculations

BREAK ON: This command enables grouping of rows and inserting blank lines between sections of a report. It can also be used to define sections for subtotal calculations. To ensure logical grouping, BREAK ON must be coordinated with the ORDER BY clause in your SELECT statement. By default, the breaking column’s value is only printed on the first line of its section to avoid visually unattractive duplication.
COMPUTE: Used in conjunction with BREAK ON, the COMPUTE command allows SQL*Plus to calculate aggregate functions (such as AVG, SUM, COUNT, MAX, MIN, STDDEV, VARIANCE) for specified sections of the report. The calculations are performed for the section defined by the BREAK ON command.

Scripts for SQL*Plus to Automate Tasks

You frequently need to run multiple SQL statements consecutively, and they can be quite long. SQLPlus scripts offer a way to create automated procedures for this. A text file with a common .sql extension that contains a number of SQL statements and SQLPlus commands is called a SQLPlus script. Because these scripts function similarly to programs, you can carry out intricate tasks repeatedly without having to type commands again. Automated SQLPlus scripts are used for the maintenance of many Oracle databases.

Any text editor, or even the built-in line editor in SQL*Plus, can be used to create these files.

Executing Scripts

START, or its abbreviation @, is used to start a SQL*Plus script.

The @@ command looks for the script in the same directory as the calling file and launches a nested SQL*Plus script inside another command file.along with

To set up your environment, SQL*Plus enables specific scripts that run automatically upon startup.

login.sql: If this file cannot be located in the current directory where SQLPlus is being launched, it is then looked for in the Oracle home directory. It’s perfect for customising your SQLPlus environment using popular layouts like SET PAGESIZE, SET LINESIZE, SET FEEDBACK OFF, and SET SQLPROMPT.
glogin.sql: This “global” login script is usually run before login.sql and is kept in the administrative directory for SQLPlus. It is employed to create default SQLPlus configurations for every user of a database, guaranteeing uniform column and environment configurations for numerous users.

Spooling Output

A SQLPlus session’s output can be redirected and saved to a file using the SPOOL command. This covers the commands you input, SQLPlus prompts, query results, and any error warnings. The word “spooling” was first used to describe the early computer file storage on magnetic tape spools, and it today commonly refers to the transfer of data between locations.

Starting Spooling: The SPOOL command requires a filename to start spooling.
Terminating Spooling: The SPOOL OFF command is used to terminate output saving and end the file.
Strategic Placement: SPOOL OFF should be used right after the SPOOL command, which should be strategically positioned in a script, usually right before the SELECT statement whose results you wish to collect. By doing this, the report file is guaranteed to contain only the prepared report output and not the SQLPlus commands themselves. The output file would also contain the SQLPlus commands you issue if SPOOL shows up too early.
Managing Existing Files: Oracle Database 10g allows you to use the SPOOL command with the APPEND or REPLACE parameters.
- To an existing file, the APPEND option adds new output.
- The new output is overwritten in an existing file when the REPLACE option is selected.
- By default (and in previous versions as well), SPOOL overwrites any existing output file with the same name with a new one.

Example: Customising Output and Spooling with the NEWSPAPER Table

Let’s illustrate these concepts using a sample NEWSPAPER table.

Create Table and Insert Values

First, we create the NEWSPAPER table and populate it with some data:

-- Create the NEWSPAPER table as described
CREATE TABLE NEWSPAPER (
    Feature VARCHAR2(15) NOT NULL,
    Section CHAR(1),
    Page NUMBER
);
-- Table created.

-- Insert values into the NEWSPAPER table 
INSERT INTO NEWSPAPER VALUES ('National News', 'A', 1);
INSERT INTO NEWSPAPER VALUES ('Sports', 'D', 1);
INSERT INTO NEWSPAPER VALUES ('Editorials', 'A', 12);
INSERT INTO NEWSPAPER VALUES ('Business', 'E', 1);
INSERT INTO NEWSPAPER VALUES ('Weather', 'C', 2);
INSERT INTO NEWSPAPER VALUES ('Television', 'B', 7);
INSERT INTO NEWSPAPER VALUES ('Births', 'F', 7);
INSERT INTO NEWSPAPER VALUES ('Classified', 'F', 8);
INSERT INTO NEWSPAPER VALUES ('Modern Life', 'B', 1);
INSERT INTO NEWSPAPER VALUES ('Comics', 'C', 4);
INSERT INTO NEWSPAPER VALUES ('Movies', 'B', 4);
INSERT INTO NEWSPAPER VALUES ('Bridge', 'B', 2);
INSERT INTO NEWSPAPER VALUES ('Obituaries', 'F', 6);
INSERT INTO NEWSPAPER VALUES ('Doctor Is In', 'F', 6);
COMMIT;
-- 14 rows created.
-- Commit complete.

SQL*Plus Script for Formatted Output and Spooling ()

Now, let’s create a SQL*Plus script to format the output of a query against this NEWSPAPER table and save it to a file.

-- File: newspaper_report.sql
-- Customising Output and Spooling Example Script

-- Disable echoing commands, verification messages, and feedback for cleaner output
SET ECHO OFF
SET VERIFY OFF
SET FEEDBACK OFF

-- Set line and page dimensions 
SET LINESIZE 80
SET PAGESIZE 20
SET NEWPAGE 0 -- Start each page with a form feed character for printers 

-- Set top title (TTITLE) with date and centre alignment 
TTITLE CENTER 'DAILY NEWSPAPER FEATURES REPORT' SKIP 1 CENTER 'As of &SYSDATE'

-- Set bottom title (BTITLE) 
BTITLE 'Confidential Report'

-- Format columns: heading, width, and word wrapping 
COLUMN Feature FORMAT A18 HEADING 'Article|Feature' WORD_WRAPPED
COLUMN Section FORMAT A7 HEADING 'Sect.'
COLUMN Page FORMAT 999 HEADING 'Pg.'

-- Break output on Section and add a blank line between sections 
BREAK ON Section SKIP 1

-- Start spooling output to a file named newspaper_report.lst 
SPOOL newspaper_report.lst

-- Select data from NEWSPAPER table, ordered by Section and Page 
SELECT Feature, Section, Page
FROM NEWSPAPER
ORDER BY Section, Page;

-- Stop spooling output
SPOOL OFF

-- Reset SQL*Plus settings to their defaults or previous values
SET ECHO ON
SET VERIFY ON
SET FEEDBACK ON
SET PAGESIZE 14
SET NEWPAGE 1
TTITLE OFF
BTITLE OFF
CLEAR COLUMNS
CLEAR BREAKS

To run this script in SQL*Plus, you would simply type: SQL> @newspaper_report.sql

Example Spooled Output ()

The newspaper_report.lst file would contain output similar to this, demonstrating the applied formatting:

Mon Aug 12                                                page    1
                  DAILY NEWSPAPER FEATURES REPORT
                       As of 12-AUG-24

Article            Sect.   Pg.
------------------ ------- ---
National News      A         1
Editorials         A        12

Television         B         7
Modern Life        B         1
Movies             B         4
Bridge             B         2

Comics             C         4
Weather            C         2

Sports             D         1

Business           E         1

Births             F         7
Classified         F         8
Obituaries         F         6
Doctor Is In       F         6
Confidential Report

This example illustrates how SQL*Plus commands enable comprehensive control over report presentation, making it a powerful tool for generating readable and well-structured reports directly from the database and saving them for later use.

Page Content

Tutorials