SQL Plus - Wrap

> Procedural Languages > (SQL Plus|SqlCl) (Oracle Database Console)

1 - About

Wrap is a feature of SQL Plus that can be set:

Advertising

3 - Syntax

3.1 - System variable

Wrap is a system variable that turns on or off (default) globally the truncating of the content outside the width of a line or column.

SET WRAP {ON|OFF}

where:

  • ON: (lines|columns) will be wrapped (default)
  • OFF: (lines|columns) will be truncated

The line size is defined by the linesize system variable

3.2 - Column

By default, if a value does not fit within the column width, SQL*Plus wraps or truncates the character string depending on the setting of the WRAP system variable. You can change this global behaviour by setting it on a column with the column command.

COLUMN column_name WRA[PPED] | WOR[D_WRAPPED] | TRU[NCATED]

where:

  • WRAPPED: wraps the string within the column bounds, beginning new lines when required.
  • WORD_WRAPPED: will
    • left justifies each new line,
    • skip all leading whitespace (for example, returns, newline characters, tabs and spaces), including embedded newline characters. Embedded whitespace not on a line boundary is not skipped.
  • TRUNCATED: truncates the string at the end of the first line of display.
Advertising

4 - Example

4.1 - linesize

4.1.1 - Before

show linesize
linesize 100
set wrap on
show wrap
wrap : lines will be wrapped

Reports:

      |                                |        | Number |            |            |        Avg
      |                                |        |     of |      Size  |       Size |       Size
Total | Segment Name                   | Type   |    Seg |   (Blocks) |       (Kb) |       (Kb)
----- | ------------------------------ | ------ | ------ | ---------- | ---------- | ----------
TOTAL | CAL_MONTH_SALES_MV             | TABLE  |      1 |          8 |         64 |         64
      | CHANNELS                       | TABLE  |      1 |          8 |         64 |         64
      | COSTS                          | PART   |     16 |      16384 |     131072 |       8192
      | COUNTRIES                      | TABLE  |      1 |          8 |         64 |         64
      | CUSTOMERS                      | TABLE  |      1 |       1664 |      13312 |      13312
      | DIMENSION_EXCEPTIONS           | TABLE  |      1 |          8 |         64 |         64
      | DR$SUP_TEXT_IDX$I              | TABLE  |      1 |          8 |         64 |         64
      | DR$SUP_TEXT_IDX$R              | TABLE  |      1 |          8 |         64 |         64
      | FWEEK_PSCAT_SALES_MV           | TABLE  |      1 |         72 |        576 |        576
      | PRODUCTS                       | TABLE  |      1 |          8 |         64 |         64
      | PROMOTIONS                     | TABLE  |      1 |         64 |        512 |        512
      | SALES                          | PART   |     16 |      16384 |     131072 |       8192
      | SUPPLEMENTARY_DEMOGRAPHICS     | TABLE  |      1 |        512 |       4096 |       4096
      | TIMES                          | TABLE  |      1 |         64 |        512 |        512

4.1.2 - After

set linesize 60
set wrap off
show wrap
wrap : lines will be truncated
truncating (as requested) before column Size (Blocks)

truncating (as requested) before column Size (Kb)

truncating (as requested) before column Avg Size (Kb)


      |                                |        | Number
      |                                |        |     of
Total | Segment Name                   | Type   |    Seg
----- | ------------------------------ | ------ | ------
TOTAL | CAL_MONTH_SALES_MV             | TABLE  |      1
      | CHANNELS                       | TABLE  |      1
      | COSTS                          | PART   |     16
      | COUNTRIES                      | TABLE  |      1
      | CUSTOMERS                      | TABLE  |      1
      | DIMENSION_EXCEPTIONS           | TABLE  |      1
      | DR$SUP_TEXT_IDX$I              | TABLE  |      1
      | DR$SUP_TEXT_IDX$R              | TABLE  |      1
      | FWEEK_PSCAT_SALES_MV           | TABLE  |      1
      | PRODUCTS                       | TABLE  |      1
      | PROMOTIONS                     | TABLE  |      1
      | SALES                          | PART   |     16
      | SUPPLEMENTARY_DEMOGRAPHICS     | TABLE  |      1
      | TIMES                          | TABLE  |      1

5 - Configuration

5.1 - Line of Characters after Wrapped Column Values

To print a line of dashes after each wrapped column value, enter the commands:

SET RECSEP WRAPPED
SET RECSEPCHAR "-"

where:

  • RECSEP determines when the line of characters is printed; you set RECSEP to EACH to print after every line, to WRAPPED to print after wrapped lines, and to OFF to suppress printing. The default setting of RECSEP is WRAPPED.
  • RECSEPCHAR sets the character printed in each line. You can set RECSEPCHAR to any character.
Advertising