Lesson 6	Setting Column Attributes
Objective	Modify Column Headings and display Width.

Setting Column Headings and Display Width in SQL*Plus

SQL*Plus is more than a command-line tool for running queries; it can also format result sets so they read like printable reports. In this lesson, you will learn how to use SQL*Plus environment commands to:

Change column headings to user-friendly labels.
Control the display width of character and numeric columns.
Improve readability using word wrapping and basic report settings.

These techniques are especially useful when you turn frequently used queries into scripts that you run on a regular basis (for example, month-end reports).

Key SQL*Plus Environment Commands

The main commands you will use to adjust headings and widths are COLUMN and SET. Together they control how each column and each line is presented in your SQL*Plus output.

SQL*Plus Environment command Description

COL[UMN] column_name options…

Defines display attributes for a column in the result set. Common options include:

HEADING text – Sets the column heading. Use double quotes to allow spaces and mixed case, for example: COLUMN product_name HEADING "Product Name"
FORMAT model – Sets the width and display format. Examples: FORMAT A15 (15-character text), FORMAT 9999.99 (numeric).
WORD_WRAPPED or WRAPPED – Wraps long text to the next line instead of truncating it.
JUSTIFY LEFT | RIGHT | CENTER – Aligns headings and data.

COLUMN settings remain in effect for the session until you change them or exit SQL*Plus.

SET attribute

Adjusts global environment settings that affect the entire report. Some commonly used attributes when formatting columns are:

`ECHO` [`OFF`\|`ON`]	Show commands from a script before executing them.
`FEED`[`BACK`] [`6`\|n\|`OFF`\|`ON`]	Controls whether “n rows selected” is displayed after a query.
`HEA`[`DING`] [`OFF`\|`ON`]	Enables or disables all column headings.
`LIN`[`ESIZE`] [`80`\|n]	Sets the number of characters per line. Important when widening columns.
`PAGES`[`IZE`] [`24`\|n]	Sets the number of printed lines per page (for paged reports).
`TERM`[`OUT`] [`OFF`\|`ON`]	Controls whether output is displayed on screen.
`TIMI`[`NG`] [`OFF`\|`ON`]	Shows the elapsed time for each completed SQL command.
`VER`[`IFY`] [`OFF`\|`ON`]	Shows the old and new values when you use substitution variables.

SHOW [ALL | attribute] Displays the current environment settings for all attributes or for a specific attribute.

With a few well-chosen COLUMN and SET commands, you can turn a wide, raw query into a compact report that fits the window or page and uses clear, business-friendly headings.

Example: Improving a Report with Column Settings

The following eight images (and their captions) illustrate a typical workflow in which a saved query is reformatted into a readable report.

1) Initial report output; BUYER column is too wide and headings are unhelpful. — 1) A saved query is executed. The **BUYER** column is so wide that it hides the third column, and the default headings are not meaningful. This is a good candidate for COLUMN formatting.

2) Open the script in a text editor to add SQL*Plus formatting commands. — 2) To modify column attributes, open the script in your text editor (for example, Notepad, Notepad++, or VS Code) so you can add COLUMN and SET commands above the query.

3) Add COLUMN PRODUCT_NAME HEADING with quoted text to create a readable heading. — 3) Add a `COLUMN` command for `PRODUCT_NAME` to set a clearer heading. Quoted text allows spaces and mixed case, for example: `COLUMN product_name HEADING "Product Name"`. COLUMN commands must appear before the query so that the environment is set before SQL*Plus formats the result.

4) Use COLUMN BUYER FORMAT A10 WORD_WRAPPED to shorten and wrap the BUYER column. — 4) Adjust the width of the **BUYER** column by referencing the column or its alias exactly as it appears in the SELECT list. For example: `COLUMN buyer FORMAT A10 WORD_WRAPPED` This limits the column to 10 characters and wraps longer names to the next line between words instead of truncating them.

5) Use SET FEEDBACK OFF to suppress the row count line at the bottom of the output. — 5) To remove the “n rows selected” line at the bottom of the report, add: `SET FEEDBACK OFF` This is often preferred for printed or exported reports where the row count is not needed.

6) Save the modified script in your text editor to persist the COLUMN and SET commands. — 6) Save the changes in your text editor (for example, confirm the **Yes** prompt in Notepad) so the updated script, including your COLUMN and SET commands, is written back to the file (such as `PR_CUST.sql`).

7) Back in SQL*Plus, run the script with START or @ to apply the new formatting. — 7) Return to SQL*Plus and execute the script (and its formatting commands) using: `START PR_CUST` or `@PR_CUST`. SQL*Plus first applies the COLUMN and SET settings, then runs the query.

8) Output now shows improved headings, column widths, and no row count line. — 8) The report output now looks cleaner: headings are more descriptive, the BUYER column is narrower and wrapped, the SALES_DATE column is visible, and there is no trailing row count line. These settings remain in effect for the current session until you change them or exit SQL*Plus.

Putting It All Together in a Script

In practice, you will usually combine COLUMN and SET commands at the top of your script so every run of the report uses the same formatting. A typical header section might look like this:


COLUMN sales_date  HEADING "Sales Dt"
COLUMN lastname    FORMAT A15      HEADING "Name"
COLUMN prod_count  FORMAT 99       HEADING "# Items"
COLUMN total_sale  FORMAT 9999.99  HEADING "Total"

SET PAGESIZE 22
SET LINESIZE 45
SET FEEDBACK OFF

Below these commands, you place the query itself. When you run the script with @script_name, SQL*Plus applies all the formatting settings and then prints the result set in the desired layout. Because these are SQL*Plus environment settings, you can easily adjust them as your report requirements evolve.

In the next lesson, you will extend this approach by using other SQL*Plus environment settings to control page breaks, titles, and more advanced report formatting options.