Oracle SQL: Datatypes

Some SQL and PL/SQL data types include:

Comment

binary_float, binary_double

Until 21c: only in PL/SQL, starting with 23c also in regular SQL (and notably as datatype for table columns).

char, nchar

Max size in PL/SQL: 32767 bytes, max size in Oracle SQL: 2000 bytes

json

A binary native representation for JSON data, «OSON», designed for fast query and update. (21c)

clob and blobs (blob and clob)

long

pls_integer

varchar2, nvarchar2

Max size in PL/SQL: 32767 bytes, max size in Oracle SQL: 4000 bytes

Datatypes for text

Datatypes that are used to store text (characters) are varchar2, char, long

Datatypes to store binary data (arrays of bytes) are raw and long raw.

Some identical data types are:

The SQL function validate_conversion can be used to check if a value can be converted to a given target data type.

dbms_types and dbms_sql export type code constants. At times their codes (numbers) are equal, sometimes, they're not.

dbms_types		dbms_sql		Comment
`typecode_varchar`	1	`varchar2_type`	1	`varchar` vs `varchar2` ??? Compare with `typecode_varchar2`!
`typecode_varchar2`	9
`typecode_number`	2	`number_type`	2
		`long_type`	8
		`rowid_type`	11
`typecode_date`	12	`date_type`	12	Compare with the «external data type 13».
		`raw_type`	23
`typecode_raw`	95
		`long_raw_type`	24
`typecode_char`	96	`char_type`	96
`typecode_bfloat`	100	`binary_float_type`	100
`typecode_bdouble`	101	`binary_double_type`	101
		`mlslabel_type`	106
		`user_defined_type`	109
`typecode_ref`	110	`ref_type`	111
`typecode_clob`	112	`clob_type`	112
`typecode_blob`	113	`blob_type`	113
`typecode_bfile`	114	`bfile_type`	114
`typecode_timestamp`	187	`timestamp_type`	180
`typecode_timestamp_tz`	188	`timestamp_with_tz_type`	181
`typecode_interval_ym`	189	`interval_year_to_month_type`	182
`typecode_interval_ds`	190	`interval_day_to_second_type`	183
`typecode_urowid`	104	`urowid_type`	208
`typecode_timestamp_ltz`	232	`timestamp_with_local_tz_type`	231
`typecode_cfile`	115
`typecode_object`	108
`typecode_varray`	247			collection type
`typecode_table`	248			collection type
`typecode_namedcollection`	122
`typecode_opaque`	58			opaque type
`typecode_nchar`	286			Any data API, short form for char with a charset form of SQLCS_NCHAR
`typecode_nvarchar2`	287			Any data API, short form for char with a charset form of SQLCS_NCHAR
`typecode_nclob`	288			Any data API, short form for char with a charset form of SQLCS_NCHAR