Sql*Plus User’s Guide and Reference

Part $13,500 .3
ners
Erra $12,000 .3
zuri
z
LAST MONTHLY
NAME SALARY COMMISSION
---- -------- ----------
Camb $11,000 .3
raul
t
Zlot $10,500 .2
key

---

Formatting Columns
6-6
SQL*Plus User's Guide and Reference
Example 6–5, "Formatting a Character Column"
. If WRAP is set to OFF, the names are 
truncated (cut off) after the fourth character.
The system variable WRAP controls all columns; you can override the setting of 
WRAP for a given column through the WRAPPED, WORD_WRAPPED, and 
TRUNCATED clauses of the COLUMN command. See the 
COLUMN
 command on 
page 12-26 for more information on these clauses. You will use the WORD_WRAPPED 
clause of COLUMN later in this chapter.
NCLOB, BLOB, BFILE or multibyte CLOB columns cannot be formatted with the 
WORD_WRAPPED option. If you format an NCLOB, BLOB, BFILE or multibyte 
CLOB column with COLUMN WORD_WRAPPED, the column data behaves as 
though COLUMN WRAPPED was applied instead.
Now return the column to its previous format:
COLUMN LAST_NAME FORMAT A10
Example 6–6
Formatting an XMLType Column 
Before illustrating how to format an XMLType column, you must create a table with 
an XMLType column definition, and insert some data into the table. You can create an 
XMLType column like any other user-defined column. To create a table containing an 
XMLType column, enter
CREATE TABLE warehouses (
warehouse_id NUMBER(3),
warehouse_spec SYS.XMLTYPE,
warehouse_name VARCHAR2 (35),
location_id NUMBER(4));
To insert a new record containing warehouse_id and warehouse_spec values into the 
new warehouses table, enter
INSERT into warehouses (warehouse_id, warehouse_spec)
VALUES (100, sys.XMLTYPE.createXML(
'
Owned
'));
To set the XMLType column width to 20 characters and then select the XMLType 
column, enter
COLUMN Building FORMAT A20
SELECT
w.warehouse_spec.extract('/Warehouse/Building/text()').getStringVal()
"Building"
FROM warehouses w;
Note:
The column heading is truncated regardless of the setting of 
WRAP or any COLUMN command clauses.
Building
--------------------
Owned

---

Formatting Columns
Formatting SQL*Plus Reports
6-7
For more information about the createXML, extract, text and getStringVal functions, 
and about creating and manipulating XMLType data, see Oracle Database PL/SQL 
Packages and Types Reference.