To reference an attribute or method of an object type in an SQL statement, use a table alias to fully qualify the reference.
Schema ob in the following example contains the cust_addr_typ type and the customers table. The customers table has a cust_address column of the cust_addr_typ type.
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 the 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, assume that the sample schema ob contains an object table category_tab based on catalog_typ. This table contains the 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 return result is as follows:
+----------------+
| Catalog Type |
+----------------+
| online catalog |
+----------------+