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.

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

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.


     



Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151
Simiral book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net