This Oracle tutorial explains the database table datatypes, their importance with graphical diagrams.
Oracle database table columns contain data. The data can be a date, string, number, Boolean, image, video etc. Oracle understands what type of data a column can have based of its Datatype.
You might think that why not storing all kinds of data in a single data type. Why are there different data types ? The reason is oracle is a relation database. It is not possible if we do not restrict its incoming data type.
This page contains (Skip to topic)..
Why are Column datatypes important?
Datatypes ensures data integrity. That means, we can not insert a number to order_date column of DATE type. Hence, while trying to insert 1001 to the table, oracle will complain. Oracle will tell you, “Hey!! You know what !! You should give me a data of date type. I do not understand what is 1001. It is not a date.”
Similarly, in the below example we are trying to insert “Rozalin” to “Order ID” column of NUMBER type. Oracle will again reject the value with an error.
However, if you will try to insert data with compliance to its column type, oracle will let it insert happily. This helps to restrict unwanted data to get inserted for some application or human error.
Most commonly used datatypes
VARCHAR2 Datatype in Oracle
VARCHAR2 stands for variable length characters. The idea behind the name is that it can hold variable length string and only occupies the data length in memory. Meaning, if you want to store “hello” in a column with
VARCHAR2(100), it will only occupy 5 bytes in memory.
-- step 1 CREATE TABLE datatype_test( col1 VARCHAR2(4000), col2 VARCHAR2(1000) ); Table DATATYPE_TEST created. --step 2 DROP TABLE datatype_test; --step 3 CREATE TABLE datatype_test( col1 VARCHAR2(4), col2 VARCHAR2(1) ); --step 4 INSERT INTO datatype_test VALUES('r','roza'); Error starting at line : 1 in command - insert into datatype_test values('r','roza') Error report - ORA-12899: value too large for column "HR"."DATATYPE_TEST"."COL2" (actual: 4, maximum: 1) --step 5 INSERT INTO datatype_test VALUES('roza','r'); 1 row inserted. --step 6 COMMIT; --step 7 INSERT INTO datatype_test VALUES('sameer','rozalin'); Error starting at line : 20 in command - INSERT INTO datatype_test VALUES('sameer','rozalin') Error report - ORA-12899: value too large for column "HR"."DATATYPE_TEST"."COL1" (actual: 6, maximum: 4) --Step 8 INSERT INTO datatype_test(col2, col1) VALUES('rozalin','sameer'); Error starting at line : 21 in command - INSERT INTO datatype_test(col2, col1) VALUES('rozalin','sameer') Error report - ORA-12899: value too large for column "HR"."DATATYPE_TEST"."COL1" (actual: 6, maximum: 4)
The list numbers represents the steps in the above code block.
- In the first step, we created a table datatype_test with two columns. The two columns are col1 and col2 of datatype varchar2(4000) and varchar2(1000) respectively.
- We dropped the table. That means the table datatype_test no longer exists after executing step 2 (i.e. DROP TABLE datatype_test;).
- Now, we again created the table with col1 as varchar2(4) and col2 as varchar2(1).
- In step 4, we are trying to insert ‘roza’ to col2 with datatype varchar2(1). The maximum length of the string, col2 can hold is 1. Hence, Oracle did not allow the data to be inserted.
- In step 5, we are proving valid data as per datatype.
- We are committing the inserted data in step 5.
- From step-7 and step-8, we see that Oracle is trying to check data integrity of col1 first irrespective of order present in the INSERT statement.
NUMBER Datatype in Oracle
NUMBER datatype in oracle stores numbers. The maximum value of number depends on the size provided in the curly braces similar to VARCHAR2.
NUMBER (PRECISION, SCALE);
Let us understand the same with the help of following examples.
DROP TABLE num_precision_scale_test; CREATE TABLE num_precision_scale_test( num1 NUMBER(5), num2 NUMBER(5,0), num3 NUMBER(5,2), num4 NUMBER(5,-3) ); INSERT INTO num_precision_scale_test VALUES(12345,12345,123.45,12345000); COMMIT; SELECT * FROM num_precision_scale_test; INSERT INTO num_precision_scale_test VALUES(12345.98,12345.678,123.454,12345); COMMIT; SELECT * FROM num_precision_scale_test;
Result: Table NUM_PRECISION_SCALE_TEST dropped. Table NUM_PRECISION_SCALE_TEST created. 1 row inserted. Commit complete. NUM1 NUM2 NUM3 NUM4 ---------- ---------- ---------- ---------- 12345 12345 123.45 12345000 1 row inserted. Commit complete. NUM1 NUM2 NUM3 NUM4 ---------- ---------- ---------- ---------- 12345 12345 123.45 12345000 12346 12346 123.45 12000
In the above try on, you saw the following thing,
Please note that, 12345.678 became 12346 with datatype NUMBER(5,0). It is so as we have .678 after point. So, the nearest approximation is 12346.
However, 123.454 became 123.45 when stored in NUMBER(5,2).
Now that you knew VARCHAR2 and NUMBER, let us talk about DATE datatype in Oracle.
DATE Datatype in Oracle
DATE datatype contains date and time. Let us take the following example.
SELECT sysdate FROM dual; SYSDATE -------- 02-09-18
The sysdate returns the system date. By defaut, we can not see the time part. We have to specify the date format string with to_char function as follows to get the time.
SELECT to_char(sysdate,'dd-mm-yyyy hh:mm AM') FROM dual; TO_CHAR(SYSDATE,'DD ------------------- 02-09-2018 07:09 AM
We will talk more on working with DATE in the later part of this course. Mow, let us talk about basic SELECT query structure next.