Oracle SQLPlus [Electronic resources] : The Definitive Guide, 2nd Edition نسخه متنی

7.4 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 Data'
SQL> COLUMN employee_id HEADING 'Employee ID'
SQL> SELECT * FROM employees;
Employee ID Employee Data
----------- ------------------------------------------------------------
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 is an improvement. However, you can do a bit more. SQL*Plus
Version 8 introduced a new command called

ATTRIBUTE,
which allows you to format the individual attributes of an object
column. In this case, you can use ATTRIBUTE to format the employee
salary so it prints as a dollar value. The following commands do
this:

ATTRIBUTE employee_type.employee_salary ALIAS emp_sal
ATTRIBUTE emp_sal FORMAT $999,999.99 The ATTRIBUTE command references the object column's
type and not the object column's name. In other
words, employee_type is used, not
employee. This is important and is easily
overlooked.

When you format an attribute for an object type, that format applies any time an object of that type is displayed. This is true even when the same object type is used in more than one column of a table or in more than one table. If you were to have two tables, each with an employee_type object column, the ATTRIBUTE commands just shown would affect the display format of data from both columns in both tables.

Having used the ATTRIBUTE command to format the employee salary
attribute, you can reissue the SELECT to get the following results,
which have the salary figures formatted as dollar amounts:

SQL> select * from employees;
Employee ID Employee Data
----------- ------------------------------------------------------------
111 EMPLOYEE_TYPE('Taras Shevchenko', '23-AUG-76', $57,000.00)
110 EMPLOYEE_TYPE('Ivan Mazepa', '04-APR-04', $67,000.00)
112 EMPLOYEE_TYPE('Igor Sikorsky', '15-NOV-61', $77,000.00) Look again at the ATTRIBUTE commands shown earlier. You will see that
two commands were used instead of one. The first command defined an
alias for the attribute. An alias is another name you can use in
subsequent ATTRIBUTE commands to save yourself the trouble of typing
in the entire object type and attribute name again. The second
ATTRIBUTE command referred to the alias. If you have deeply nested
objects, the dot notation for an attribute can be long, so this
aliasing ability can come in handy.

The only format element that can be used with a text attribute is A.
For example, you might specify A10 as the format for the employee
object's employee name attribute. When used with the
ATTRIBUTE command, a text format such as A10 serves to specify a
maximum display length for the attribute. Any characters beyond that
length are truncated and consequently not displayed. Notice the full
name in the line below:

111 EMPLOYEE_TYPE('Taras Shevchenko', '23-AUG-76', 57000) Applying a format of A10 to the employee name field results in the
name being truncated to 10 characters in length, as shown here:

111 EMPLOYEE_TYPE('Taras Shev', '23-AUG-76', 57000) Text attributes are never expanded to their maximum length. Such
values are delimited by quotes. Adding extra space inside the quotes
would be tantamount to changing the values, and there would be little
point putting the extra space outside the quotes.

Attributes of type DATE seem unaffected by any format settings you specify even though they, like text fields, are displayed within quotes.

As with the COLUMN command, the effects of ATTRIBUTE commands are
cumulative. That's why two commands are able to be
used for the previous example in place of one. In contrast to COLUMN,
there is no CLEAR ATTRIBUTES command. However, the CLEAR COLUMNS
command will erase any attribute settings you have defined.

Appendix B describes the format specifiers that you can use with the ATTRIBUTE command.