In this lesson, you are going to learn about some of the basic data types that exist in Oracle.
I’m not going to cover all of them because there are some types that are not used very frequently and I don’t want to saturate your mind with information you are most likely not going to need by now.
What is a data type?
But, before we discuss the specific data types that exist in Oracle, I want to make sure you understand what a data type is, which is actually very simple.
As the term implies, a data type defines the type of data you can put into a database column.
For example, if you have a column to store the price of a product, you know that you are only going to store numeric data in it, so you define your column with a number data type. If you try to insert a row that has non-numeric data for that column, you will get an error.
In a similar way, if you have a column where you want to store the hire date of each employee, you define that column with a date type. If you try to insert, for example, a name, or a number into that column, you will get an error, because the column was defined with a date data type, and thus, it can only store dates.
Numeric data types
So, as I mentioned in the previous example, there are numeric data types, and the most commonly used one is NUMBER.
The NUMBER data type, allows you to store numeric data, and you can define the maximum number of total digits, or precision, and the maximum number of decimals, or scale.
Let’s take a look at the first part of the tables creation script you ran in a previous lecture, to see some examples of how the number data type is used:
CREATE TABLE course.department
(
id number(5) constraint pk_department primary key,
name varchar2(50),
monthly_budget number(8,2),
last_employee_id number(5)
);Here in the department table, we have 3 numeric columns. The first one is the ID, which can accept at maximum numbers with five digits, and the second one is the monthly budget, which can accept numbers with at most 8 digits in total, and 2 decimals at most.
Character or Text data types
Okay, the next data type we are going to talk about is the VARCHAR2 type.
This is a type that allows you to store variable-length character or alphanumeric strings. For example, this is the type you would use to store names, descriptions, and similar data. You can actually store numbers too, but you must have clear the fact that, if you store numbers, they are going to be treated as strings unless they are explicitly or implicitly converted into numbers.
I said that they are variable-length strings because when you define a varchar2 column, you define a maximum length, but if you store data that does not occupy that maximum, the database actually stores only the data you entered, and the rest of the available space is not used.
This is what differentiates this data type from the CHAR data type, because CHAR columns have a fixed length, so for example, if you define a CHAR column with a length of 50 bytes, it will always take those 50 bytes, no matter if you store only one character in it. In that case, what would happen is that the database would add 49 blank spaces to the right of the one character you wanted to store, to complete the 50 bytes length you defined.
As you can imagine, the CHAR data type is a lot less used than VARCHAR2 for that reason.
And there is also a VARCHAR data type, which at the present time works exactly the same way as VARCHAR2, but Oracle has said that it can change in the future, so you are going to be better off using VARCHAR2 only.
Date and Time data types
Another very important data type you are going to use a lot is DATE.
And there is not much to say about it, other than the fact that date columns actually store not only the date but also the time, which includes hours, minutes and seconds after midnight.
Please allow me to make a short parenthesis here, even though I will probably mention this again more than once in the future, because it is really important.
When you want to store dates in the database, they must be stored in columns defined with the DATE data type. They must NOT be stored as numbers and they must NOT be stored as varchars, and there is no exception to this. Doing something like that is a very bad practice and such a bad design will for sure cause you problems in the future, so, please remember, dates must be stored as dates. Period!
There is another type that can be used to store dates, with the difference that besides hours, minutes, and seconds, it also stores fractions of a second, and this type is called TIMESTAMP.
Large Object data types
And finally, let’s briefly talk about LOB types.
These are used to store large amounts of unstructured data, for example, to store employees or product pictures, videos, audio, etc.
The BLOB data type is the one that allows you to store large amounts of data in binary format, so, this is the one you would use to store videos, images, etc.
Then we have the CLOB data type, which is used to store large amounts of data in character or text format, and the NCLOB datatype, which is pretty similar to CLOB, with the difference that it stores data in UNICODE.
And finally, there’s the BFILE type, which stores binary data in operating system files, outside of the database, so, what you really store in the database is only a pointer to the operating system file.
So, based on my experience, you are probably going to be using NUMBERs, VARCHARs AND DATEs a lot, and then BLOBs and CLOBs to a lesser degree, and more rarely BFILES.
Okay, that’s it for this lesson. Now you are well prepared to start learning about the SELECT statement, which is the topic of the next lesson.
See you there!