Datatypes

2 Datatypes

Oracle provides numerous datatypes that should port well to other programming languages. Other datatypes exist whose main purpose is to support embedded SQL programs. The ones listed here are commonly encountered in Oracle and should work for SQL*Plus and any Oracle SQL precompiler.

INTEGER - Maximum of 38 digits of precision

NUMBER(p, s) - Can store integers and real numbers

VARCHAR(size) - Same as VARCHAR2, but use of VARCHAR2 instead is recommended for future versions of Oracle

VARCHAR2(size) - Variable length character string having maximum length size. The maximum value for size is 4000 and the minimum is 1. There is no default value. You have to specify size for this type

LONG - Character data of variable length up to 2 gigabytes

DATE - Valid dates are between January 1, 4712 BC and December 31, 4712 AD

RAW(size) - Raw binary data of length size bytes. The maximum size is 2000 bytes. A value must be specified for size

LONG RAW - Raw binary data of variable length up to 2 gigabytes

CHAR(size) - Fixed length character data of length size bytes. The maximum value is 2000 and the default and minimum is 1 byte. If a value is shorter than size bytes, the value is blank padded up to the fixed length.

NULL - Means "no value", just like its use in programming languages. It is probably more correct to call it a value rather than a datatype

2.1 NUMBER Datatype

Most of the datatypes are self-explanatory. NUMBER is an exception. To create a NUMBER datatype, use this syntax:

NUMBER(p, s)

p stands for precision (total number of digits) and s stands for scale (number of digits to the right of the decimal point). The p and s are optional. The default precision is 38 and the default scale is 0. The precision can range from 1 to 38 and the scale can range from -84 to 127. If a value exceeds the precision, Oracle will give an error. If it exceeds the scale, Oracle will round it.

Examples:

Input Data Specified As Stored As

1,234.56 NUMBER 1234.56

1,234.56 NUMBER(*,1) 1234.6

1,234.56 NUMBER(6) 1235

1,234.56 NUMBER(3,2) (not accepted, exceeds precision)

1,234.56 NUMBER(6,2) 1,234.56

1,234.56 NUMBER(6,-2) 1200 (round to left of decimal point)

More examples are provided in the Appendix at the end of this set of tutorials.

2.2 LONG Datatype

The LONG datatype is used to store variable length character strings of up to 2 gigabytes. For your homework, chances are you will use VARCHAR, VARCHAR2, or CHAR instead.

2.3 DATE Datatype

Even though dates are stored as integers (e.g., 12 for December), Oracle provides a datatype that already stores dates. Actually, the DATE datatype stores more than the date. It stores all of the following information:

century

year

month

hour

minute

second

There is no such thing as a date literal. Instead, you must define it as a character or a numeric value and then use the TO_DATE function to convert it. A function called SYSDATE exists that returns the current date and time.

DATE datatypes can be added or subtracted, but not multiplied or divided. Other functions exist like ADD_MONTHS or MONTHS_BETWEEN that adds the number of months to a DATE datatype or calculates the number of months in between two dates, respectively. For more information, see the section on Functions.

2.4 RAW and LONG RAW Datatypes

Both of these datatypes are used to store binary data, but you will not need these for your homework. Some examples of binary data are graphic files and digitized sound. As these are new features to database management systems in general, support for them is rather limited other than storing and retrieving such data.

2.5 NULL

As in programming languages, null is used to indicate that a column has no value. Null is not zero, and null + 10 = null. Actually, except for concatenation, all operators return null when given a null operand.

Group functions may ignore null, so be careful! For example, the average of (1000, null, null, null, 2000) is 1500 since (1000 + 2000)/2 = 1500.

Only two comparison operators exist for null. They are IS NULL and IS NOT NULL.

All of you should already know truth tables with TRUE and FALSE. Consider NULL to be a third value. The NOT, AND, and OR truth tables for Oracle appear at the end of this document.

2.6 External and Internal Datatypes

Table 1. Default External Datatype Assignments for C/C++ Types

C/C++ Type, or Pseudotype
(these can be used in
a DECLARE SECTION) Oracle External Type
(these cannot be used in a DECLARE SECTION;
they are used by the precompiler)

char, char[n], char*

VARCHAR2

CHARZ

STRING

CHARF

(DBMS=V6 default or CHAR_MAP=VARCHAR2)

(DBMS=V7, V8 default)

(CHAR_MAP=STRING)

(CHAR_MAP=CHARF) ***

int, int*

INTEGER

short, short*

INTEGER

long, long*

INTEGER

float, float*

FLOAT

double, double*

FLOAT

VARCHAR*,

VARCHAR[n]

VARCHAR
VARCHAR is the same as VARCHAR2

Source: Advanced Topics. Pro*C/C++ Precompiler Programmer's Guide Release 8.1.6. 18 May 2001.

*** These are possible precompiler settings. For all versions of Oracle 8, the default external datatype for char, char[n], and char* is CHARZ.

Table 2. C/C++ Datatypes Normally Used with each External Datatype

External Datatype Code C/C++ Datatype

VARCHAR2

1

char[n]

NUMBER

2

char[n] ( n <= 22)

INTEGER

3

int

FLOAT

4

float

STRING

5

char[n+1]

LONG

8

char[n]

VARCHAR

9

char[n+2]

DATE

12

char[n]

RAW

23

unsigned char[n]

LONG RAW

24

unsigned char[n]

CHAR

96

char[n]

CHARF (same as CHAR)

96

char[n]

CHARZ

97

char[n+1]

External Datatype	Code	C/C++ Datatype
VARCHAR2	1	char[n]
NUMBER	2	char[n] ( n <= 22)
INTEGER	3	int
FLOAT	4	float
STRING	5	char[n+1]
LONG	8	char[n]
VARCHAR	9	char[n+2]
DATE	12	char[n]
RAW	23	unsigned char[n]
LONG RAW	24	unsigned char[n]
CHAR	96	char[n]
CHARF (same as CHAR)	96	char[n]
CHARZ	97	char[n+1]

Source: Oracle Dynamic SQL: Method 4. Pro*C/C++ Precompiler Programmer's Guide Release 8.1.6. 18 May 2001.

Table 3. Implicit Conversion Possibilities between Oracle External and Internal Datatypes

EXTERNAL
DATATYPES INTERNAL DATATYPES (these types are used for table columns)

VARCHAR2 NUMBER LONG DATE RAW LONG RAW CHAR

VARCHAR

I/O

I/O

I/O

I/O(1)

I/O(2)

I/O(2)

NUMBER

I/O(3)

I/O

I

I/O(3)

INTEGER

I/O(3)

I/O

I

I/O(3)

FLOAT

I/O(3)

I/O

I

I/O(3)

STRING

I/O

I/O

I/O

I/O(1)

I/O(2)

I/O(2, 4)

I/O

LONG

I/O

I/O

I/O

I/O(1)

I/O(2)

I/O(2, 4)

I/O

VARCHAR

I/O

I/O

I/O

I/O(1)

I/O(2)

I/O(2, 4)

I/O

DATE

I/O

I

I/O

I/O

RAW

I/O(5)

I(4, 5)

I/O

I/O

I/O(5)

LONG RAW

O(5)

I(4, 5)

I/O

I/O

O(5)

CHAR

I/O

I/O

I/O

I/O(1)

I/O(2)

I(2)

I/O

CHARZ

I/O

I/O

I/O

I/O(1)

I/O(2)

I(2)

I/O

Notes:

(1) For input, host string must be in the Oracle DATE character format.
On output, column value is returned in Oracle DATE format.
(2) For input, host string must be in hex format.
On output, column value is returned in hex format.
(3) For output, column value must represent a valid number.
(4) Length must be less than or equal to 2000.
(5) On input, column value is stored in hex format.
On output, column value must be in hex format.

Legend:

I = Conversion valid for input only (from external to internal)

O = Conversion valid for output only (from internal to external)

I/O = Conversion valid for input or output

EXTERNAL DATATYPES	INTERNAL DATATYPES (these types are used for table columns)
VARCHAR2	NUMBER	LONG	DATE	RAW	LONG RAW	CHAR
VARCHAR	I/O	I/O	I/O	I/O(1)	I/O(2)	I/O(2)
NUMBER	I/O(3)	I/O	I				I/O(3)
INTEGER	I/O(3)	I/O	I				I/O(3)
FLOAT	I/O(3)	I/O	I				I/O(3)
STRING	I/O	I/O	I/O	I/O(1)	I/O(2)	I/O(2, 4)	I/O
LONG	I/O	I/O	I/O	I/O(1)	I/O(2)	I/O(2, 4)	I/O
VARCHAR	I/O	I/O	I/O	I/O(1)	I/O(2)	I/O(2, 4)	I/O
DATE	I/O		I	I/O			I/O
RAW	I/O(5)		I(4, 5)		I/O	I/O	I/O(5)
LONG RAW	O(5)		I(4, 5)		I/O	I/O	O(5)
CHAR	I/O	I/O	I/O	I/O(1)	I/O(2)	I(2)	I/O
CHARZ	I/O	I/O	I/O	I/O(1)	I/O(2)	I(2)	I/O
Notes: (1) For input, host string must be in the Oracle DATE character format. On output, column value is returned in Oracle DATE format. (2) For input, host string must be in hex format. On output, column value is returned in hex format. (3) For output, column value must represent a valid number. (4) Length must be less than or equal to 2000. (5) On input, column value is stored in hex format. On output, column value must be in hex format.	Legend: I = Conversion valid for input only (from external to internal) O = Conversion valid for output only (from internal to external) I/O = Conversion valid for input or output

Source: Datatypes. Oracle Call Interface Programmer's Guide Release 8.1.6. 18 May 2001.

Input Data	Specified As	Stored As
1,234.56	NUMBER	1234.56
1,234.56	NUMBER(*,1)	1234.6
1,234.56	NUMBER(6)	1235
1,234.56	NUMBER(3,2)	(not accepted, exceeds precision)
1,234.56	NUMBER(6,2)	1,234.56
1,234.56	NUMBER(6,-2)	1200 (round to left of decimal point)

C/C++ Type, or Pseudotype (these can be used in a DECLARE SECTION)	Oracle External Type (these cannot be used in a DECLARE SECTION; they are used by the precompiler)
char, char[n], char*	VARCHAR2 CHARZ STRING CHARF	(DBMS=V6 default or CHAR_MAP=VARCHAR2) (DBMS=V7, V8 default) (CHAR_MAP=STRING) (CHAR_MAP=CHARF) ***
int, int*	INTEGER
short, short*	INTEGER
long, long*	INTEGER
float, float*	FLOAT
double, double*	FLOAT
VARCHAR*, VARCHAR[n]	VARCHAR	VARCHAR is the same as VARCHAR2