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 9,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.

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', ,000.00) 110 EMPLOYEE_TYPE('Ivan Mazepa', '04-APR-04', ,000.00) 112 EMPLOYEE_TYPE('Igor Sikorsky', '15-NOV-61', ,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.

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.