All language subtitles for [SubtitleTools.com] Object Types - Learning Oracle 12c [Video]

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.