Tipe data Database Oracle

Tipe Data adalah klasifikasi atau jenis dari suatu data. Pada database Oracle ada sedikit perbedan dengan database Mysql yang biasa kita dipakai. Tipe data Oracle diasosiasikan dengan nilai properti yang diset tetap. Properti ini menyebabkan nilai-nilai dari satu tipe data diperlakukan berbeda dengan nilai-nilai lain oleh Oracle. Oracle Database menyediakan beberapa tipe data built-in serta beberapa kategori untuk jenis yang ditentukan oleh pengguna, yang dapat digunakan sebagai tipe data. Penjelasan beberapa tipe data dasar dalam oracle sebagai berikut:

Char (i): String dengan panjang karakter tetap sebesar ukuran i. Tipe data ini mempunyai kemampuan yang hampir sama yakni menyimpan karakter, hanya saja maksimum size yang disimpan hanya sebesar 2000 bytes.

Varchar2 (i): String yang memiliki panjang karakter variable dengan panjang maximal sebesar i. Tipe data ini memperbolehkan penyimpanan semua karakter yang dapat dimasukkan melalui keyboard. Maksimum size yang dapat disimpan sebesar 4000 bytes(karakter). Tipe data ini juga memungkinkan untuk menyimpan data numerik. Biasanya space akan langsung dielimansi jika menggunakan tipe data ini. 

Number(p,s): tipe data number memiliki presisi p dan s digit dibelakang koma Jika kita abaikan p dan s berarti dianggap sebagai tipe number floating point. Tipe data ini menyimpan bilangan integer sampai maksimum dari digit integer tersebut. Bagian kiri menunjukkan presisi dan bagian kanan menunjukkan skala.

Long: Data karakter dengan ukuran panjang bervariasi, hingga mencapai 2 GigaBytes. (tipe data ini tidak dapat digunakan sebagai key).

Raw(size): Tipe data berupa binary data dengan ukuran maksimal 255 bytes

Long Raw: Tipe data berupa binary data dengan ukuran maksimal 2 GigaBytes tidak dikonversi oleh oracle (data mentah apa adanya, spasi dihitung 1 karakter).

Date: Tipe data ini meyimpan waktu dan tanggal dari sebuah informasi, dengan komponen waktu yang dibulatkan ke detik terdekat.  Untuk menampilkannya menjadi sebuah text, harus menggunakan fungsi to_char.

Timestamp: Tipe data ini mirip dengan tipe data date. Di dalam tipe data ini terdapat komponen waktu yang dapat langsung ditampilkan tanpa harus mengubahnya terlebih dahulu dengan fungsi to_char.

Clob: Tipa data ini memperbolehkan penyimpanan objek dengan ukuran yang sangat besar sekitar 4 GB. Clob mirip sekali dengan varchar2 hanya saja tipe data ini mempunyai kapasistas maksimum yang lebih besar.

Blob: Tipe data ini memperbolehkan penyimpanan objek binary yang besar dan tidak terstruktur. Contoh dari tipe data blob adalah suara dan video.

Tipe data Lebih detail sebagai berikut:

Character Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation
char(size)	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Where size is the number of characters to store. Fixed-length strings. Space padded.
nchar(size)	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Where size is the number of characters to store. Fixed-length NLS string Space padded.
nvarchar2(size)	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Maximum size of 4000 bytes.	Where size is the number of characters to store. Variable-length NLS string.
varchar2(size)	Maximum size of 4000 bytes. Maximum size of 32KB in PLSQL.	Maximum size of 4000 bytes. Maximum size of 32KB in PLSQL.	Maximum size of 4000 bytes. Maximum size of 32KB in PLSQL.	Where size is the number of characters to store. Variable-length string.
long	Maximum size of 2GB.	Maximum size of 2GB.	Maximum size of 2GB.	Variable-length strings. (backward compatible)
raw	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Maximum size of 2000 bytes.	Variable-length binary strings
long raw	Maximum size of 2GB.	Maximum size of 2GB.	Maximum size of 2GB.	Variable-length binary strings. (backward compatible)

Numeric Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation
number(p,s)	Precision can range from 1 to 38. Scale can range from -84 to 127.	Precision can range from 1 to 38. Scale can range from -84 to 127.	Precision can range from 1 to 38. Scale can range from -84 to 127.	Where p is the precision and s is the scale. For example, number(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
numeric(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale. For example, numeric(7,2) is a number that has 5 digits before the decimal and 2 digits after the decimal.
dec(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale. For example, dec(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.
decimal(p,s)	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Precision can range from 1 to 38.	Where p is the precision and s is the scale. For example, decimal(3,1) is a number that has 2 digits before the decimal and 1 digit after the decimal.

Date/Time Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation
date	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.	A date between Jan 1, 4712 BC and Dec 31, 9999 AD.
timestamp (fractional seconds precision)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds. For example: timestamp(6)
timestamp (fractional seconds precision) with time zone	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds; with a time zone displacement value. For example: timestamp(5) with time zone
timestamp (fractional seconds precision) with local time zone	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	fractional seconds precision must be a number between 0 and 9. (default is 6)	Includes year, month, day, hour, minute, and seconds; with a time zone expressed as the session time zone. For example: timestamp(4) with local time zone
interval year (year precision) to month	year precision is the number of digits in the year. (default is 2)	year precision is the number of digits in the year. (default is 2)	year precision is the number of digits in the year. (default is 2)	Time period stored in years and months. For example: interval year(4) to month
interval day (day precision) to second (fractional seconds precision)	day precision must be a number between 0 and 9. (default is 2) fractional seconds precision must be a number between 0 and 9. (default is 6)	day precision must be a number between 0 and 9. (default is 2) fractional seconds precision must be a number between 0 and 9. (default is 6)	day precision must be a number between 0 and 9. (default is 2) fractional seconds precision must be a number between 0 and 9. (default is 6)	Time period stored in days, hours, minutes, and seconds. For example: interval day(2) to second(6)

Large Object (LOB) Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation
bfile	Maximum file size of 4GB.	Maximum file size of 232-1 bytes.	Maximum file size of 264-1 bytes.	File locators that point to a binary file on the server file system (outside the database).
blob	Store up to 4GB of binary data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage).	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage).	Stores unstructured binary large objects.
clob	Store up to 4GB of character data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character data.	Stores single-byte and multi-byte character data.
nclob	Store up to 4GB of character text data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data.	Store up to (4 gigabytes -1) * (the value of the CHUNK parameter of LOB storage) of character text data.	Stores Unicode data.

Rowid Datatypes

Data Type Syntax	Oracle 9i	Oracle 10g	Oracle 11g	Explanation
rowid	The format of the rowid is: BBBBBBB.RRRR.FFFFF Where BBBBBBB is the block in the database file; RRRR is the row in the block; FFFFF is the database file.	The format of the rowid is: BBBBBBB.RRRR.FFFFF Where BBBBBBB is the block in the database file; RRRR is the row in the block; FFFFF is the database file.	The format of the rowid is: BBBBBBB.RRRR.FFFFF Where BBBBBBB is the block in the database file; RRRR is the row in the block; FFFFF is the database file.	Fixed-length binary data. Every record in the database has a physical address or rowid.
urowid(size)				Universal rowid. Where size is optional.

Sumber: Referensi tipe data oracle dapat dilihat di https://www.techonthenet.com/oracle/datatypes.php