If the value of an object itself is NULL, it is called a NULL object. A NULL object is different than an object whose properties' values are all NULL (these objects are not NULL objects). These objects, whose properties' values are all NULL, can have NULL values for specific properties, and this properties value can be changed to a non-NULL value by using a procedure or function. However, a NULL object cannot change its properties' values, as well as cannot invoke its method.

The following is an example of inserting a non-NULL object which contains both NULL objects and NULL properties' values in the object column of a table that contains column objects.

CREATE TYPE customer_type2 AS OBJECT (
  custno NUMBER,
  name   VARCHAR2(40),
  phone  VARCHAR2(20),
  MEMBER FUNCTION tostring RETURN VARCHAR DETERMINISTIC);
/

CREATE TYPE BODY customer_type2 AS
  MEMBER FUNCTION tostring RETURN VARCHAR DETERMINISTIC IS
  BEGIN
    RETURN custno || ':' || name || ':' || phone;
  END;
END;
/

CREATE TABLE customer2 (
  cust_obj customer_type2,
  status   VARCHAR(1));

INSERT INTO customer2 VALUES (Null, 'I');
INSERT INTO customer2 VALUES (customer_type2 (NULL, NULL, NULL), 'V');

Just like users can define constraints on a table, they can also define constraints on a table that uses object types. However, constraints can only be defined for properties that exist at the bottom of object type's containment tree. The properties at the very bottom cannot be object type properties, so these properties have built-in types.

The following is an example of specifying an object type's property as the primary key of an object table when creating an object table.

CREATE TABLE customer_type_tab2 of customer_type2 (
  custno PRIMARY KEY);

When a table contains an object column, a constraint can also be defined in the object's properties. In this case, it can be defined with a period (.).

CREATE TABLE customer3 (
  cust_obj customer_type2,
  status   VARCHAR(1),
  CONSTRAINT cust_check_cons
  CHECK (cust_obj.custno IS NOT NULL));

Conditions for an index column are the same as the conditions that can define constraints. Only the properties that exist at the bottom of an object type's containment can be specified as an index column. In this case, it can be defined with a period (.)

CREATE INDEX cust_idx_1 ON customer3 (cust_obj.custno);

A function-based index cannot be created if a function's return type is an object type. When invoking the member method of a function of an object type while defining the function-based index of an object table, the table alias can be defined with a period as shown below.

CREATE INDEX cust_idx_2 ON customer_type_tab2 x (x.tostring());

Use the dot notation if a table column is an object type and the user wants to select this object's property value. Take the following caution when using the dot donation in a SELECT statement: when using the dot notation in an object column, make sure that the table alias comes after the dot notation.

SELECT cust_obj.phone FROM customer3; -- Incorrect
SELECT x.cust_obj.phone FROM customer3 x; -- Correct

The VALUE(x) expression that takes each table's table alias as an argument in an SQL statement, and then returns the row object that corresponds to the current row.

INSERT INTO customer_type_tab2 values (1, 'David', '555-666-7777');
DECLARE
  c1 customer_type2;
BEGIN
  SELECT VALUE(x) into c1 FROM customer_type_tab2 x where custno = 1;
  dbms_output.put_line(c1.tostring());
END;
/

As in the above example where the users are not viewing an object table's property value but rather invoking each member method, a VALUE(x) expression is required. When an UPDATE statement is executed as follows, a (VALUE) expression can be used for overwriting an object table's existing object with an object the user-defined constructor created. This is shown in the following example.

CREATE OR REPLACE TYPE customer_type3 AS OBJECT (
  custno NUMBER,
  name   VARCHAR2(40),
  phone  VARCHAR2(20),
  CONSTRUCTOR FUNCTION customer_type3 RETURN SELF AS RESULT);
/

CREATE OR REPLACE TYPE BODY customer_type3 AS
  CONSTRUCTOR FUNCTION customer_type3 RETURN SELF AS RESULT IS
  BEGIN
    custno := -1;
    name := 'Untitled';
    phone := 'N/A';
    RETURN;
  END;
END;
/

CREATE TABLE customer_type_tab3 OF customer_type3;

INSERT INTO customer_type_tab3 values (1, 'David', '555-666-7777');

UPDATE customer_type_tab3 x SET VALUE(x) = customer_type3()
WHERE custno = 1;

When a function or procedure performs operations based on the values of a given object, a member method is used to define these operations.

As in a MEMBER FUNCTION or MEMBER PROCEDURE, specify the MEMBER in front when defining a member method within a CREATE TYPE statement. A member method defined like this can be invoked by using the dot notation after an object expression as shown in the following example.

dbms_output.put_line(my_obj_var.obj_memfunc1());

The object expression which contains the value of the object comes before the dot. After the dot, specify the name of the member method which is defined in the object type of the object. When invoking a member method in SQL, the user should always put parentheses around it, even if there are no parameters to pass to the method.

SELECT x.cust_obj.tostring() FROM customer2 x;

Member Method and SELF Parameter

Member methods are always assumed to have a single parameter set as the first parameter. This parameter is called a SELF parameter. In a SELF parameter, the value of the target object specified when the member method is called is sent as the value of the parameter.

The user can specify this SELF parameter as the first parameter when defining the member method. However, even if not specified, it is automatically assumed to have been specified. Therefore, no syntax error is generated.

When specifying the target object's property value in the type's body, it can specified using the SELF parameter as well as the dot notation, although unnecessary for specification (as long as the parameter and the name do not overlap).

CREATE OR REPLACE TYPE rectangle_type AS OBJECT (
  x NUMBER,
  y NUMBER,
  MEMBER FUNCTION around RETURN NUMBER,
  MEMBER FUNCTION area(SELF IN OUT NOCOPY rectangle_type) RETURN NUMBER);
/

CREATE OR REPLACE TYPE BODY rectangle_type AS
  MEMBER FUNCTION around RETURN NUMBER IS
  BEGIN
    RETURN 2 * (x + y);
  END;
  MEMBER FUNCTION area RETURN NUMBER IS
  BEGIN
    RETURN SELF.x * SELF.y;
  END;
END;
/

As shown in the following example, the user can invoke a member method of an object stored in an object table by using the dot notation.

As well, it can be done using a VALUE(x) expression which represents a row object.

CREATE TABLE rect_tab of rectangle_type;
INSERT INTO rect_tab VALUES(2, 4);
INSERT INTO rect_tab VALUES(rectangle_type(8, 3));
SELECT x.around(), x.area() FROM rect_tab x;
SELECT VALUE(x).around(), VALUE(x).area() FROM rect_tab x;

Comparison of Member Methods and Object Methods

To compare or sort objects, the object type in which the two objects are based on must be the same. A comparison operation is performed using a member method of a set type. If such member method does not exist, then an error is generated. However, equal and unequal comparisons are allowed through special rules. There are two types of methods for performing comparisons, map method and order method. Only one of these can be defined for a single object type.

A static method does not define operations to be performed on a particular object, but is simply a function or procedure defined within an object type. Therefore, there are no predefined SELF parameters in static methods. To invoke a static method, precede the object type name with a dot before the method name.

my_obj_type.my_static_method1();

The constructor method is a function that returns an object of that type as a result of execution. There are two types: predefined default constructor and user-defined constructor.

An object identifier can be used to uniquely identify an object stored in the object table. The object identifier can select either an auto-generated type or a primary key type when creating an object table.

When a VARRAY is stored as a column of a table, it is actually stored as a single column. The maximum length of a VARRAY type value is a value obtained by multiplying the maximum length of the element type by the maximum number of elements of the VARRAY type, with some added overhead.

If the maximum possible length of the VARRAY type value exceeds the maximum length of VARCHAR2, a LOB segment is also created, which allows storing of the VARRAY value with a long length when creating a table with this VARRAY column. However, when storing the actual value, if the value is smaller than the maximum length of VARCHAR2 for each value regardless of the existence of the LOB segment, it is stored in the table as it is, otherwise, it is stored in the LOB segment.