Most Important Datatypes in Oracle


Oracle SQL Types

1.CHAR[(length [BYTE | CHAR])]:

Fixed-length character data of length bytes or characters and padded with trailing spaces. Maximum length is 2,000 bytes.


2.VARCHAR2(length [BYTE | CHAR]):

Variable-length character data of up to length bytes or characters. Maximum length is 4,000 bytes.


3.NUMBER(precision, scale) and NUMERIC(precision, scale):

Variable-length number; precision is the maximum number of digits (in front of and behind a decimal point, if used) that may be used for the number. The maximum precision supported is 38; scale is the maximum number of digits to the right of a decimal point (if used). If neither precision nor scale is specified, then a number with up to a precision and scale of 38 digits may be supplied (meaning you can supply a number with up to 38 digits, and any of those 38 digits may be in front of or behind the decimal point).

4.DATE:

Date and time with the century, all four digits of year, month, day, hour (in 24-hour format), minute, and second. May be used to store a date and time between January 1, 4712 B.C. and December 31, 4712 A.D. Default format is specified by the NLS_DATE_FORMAT parameter (for example: DD-MON-RR).


5.INTERVAL DAY[(days precision)] TO SECOND[(seconds precision)]:

Time interval measured in days and seconds; days precision specifies the precision for the days, which is an integer from 0 to 9 (default is 2); seconds precision specifies the precision for the fractional part of the seconds, which is an integer from 0 to 9 (default is 6). Can be used to represent a positive or negative time interval.


6.TIMESTAMP[(seconds precision)]:

Date and time with the century, all four digits of year, month, day, hour (in 24-hour format), minute, and second; seconds precision specifies the number of digits for the fractional part of the seconds, which can be an integer from 0 to 9 (default is 6). Default format is specified by the NLS_TIMESTAMP_FORMAT parameter.


7.TIMESTAMP[(seconds precision)] WITH TIME ZONE:

Extends TIMESTAMP to store a time zone. The time zone can be an offset from UTC, such as ‘-5:0', or a region name, such as ‘US/Pacific'. Default format is specified by the NLS_TIMESTAMP_TZ_FORMAT parameter.


8.TIMESTAMP[(seconds precision)] WITH LOCAL TIME ZONE:

Extends TIMESTAMP to convert a supplied date time to the local time zone set for the database. The process of conversion is known as normalizing the date time. Default format is specified by the NLS_TIMESTAMP_FORMAT parameter.


9.CLOB:Variable length single-byte character data of up to 128 terabytes.


10.NCLOB: Variable length Unicode national character set data of up to 1

28 terabytes.


11.RAW(length):Variable length binary data of up to length bytes. Maximum length is 2,000 bytes. Super ceded by BLOB type, but supported for backwards.





#DBMS #OracleDatatypes #Databaseassignmenthelp

12 views0 comments

Recent Posts

See All