Q
Manage Learn to apply best practices and optimize your operations.

How to make an object

How do I make an object?
First, you need to be using an Oracle version that supports objects. This means that you have to be using Oracle 8.0 or greater. When you are creating an object, you are defining your own data types. You may find these more useful than typical datatypes like CHAR, VARCHAR2, NUMBER. So how do you create your own datatype, or object? Below, I've created an object that will be a "name", composed of the first name, the middle initial, and the last name.

ORA9I SQL> CREATE TYPE name_type AS OBJECT 
  2  (
  3     first    VARCHAR2(20),
  4     mi       CHAR(1),
  5     last     VARCHAR2(30)
  6  );
  7  /

Type created.

Now that I've created this type, I can use it when creating a table as follows:

ORA9I SQL> CREATE TABLE employees 
  2  (
  3     emp_name name_type,
  4     street   VARCHAR2(30),
  5     city     VARCHAR2(30),
  6     state    CHAR(2),
  7     zip      NUMBER(5)
  8  )
  9  /

Table created.

ORA9I SQL> desc employees
 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 EMP_NAME                                           NAME_TYPE
 STREET                                             VARCHAR2(30)
 CITY                                               VARCHAR2(30)
 STATE                                              CHAR(2)
 ZIP                                                NUMBER(5)

Note that the EMP_NAME column in the EMPLOYEES table is defined as the type that we created above. This is shown in the DESCRIBE command as well. I can now insert data into this table as follows:

ORA9I SQL> INSERT INTO employees VALUES
  2  (name_type('Brian','S','Peasland'),'123 Main
Ave','Anywhere','ST',12345);

1 row created.

ORA9I SQL> select * from employees;

EMP_NAME(FIRST, MI, LAST)
--------------------------------------------------------------------------------
STREET                         CITY                           ST       
ZIP
------------------------------ ------------------------------ --
----------
NAME_TYPE('Brian', 'S', 'Peasland')
123 Main Ave                   Anywhere                       ST     
12345

When inserting the row of data, I had to tell Oracle that the value I was inserting was the NAME_TYPE type and then define the components of that type. You can also see how the data was displayed when I did a simple SELECT on the table. To pull just one component of the EMP_NAME out of this table, you can query similar to the following:

ORA9I SQL> SELECT e.emp_name.last
  2  FROM employees e;

EMP_NAME.LAST
------------------------------
Peasland

Notice that I used the 'dot' notation to pull just the LAST component of the EMP_NAME column. I also had to alias the table name and use that alias in the SELECT clause otherwise my SQL statement would fail.

Hopefully, this has been a quick and easy tutorial to get you on the road with Oracle's objects. There is much more to learn, so please consult the Oracle documentation.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close