Original subtitles

In this lesson, we're going to take a look

at objects in the database.

Now, when we refer to an object or a database object,

we can be referring to tables, indexes, procedures, packages,

or the like.

But starting in version 8.0, Oracle

was attempting to leverage some of the recent excitement

over object-oriented technology and to apply this

to database objects.

The result is the object technology

that Oracle has in the database.

Now, this is not something that is very commonly used today.

Some might even say it was added more of a marketing idea

more than anything.

However, it is present in the database, it can be used,

and, indeed, companies do use it because it really

allows you to multidimensionalize data,

at least in a rudimentary way.

Now, Oracle has other products to do multidimensional data,

but if you're going to use the object-oriented methodology all

of the way, so to speak, then you

can create objects and object types and tables that

use those object types within the database.

So the first thing we want to do is create a type.

So we're using a create or replace type statement

to create a type called address_ot,

and the ot as being object type.

And then we use the keywords AS OBJECT,

and this refers to our ability to instantiate it,

in certain circumstances, and use it

in a more object-oriented way.

Basically what this type is is a complex or multidimensional

data type.

So we have data types, like VARCHAR2, NUMBER, and DATE,

those being scalar data types.

These would be more of a multi-scalar object type,

or multidimensional.

I think you'll see what I mean as we create it.

So we have a data type here that we're creating, an object type.

We have street address, city, date code, and ZIP.

And our type is created.

So of course, when we're creating a table,

we could create individual columns

for street, city, state, and ZIP.

But what we're doing here is we're

encapsulating all of those into a composite type, if you will,

of address_ot.

And we're creating it as an object

so it can be instantiated in that way.

So let's create a table with this type.

So this is a table emp_info for just employee information.

Now, notice what we've done here.

We've created a table with four columns, a first name,

last name, and birth date.

And these are all what we would consider normal or regular data

types.

But the address that we're putting in our employee

information is actually an object type.

So we're declaring this address to be of the type address_ot.

So how do we leverage this?

So let's use a simple anonymous block PL SQL.

We'll declare a local variable, lv_address.

So for lv_address, the local variable,

we're assigning it to use the address object type

and to pass these values into the object.

And they correspond to these four variables

inside the object.

I'm going to take this and move the assignment down here.

And here, we'll use for instantiation.

So we're declaring the lv_address variable

as being of data type address_ot.

Here, we assign a value to the variable.

And now we're going to insert into the table.

Now, notice, if we're going to insert into this table,

certainly we can insert a first name, a last name, and a birth

date.

But how are we going to insert an entire address

into this object type?

Well, we'll see the methodology here.

We simply say lv_address.

Now, this references this variable assignment here.

And these values go back to match our object type.

Create that table.

And run our statement.

Anonymous block completed.

And if we do a select count star from emp info, highlight it,

we notice that we have one row.

So let's do select star from emp info.

Now, notice what we have here, the first name, last name,

and birthdate that we inserted.

But for address, it has to make a reference

to the address_ot object type.

And to pull this out, we actually

need to do a little extra work.

We actually need to reference the object type using an alias.

So here, we have the emp_info table

that we reference with the ei alias,

and ei being the alias here.

We say address, meaning this column, and street.

And this pulls out the street that we

inserted using the object type.

So essentially, we've instantiated this to pull back

just the street information.

And that goes back to this part of the type.

We could also change this to city

and pull back city information, as well.

So again, you may not see this all that often,

but this is kind of a basic look at how object technology works

within an Oracle database.