All Products
Search
Document Center

Reference object type attributes and methods

Last Updated: Jun 18, 2021

To reference object type attributes or methods in an SQL statement, qualify the reference by using a table alias. In the following example, the sample schema ob contains the cust_address_typ type and the customers table, and the customers table has a cust_address_typ column named cust_address.

CREATE TYPE cust_address_typ
  OID '82A4AF6A4CD1656DE034080020E0EE3D'
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_address_typ,
. . .

In an SQL statement, you must qualify the reference to the postal_code attribute by using 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 arguments, provide empty parentheses. For example, the ob sample schema contains the category_tab object table based on catalog_typ, and this table contains the getCatalogName member function. To call this method in an SQL statement, 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 following result is returned:

+----------------+
| Catalog Type   | 
+----------------+
| online catalog | 
+----------------+