Introduction to Oracle OBJECT Data Type

In the Oracle database, OBJECT is a composite data type that can be used to define a custom data type with multiple attributes.

Syntax

The syntax for OBJECT data type is as follows:

CREATE TYPE type_name AS OBJECT (
  attribute_name1 datatype,
  attribute_name2 datatype,
  attribute_name3 datatype,
  ...
);

Where type_name represents the name of the custom data type, attribute_name represents the attribute names of the custom type, and datatype represents the data types of the attributes.

Use Cases

OBJECT is commonly used in scenarios where custom data types need to be defined, such as storing complex object data or combining multiple data types into a new data type. Using OBJECT can help to normalize the data tables, avoiding data redundancy and duplication.

Examples

Here are two examples of OBJECT data type.

Example 1: Creating an Object Type

Creating a custom object type person_type that includes attributes for person information.

CREATE TYPE person_type AS OBJECT (
  name VARCHAR2(50),
  age NUMBER,
  gender VARCHAR2(10)
);

Example 2: Using an Object Type

Using the created object type person_type to create a new table for storing multiple person information.

CREATE TABLE person (
  id NUMBER,
  person_data person_type
);

INSERT INTO person (id, person_data)
VALUES (1, person_type('Alice', 25, 'Female'));

INSERT INTO person (id, person_data)
VALUES (2, person_type('Bob', 30, 'Male'));

SELECT * FROM person;

Output:

ID   PERSON_DATA.NAME   PERSON_DATA.AGE   PERSON_DATA.GENDER
---- ------------------ ----------------- ------------------
1    Alice              25                Female
2    Bob                30                Male

Conclusion

OBJECT is a very useful data type that can be used to define custom composite data types. It can help to normalize data tables and avoid data redundancy and duplication. By creating custom data types, we can conveniently store and query complex object data.