If you reference attributes or methods of object types in an SQL statement, you must use table aliases to fully qualify the references.
In the following example, the sample schema ob contains the type cust_addr_typ and the table customers, which has a column cust_address of type cust_addr_typ:
CREATE TYPE cust_addr_typ
AS OBJECT
(street_address VARCHAR2(40),
postal_code VARCHAR2(10),
city VARCHAR2(30),
state_province VARCHAR2(10),
country_id CHAR(2));
/
CREATE TABLE customers
(customer_id NUMBER(6),
cust_first_name VARCHAR2(20) CONSTRAINT cust_fname_nn NOT NULL,
cust_last_name VARCHAR2(20) CONSTRAINT cust_lname_nn NOT NULL,
cust_address cust_addr_typ,
. . .)
In an SQL statement, the reference to the postal_code attribute must be fully qualified with a table alias, as shown in the following example:
SELECT c.cust_address.postal_code
FROM customers c;
UPDATE customers c
SET c.cust_address.postal_code = '610000'
WHERE c.cust_address.city = 'chengdu'
AND c.cust_address.state_province = 'SICHUAN';
To reference a member method that does not accept parameters, you must provide empty parentheses. For example, the sample schema ob contains an object table category_tab based on the catalog_typ type, which has a member function getCatalogName. To call this method in an SQL statement, you must provide empty parentheses, as shown in the following example:
SELECT TREAT(VALUE(c) AS catalog_typ).getCatalogName() "Catalog Type"
FROM categories_tab c
WHERE category_id = 10;
The result is as follows:
+----------------+
| Catalog Type |
+----------------+
| online catalog |
+----------------+