Data Types In Oracle SQL - RAW, LONG RAW, DATE AND TIME Part 3
In this tutorial i am going to explain about data types, RAW, LONG RAW, LONG, DATE and TIMESTAMP in Oracle. The previous tutorials are available here part 1, part 2
RAW And LONG RAW DataTypes :
The RAW and LONG RAW are used to store binary data or byte strings. These are variable-length datatypes like the VARCHAR2 datatype. You can use them to store graphics, sound, documents, or arrays of binary data. As per Oracle, these data types are not subjected to automatic character set conversion between different systems. Some Oracle services such as Oracle Net Services and Import/Export automatically convert CHAR, VARCHAR2 and LONG data from database character set to the user session character set, if the two character sets are different.
The maximum length of RAW datatype is 255 bytes.
Syntax :
Column_name RAW(10)
The maximum length of LONG RAW datatype is 2GB.
Syntax :
Column_name LONG RAW
A table can have only one LONG RAW column. Oracle advises to use BLOB data type instead of LONG RAW datatype for storing binary data.
LONG DataType :
LONG datatype is used to store character data of variable length upto 2 GB. It’s like bigger version of VARCHAR2 datatype. A table can only have one LONG column. You can store text, arrays of characters, and short documents in Long datatype.
Oracle advised to use CLOB or NCLOB datatypes instead of LONG datatype.
Syntax :
Column_name LONG
Some More Restrictions On LONG Datatype :
1. Object types cannot be created on LONG attribute.
2. LONG columns cannot appear in WHERE clauses or in integrity constraints.
3. Indexes cannot be created on LONG columns.
4. LONG can be returned through a function, but not through a stored procedure.
5. It can be declared in PL/SQL but cannot be referenced in SQL.
Date and Time Data Types :
DATE Data Type :
1. The DATE datatype is used to store date and time specific information.
2. It stores the year - including century, month, day, hours, minutes and seconds. Seconds are considered from midnight.
3. The dates can be specified as literals, using the Gregorian Calendar.
4. The default date format is “DD-MON-YY”, and is specified is NLS_DATE_FORMAT. Eg: 22-DEC-1990.
5. The data related to Date will be stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.
6. The Date range that can be stored in Oracle is from January 1, 4712 BC through December 31, 9999 AD.
7. Default format mask is “AD” also known as CE ( Common Era). For storing of dates in BC, use BC in the format mask.
8. Use TO_DATE function to convert dates from Oracle default format to other formats. Eg. TO_DATE (‘December 22, 1990’, ‘MONTH DD, YYYY’).
9. Oracle database stores time in 24-hour format - HH:MI:SS. The default time accepted by Oracle is 00:00:00 A.M.
10. The default date accepted by Oracle is first day of the current month.
11. Use TO_DATE function to enter the time portion of a date with a format mask indicating the time portion. eg: TO_DATE(‘22-DEC-90 12:00 A.M’, ‘DD-MON-YY HH:MI A.M’));
DATETIME Data Type :
1. The DATETIME data type is used for values that contain both date and time parts.
2. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
3. The DATETIME is best used to store a future event.
TIMESTAMP Data Type :
1. The main problem with Date data type was it’s inability to be granular enough to determine which event might have happened first in relation to another event.
2. The Timestamp data type is an extension to the DATE data type that can store date and time data including fractional seconds.
3. Use cast function to cast Date data type to Timestamp data type. eg: cast(birthdate As TIMESTAMP).
4. Use TO_CHAR function to convert timestamp data type to readable format. TO_CHAR(time,’MM/DD/YYYY HH24:MI:SS:FF3’).
5. It has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
6. TIMESTAMP is usually used to capture the current time.
System Date and System Timestamp :
1. Use SYSDATE function to get System’s date and time. eg. select sysdate from dual;
2. Use SYSTIMESTAMP to get System’s date and time in Timestamp data type.
eg. select systimestamp from dual.
RAW And LONG RAW DataTypes :
The RAW and LONG RAW are used to store binary data or byte strings. These are variable-length datatypes like the VARCHAR2 datatype. You can use them to store graphics, sound, documents, or arrays of binary data. As per Oracle, these data types are not subjected to automatic character set conversion between different systems. Some Oracle services such as Oracle Net Services and Import/Export automatically convert CHAR, VARCHAR2 and LONG data from database character set to the user session character set, if the two character sets are different.
The maximum length of RAW datatype is 255 bytes.
Syntax :
Column_name RAW(10)
The maximum length of LONG RAW datatype is 2GB.
Syntax :
Column_name LONG RAW
A table can have only one LONG RAW column. Oracle advises to use BLOB data type instead of LONG RAW datatype for storing binary data.
LONG DataType :
LONG datatype is used to store character data of variable length upto 2 GB. It’s like bigger version of VARCHAR2 datatype. A table can only have one LONG column. You can store text, arrays of characters, and short documents in Long datatype.
Oracle advised to use CLOB or NCLOB datatypes instead of LONG datatype.
Syntax :
Column_name LONG
Some More Restrictions On LONG Datatype :
1. Object types cannot be created on LONG attribute.
2. LONG columns cannot appear in WHERE clauses or in integrity constraints.
3. Indexes cannot be created on LONG columns.
4. LONG can be returned through a function, but not through a stored procedure.
5. It can be declared in PL/SQL but cannot be referenced in SQL.
Date and Time Data Types :
DATE Data Type :
1. The DATE datatype is used to store date and time specific information.
2. It stores the year - including century, month, day, hours, minutes and seconds. Seconds are considered from midnight.
3. The dates can be specified as literals, using the Gregorian Calendar.
4. The default date format is “DD-MON-YY”, and is specified is NLS_DATE_FORMAT. Eg: 22-DEC-1990.
5. The data related to Date will be stored in fixed-length fields of seven bytes each, corresponding to century, year, month, day, hour, minute, and second.
6. The Date range that can be stored in Oracle is from January 1, 4712 BC through December 31, 9999 AD.
7. Default format mask is “AD” also known as CE ( Common Era). For storing of dates in BC, use BC in the format mask.
8. Use TO_DATE function to convert dates from Oracle default format to other formats. Eg. TO_DATE (‘December 22, 1990’, ‘MONTH DD, YYYY’).
9. Oracle database stores time in 24-hour format - HH:MI:SS. The default time accepted by Oracle is 00:00:00 A.M.
10. The default date accepted by Oracle is first day of the current month.
11. Use TO_DATE function to enter the time portion of a date with a format mask indicating the time portion. eg: TO_DATE(‘22-DEC-90 12:00 A.M’, ‘DD-MON-YY HH:MI A.M’));
DATETIME Data Type :
1. The DATETIME data type is used for values that contain both date and time parts.
2. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
3. The DATETIME is best used to store a future event.
TIMESTAMP Data Type :
1. The main problem with Date data type was it’s inability to be granular enough to determine which event might have happened first in relation to another event.
2. The Timestamp data type is an extension to the DATE data type that can store date and time data including fractional seconds.
3. Use cast function to cast Date data type to Timestamp data type. eg: cast(birthdate As TIMESTAMP).
4. Use TO_CHAR function to convert timestamp data type to readable format. TO_CHAR(time,’MM/DD/YYYY HH24:MI:SS:FF3’).
5. It has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.
6. TIMESTAMP is usually used to capture the current time.
System Date and System Timestamp :
1. Use SYSDATE function to get System’s date and time. eg. select sysdate from dual;
2. Use SYSTIMESTAMP to get System’s date and time in Timestamp data type.
eg. select systimestamp from dual.
Comments
Post a Comment