Oracle data types.
In this video, you'll learn all about the types of data in Oracle, what they can store
and when to use them.
Understanding and choosing a data type for a column can be hard.
Knowing what data types are available is the first step.
There are some you should use in certain situations and some you should avoid.
I've got a blog post in the description that lists all of these data types as well, which
you can use as a reference.
Let's get into the video.
There were three main groups of data in Oracle SQL and in most SQL databases.
They are text, number and date.
We'll look at each of these groups and each data type in Oracle SQL, and when you should
use them.
Here's a list of the text data types in Oracle.
You've got CHAR, VARCHAR and some variations of them, as well as some other larger data
types.
The CHAR data type is a fixed-length character data type.
It's common in all SQL databases.
When you specify a CHAR data type, you also specify the number of characters that can
be used in the field.
You can store up to this number of characters and any unused characters are stored as a
space.
For example, a field of CHAR(10) can I have a value of tree.
Tree is only four characters, so the remaining six characters in the field are set to spaces.
This can cause issues with displaying data and comparing data.
When should you use CHAR?
I don't think you should ever use the CHAR data type.
There are other data types that you can use to do the same thing as CHAR and don't have
the same drawbacks of padding with spaces.
VARCHAR2 data type is a variable-length character type.
This means it can store text values up to a maximum number of characters you specify.
It's similar to CHAR, except it does not pad unused characters with spaces.
If you store the value of tree in a VARCHAR2(10) field then only the word tree is stored.
No spaces are added.
Why is it called VARCHAR2?
This is because of VARCHAR is an SQL standard data type and Oracle's VARCHAR2 does not meet
the standard.
So, rather than calling it VARCHAR, they called it VARCHAR2, which leaves them the option
later to implement the VARCHAR standard.
It's a bit confusing, but something to get used to.
So, when should you use VARCHAR2?
Most of the time when storing text values.
It's the most common data type that I use.
The only exception would be is if you need to store Unicode values, then you can use
a different data type.
The NCHAR data type is similar to CHAR, but it can store Unicode values.
You can store multi-byte characters into a fixed-length field.
However, just like the CHAR data type unused characters are stored as spaces.
When would use NCHAR?
I would not recommend using NCHAR as there's another data type that can be used, and that
is NVARCHAR2.
The NVARCHAR2 data type is used to store Unicode or multi-byte characters into a single string.
It's similar to VARCHAR2.
If you need to store Unicode data in a text field, I would suggest using NVARCHAR2 instead
of NCHAR.
The LONG data type can store up to two gigabytes of text data.
That seems like a lot of data, right?
It could be useful if you need to store large text inputs.
However, the LONG data type has been deprecated by Oracle and only remains for backwards compatibility.
So, I would not recommend using the LONG data type, use BLOB or CLOB instead, which we'll
cover later in this video.
The RAW data type is similar to VARCHAR2, in that it stores variable-length strings.
However, there is no character conversion performed when you import or export between
systems.
So, RAW would be a good choice if you need to convert data between systems and are concerned
with, or have issues with data being transformed.
The LONG RAW data type is similar to the LONG data type, except it's stored in raw data
form, just like the RAW data type for converting between systems.
However, just like the LONG data type it's been deprecated by Oracle.
It's better to use the BLOB or BFILE data type instead of LONG RAW.
Now, we've covered the text data types, it's time to look at the numeric data types.
There are several numeric data types in Oracle, and each of them serve a purpose.
Let's look at them in more detail.
The NUMBER data type is probably the most common numeric data type in Oracle that I've
seen.
It stores whole numbers as well as decimals.
You can specify the precision and the scale of values, which allows you to store decimals.
It stores a very high number value, up to 38 digits.
So, when should you use the NUMBER data type?
Whenever you need to store an exact number, either whole or decimal, these should be in
most cases.
So, most of the time I imagine developers would be using the NUMBER data type with whatever
precision and scale they need.
The BINARY_FLOAT data type is used for storing floating point numbers.
These are approximations of numbers and use a different calculation to store.
It's similar to a FLOAT data type in other languages.
So, when should you use BINARY_FLOAT?
If performance is important, or if you need to store a large floating point number, and
aren't concerned with accuracy.
I don't often use floating point numbers, so I haven't seen this used very often at
all.
BINARY_DOUBLE is another data type that stores decimal numbers, similar to BINARY_FLOAT.
However, it's similar to the DOUBLE data type in other languages.
BINARY_DOUBLE is 64-bits and BINARY_FLOAT is 32-bits.
You should use BINARY_DOUBLE if performance is important, if you don't need the accuracy
of the NUMBER data type and if your number is too large for BINARY_FLOAT.
Similar to BINARY_FLOAT, I haven't seen this used very often.
Oracle SQL has a BOOLEAN data type, which is used to store a true or false values.
This can be handy if you need to store this type of information and many other databases
don't have a BOOLEAN data type.
However, this is only available in PL/SQL.
You can't store a BOOLEAN in a table in Oracle.
If you want to store a BOOLEAN, I recommend a number with one digit.
You should use BOOLEAN if you're using PL/SQL and need to store a true or false value use
BOOLEAN.
The PLS_INTEGER data type is only available in PL/SQL.
It's an integer value and works in a similar way to the NUMBER data type, but performs
better in PL/SQL.
It can only be used to store whole numbers as well, not decimals.
So, if you're using PL/SQL and need to store whole numbers, use PLS_INTEGER instead of
NUMBER.
It takes up less storage and can perform better.
The BINARY_INTEGER data type is similar to PLS_INTEGER.
It can only take whole numbers and has a similar range to PLS_INTEGER.
However, it's older and slower than PLS_INTEGER.
When should you use BINARY_INTEGER?
Never.
It's older and slower than PLS_INTEGER, so you should use PLS_INTEGER instead.
The INTEGER data type is an SQL standard data type.
However, in Oracle, it's a synonym for NUMBER with 38 digits.
This means if you declare a column as integer, it's actually declared as a NUMBER with 38
places and no decimals behind the scenes.
So, when should you use INTEGER?
I don't think you should ever use it.
If you need whole numbers use NUMBER instead, and just don't declare any decimals.
The FLOAT data type is another SQL standard data type.
However, in Oracle, it maps to the NUMBER data type.
It works differently to the BINARY_FLOAT data type as it stores an exact number.
I would suggest not using the FLOAT data type, as it just maps to the NUMBER of data type.
Just use NUMBER instead.
The final numeric data type in Oracle is the DECIMAL data type.
Behind the scenes this just maps to the NUMBER data type, just like FLOAT and INTEGER.
I would also suggest avoiding the DECIMAL data type and just using NUMBER instead.
Here's a list of the different date and time data types in Oracle.
There are a few that store date and time and a couple that store intervals of time.
Let's take a look.
The DATE data type in Oracle actually stores both the date and a time component.
It can be confusing as it's called DATE and by default only displays the date, but it
stores date and time; year, month, day, hours, minutes and seconds.
You should use the DATE data type if you want to store a date and time, but fractional seconds
and time zones are not important.
The TIMESTAMP data type is similar to DATE, except it allows you to store fractions of
a second.
It stores the date and time component, including up to nine decimal places of a second.
If you need sub-second times such as for log files, and if time zones are not important,
you should use TIMESTAMP.
The TIMESTAMP WITH TIME ZONE data type is similar to TIMESTAMP, but it stores the time
zone as well.
So, it stores year, month, day, hour, minute, second, fractions of a second and the time
zone.
You can specify the number of hours and minutes as an offset to UTC, either positive or negative.
So, when should you use this data type?
When you need to store time zones, but it does not need to be tailored to each user.
The TIMESTAMP WITH LOCAL TIME ZONE data type is used to store a timestamp and to time zone.
However, it's different to TIMESTAMP WITH TIME ZONE because when you select from this
data type, you get a value in your local timezone.
The other data type, TIMESTAMP WITH TIME ZONE will display the data in the database time
zone.
If you need to store dates and times, and times zones are important and they need to
be tailored to each user, then use the TIMESTAMP WITH LOCAL TIME ZONE data type.
The INTERVAL YEAR TO MONTH data type is one of two interval data types in Oracle.
While the other date data types store a point in time, the interval data types store a period
of time.
The INTERVAL YEAR TO MONTH data type stores a period of time that includes years and months.
For example, three years and six months.
You could store it as separate fields with numbers, but it won't have the inbuilt validation
like the interval data types do.
If you need to store a period of time and the period includes years and months then
use the INTERVAL YEAR TO MONTH.
The other interval data type is the INTERVAL DAY TO SECOND data type.
It can store an interval of time that includes days all the way down to seconds.
It includes validation to ensure that the value is valid.
When should you use INTERVAL DAY TO SECOND?
Use it when you need to store a period of time that includes days, minutes, hours or
seconds.
There are a few other data types that don't fit into these categories.
They can be used to store large amounts of text, binary data or other specific cases.
The BFILE data type is used to refer to files on the server which are outside the database.
It's like a point out to a file.
BFILE values are read only to Oracle and the maximum size is four gigabytes.
So, when should you use a BFILE?
If you need to refer to files outside of the database and would rather keep the file there
than import it into a CLOB or a BLOB column.
BLOB data type stands for Binary Large Object.
It's used to store unstructured binary objects.
It's got a maximum size of four gigabytes.
You should consider using BLOBs when you want to store things like an image or audio files
in the database.
The CLOB data type stands for Character Large Object and is used to store large amounts
of character data.
It has a maximum size of four gigabytes.
It's a newer version of the LONG data type.
You should use the CLOB data type if you need to store large amounts of texts that cannot
fit into a VARCHAR2 data type.
The NCLOB data type is just like CLOB, but it can store multi-byte characters in the
same way that NCHAR and NVARCHAR2 do.
It has a maximum size of four gigabytes.
You should consider the NCLOB data type if you need to store large amounts of text data,
including Unicode data that won't fit into an NVARCHAR2 data type.
The ROWID data type is used to store an address of a row in a database.
It's an alphanumeric value used by the ROWID pseudocolumn.
You can specify a column with this data type if you want to store a value of ROWID, but
it should not be used as foreign keys to other rows.
If you want to store a row ID for later reference, use the ROWID data type.
The UROWID data type is used to store a ROWID values that come from non-Oracle databases.
It's pretty flexible as it's unknown what type of data it could be.
You should consider this data type when you need to store row IDs from non-Oracle databases.
That brings us to the end of the Oracle data types.
If you've learned something new from this video, make sure to subscribe to my channel.
If you want to learn more about database design and development, visit databasestar.com, that's
where I share my best database related content.
Which point in this video was most helpful for you?
Was it the full list of the Oracle data types or when each of them should, or shouldn't
be used or something else?
Thanks for watching.