Oracle SQL*Plus: The Definitive Guide, 2nd Edition

Formatting Object Columns

Oracle8 introduced objects to Oracle's relational database world. You can define object types that you can then use as datatypes for columns in a relational table. The following example shows an object type named employee_type, as well as an employees table that contains an object column named employee. The employee column stores employee_type objects.

SQL> DESCRIBE employee_type
 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPLOYEE_NAME                            VARCHAR2(40)
 EMPLOYEE_HIRE_DATE                       DATE
 EMPLOYEE_SALARY                          NUMBER(9,2)
     
SQL> DESCRIBE employees
 Name                            Null?    Type
 ------------------------------- -------- ----
 EMPLOYEE_ID                              NUMBER
 EMPLOYEE                                 EMPLOYEE_TYPE

When you select from this table using SQL*Plus, the employee object is treated as one database column, which in fact it is. The attributes of the employee object are displayed in parentheses:

SQL> select * from employees;

EMPLOYEE_ID
-----------
EMPLOYEE(EMPLOYEE_NAME, EMPLOYEE_HIRE_DATE, EMPLOYEE_SALARY)
------------------------------------------------------------------
        111
EMPLOYEE_TYPE('Taras Shevchenko', '23-AUG-76', 57000)

        110
EMPLOYEE_TYPE('Ivan Mazepa', '04-APR-04', 67000)

        112
EMPLOYEE_TYPE('Igor Sikorsky', '15-NOV-61', 77000)

This output looks messy. You can tidy it up a bit by formatting the two columns so both fit on one line. As far as SQL*Plus is concerned, only two columns exist: employee_id and employee. Here's an example that formats the columns somewhat better:

SQL> COLUMN employee FORMAT A60 HEADING 'Employee ...