SQL Extensions   «Prev  Next»

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

Setting Oracle Column Attributes

SQL*Plus can be used to generate reports from your queries. There are a variety of environment commands that adjust the appearance of your output. The following table describes some of these environment commands.
SQL*Plus Environment command Description
COL[UMN] columnname [expression] Specify attributes for the column. Expression can be one or more of these choices:
  1. FORMAT [An | 999] - set the width of column
  2. HEADING text - text in column heading
  3. JUSTIFY [LEFT | RIGHT | CENTER] NOPRINT NEWLINE - go to new line before printing column
SET attribute Adjust environment settings. Attributes listed below are the more popular ones. The first option is the default setting. There are many more attributes.
>ECHO [OFF|ON] Repeat a command in a file before executing it.
FEED[BACK] [6|n|OFF|ON] Report how many rows you want returned.
HEA[DING] [OFF|ON] Turn on and off all column headings.
LIN[ESIZE] [80|n] Adjust number of characters per line.
PAGES[IZE] [24|n] Adjust number of lines per page.
TERM[OUT] [OFF|ON] Turn on and off output display.
TIMI[NG] [OFF|ON] Turn on and off performance display.
VER[IFY] [OFF|ON] Turn on and off variable substitution display.
SHOW [ALL | attribute ] Show the setting(s) for all attributes or for the specified attribute.

Review the Slide Show to see how easy it is to change a query into a report good enough to print.
1) Set Column 1 2) Set Column 2 3) Set Column 3 4) Set Column 4 5) Set Column 5 6) Set Column 6 7) Set Column 7 8) Set Column 8

  1. In the previous lesson, we saved a file with a query. This show the results when you execute the query.
  2. To modify the column attributes, edit the file using the text editor
  3. The first change is to modify the column heading for the PRODUCT_NAME column.
  4. The second change to adjust the width of the BUYER column.
  5. The final change is to tell SQL*Plus to omit the line at the end of the query stating how many rows were returned.
  6. Notepad prompts you to save the changes.
  7. Now that you have returned to SQL*PLUS, execute the SQL*Plus environment commands and the query using the START command.
  8. The results of the query look different now.

Modify Column Attributes

SQL Plus Commands to Adjust Column Page Settings

The next lesson continues with more SQL*Plus environment commands that help create reports.
Click here to practice adding SQL*Plus environment commands.
Sql Plus Commands to Adjust Column Page Settings