Datatypes

When you create a table or cluster, you must specify an internal datatype for each of its columns. When you create a procedure or stored function, you must specify an internal datatype for each of its arguments. These datatypes define the domain of values that each column can contain or each argument can have. For example, DATE columns cannot accept the value February 29 (except for a leap year) or the values 2 or 'SHOE'. Each value subsequently placed in a column assumes the column's datatype. For example, if you insert '01-JAN-92' into a DATE column, Oracle7 treats the '01-JAN-92' character string as a DATE value after verifying that it translates to a valid date.

Table 2 - 1 summarizes Oracle7 internal datatypes. The rest of this section describes these datatypes in detail.

Note: The Oracle precompilers recognize other datatypes in embedded SQL programs. These datatypes are called external datatypes and are associated with host variables. Do not confuse the internal datatypes with external datatypes. For information on external datatypes, including how Oracle7 converts between internal and external datatypes, see Programmer's Guide to the Oracle Precompilers.

Code	Internal Datatype	Description
1	VARCHAR2(size)	Variable length character string having maximum length size bytes. Maximum size is 2000, and minimum is 1. You must specify size for a VARCHAR2
2	NUMBER(p,s)	Number having precision p and scale s. The precision p can range from 1 to 38. The scale s can range from -84 to 127.
8	LONG	Character data of variable length up to 2 gigabytes, or 231 -1 bytes.
12	DATE	Valid date range from January 1, 4712 BC to December 31, 4712 AD.
23	RAW(size)	Raw binary data of length size bytes. Maximum size is 255 bytes. You must specify size for a RAW value.
24	LONG RAW	Raw binary data of variable length up to 2 gigabytes.
69	ROWID (see note below)	Hexadecimal string representing the unique address of a row in its table. This datatype is primarily for values returned by the ROWID pseudocolumn.
96	CHAR(size)	Fixed length character data of length size bytes. Maximum size is 255. Default and minimum size is 1 byte.
106	MLSLABEL	Binary format of an operating system label. This datatype is used with Trusted Oracle7.

Table 2 - 1. Internal Datatype Summary

The codes listed for the datatypes are used internally by Oracle7. The datatype code of a column is returned when you use the DUMP function.

Note: The DESCRIBE embedded SQL command and the ODESCR call of the Oracle Call Interfaces (OCIs) returns a code of 11 for the ROWID datatype.

Character Datatypes

Character data is stored in strings with byte values corresponding to the character set, such as 7-bit ASCII or EBCDIC Code Page 500, specified when the database was created. Oracle7 supports both single-byte and multi-byte character sets.

These datatypes are used for character data:

• CHAR

• VARCHAR2

CHAR Datatype

The default for a CHAR column is 1 character and the maximum allowed is 255 characters. A zero-length string can be inserted into a CHAR column, but the column is blank-padded to 1 character when used in comparisons. For information on comparison semantics, see the section "Datatype Comparison Rules" .

VARCHAR2 Datatype

You must specify a maximum length for a VARCHAR2 column. The maximum length of VARCHAR2 data is 2000 bytes. Oracle7 compares VARCHAR2 values using non-padded comparison semantics. For information on comparison semantics, see the section "Datatype Comparison Rules" .

VARCHAR Datatype

NUMBER Datatype

You can specify a fixed point number using the following form:

NUMBER(p,s)

where:

p

is the precision, or the total number of digits. Oracle7 guarantees the portability of numbers with precision ranging from 1 to 38.

s

is the scale, or the number of digits to the right of the decimal point. The scale can range from -84 to 127.

You specify an integer using the following form:

NUMBER(p)

is a fixed point number with precision p and scale 0. (Equivalent to NUMBER(p,0).)

You specify a floating point number using the following form:

NUMBER

is a floating point number with precision 38. Note that a scale value is not applicable for floating point numbers.

Scale and Precision

The following examples show how Oracle7 stores data using different precisions and scales.

Actual Data

Specified As

Stored As

7456123.89

NUMBER

7456123.89

7456123.89

NUMBER(9)

7456124

7456123.89

NUMBER(9,2)

7456123.89

7456123.89

NUMBER(9,1)

7456123.9

7456123.8

NUMBER(6)

exceeds precision

7456123.8

NUMBER(15,1)

7456123.8

7456123.89

NUMBER(7,-2)

7456100

7456123.89

NUMBER(-7,2)

exceeds precision

Negative Scale

Scale Greater than Precision

Actual Data

Specified As

Stored As

.01234

NUMBER(4,5)

.01234

.00012

NUMBER(4,5)

.00012

.000127

NUMBER(4,5)

.00013

.0000012

NUMBER(2,7)

.0000012

.00000123

NUMBER(2,7)

.0000012

Floating Point Numbers

You can specify floating point numbers with the appropriate forms of the NUMBER datatype discussed in the section "NUMBER Datatype" . Oracle7 also supports the ANSI datatype FLOAT. You can specify this datatype using one of these syntactic forms:

FLOAT

specifies a floating point number with decimal precision 38, or a binary precision of 126.

FLOAT(b)

specifies a floating point number with binary precision b. The precision b can range from 1 to 126.

To convert from binary to decimal precision, multiply b by 0.30103. To convert from decimal to binary precision, multiply the decimal precision by 3.32193. The maximum of 126 digits of binary precision is roughly equivalent to 38 digits of decimal precision.

LONG Datatype

You can reference LONG columns in SQL statements in these places:

• SELECT lists

• SET clauses of UPDATE statements

• VALUES clauses of INSERT statements

• A table cannot contain more than one LONG column.

• LONG columns cannot appear in integrity constraints (except for NULL and NOT NULL constraints).

• LONG columns cannot be indexed.

• A stored function cannot return a LONG value.

• Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.

• CREATE SNAPSHOT

• WHERE, GROUP BY, ORDER BY, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements

• UNIQUE clause of a SELECT statement

• the column datatype clause of a CREATE CLUSTER statement

• SQL functions (such as SUBSTR or INSTR)

• expressions or conditions

• select lists of queries containing GROUP BY clauses

• select lists of subqueries or queries combined by set operators

• select lists of CREATE TABLE AS SELECT statements

• select lists in subqueries in INSERT statements

• A SQL statement within a trigger can insert data into a LONG column.

• If data from a LONG column can be converted to a constrained datatype (such as CHAR and VARCHAR2), a LONG column can be referenced in a SQL statement within a trigger. Note that the maximum length for these datatypes is 32 Kbytes.

• Variables in triggers cannot be declared using the LONG datatype.

• :NEW and :OLD cannot be used with LONG columns.

DATE Datatype

For each DATE value the following information is stored:

• century

• year

• month

• day

• hour

• minute

• second

If you specify a date value without a time component, the default time is 12:00:00a.m. (midnight). If you specify a date value without a date, the default date is the first day of the current month.

The date function SYSDATE returns the current date and time. For information on the SYSDATE and TO_DATE functions and the default date format, see Chapter 3 "Operators, Functions, Expressions, Conditions" of this manual.

Date Arithmetic

Oracle7 provides functions for many of the common date operations. For example, the ADD_MONTHS function allows you to add or subtract months from a date. The MONTHS_BETWEEN function returns the number of months between two dates. The fractional portion of the result represents that portion of a 31-day month. For more information on date functions, see the section "Date Functions" .

Because each date contains a time component, most results of date operations include a fraction. This fraction means a portion of one day. For example, 1.5 days is 36 hours.

Using Julian Dates

Example

This statement returns the Julian equivalent of January 1, 1992:

SELECT TO_CHAR(TO_DATE('01-01-1992', 'MM-DD-YYYY'),'J')
    FROM DUAL
TO_CHAR(TO_DATE('01-01-1992','MM-DD-YYYY),'J')
----------------------------------------------
2448623

RAW and LONG RAW Datatypes

RAW is a variable-length datatype like the VARCHAR2 character datatype, except that SQL*Net (which connects user sessions to the instance) and the Import and Export utilities do not perform character conversion when transmitting RAW or LONG RAW data. In contrast, SQL*Net and Import/Export automatically convert CHAR, VARCHAR2, and LONG data between the database character set to the user session character set (set by the NLS_LANGUAGE parameter of the ALTER SESSION command), if the two character sets are different.

When Oracle automatically converts RAW or LONG RAW data to and from CHAR data, the binary data is represented in hexadecimal form with one hexadecimal character representing every four bits of RAW data. For example, one byte of RAW data with bits 11001011 is displayed and entered as 'CB'.

LONG RAW data cannot be indexed, but RAW data can be indexed.

ROWID Datatype

Character values representing ROWIDs:

block.row.file

where:

block

is a hexadecimal string identifying the data block of the data file containing the row. The length of this string may vary depending on your operating system.

row

is a four-digit hexadecimal string identifying the row in the data block. The first row in the block has the number 0.

file

is a hexadecimal string identifying the database file containing the row. The first data file has the number 1. The length of this string may vary depending on your operating system.

Example

Consider this ROWID value:

0000000F.0000.0002

The row corresponding to this ROWID is the first row (0000) in the fifteenth data block (0000000F) of the second data file (0002).

MLSLABEL Datatype

ANSI, DB2, and SQL/DS Datatypes

ANSI SQL Datatype

Oracle7 Datatype

CHARACTER(n) CHAR(n)

CHAR(n)

CHARACTER VARYING(n) CHAR VARYING(n)

VARCHAR(n)

NUMERIC(p,s) DECIMAL(p,s)

NUMBER(p,s)

INTEGER INT SMALLINT

NUMBER(38)

FLOAT(b) ² DOUBLE PRECISION ³ REAL ⁴

NUMBER

Table 2 - 2. ANSI Datatypes Converted to Oracle7 Datatypes

SQL/DS or DB2 Datatype

Oracle7 Datatype

CHARACTER(n)

CHAR(n)

VARCHAR(n)

VARCHAR(n)

LONG VARCHAR(n)

LONG

DECIMAL(p,s) ¹

NUMBER(p,s)

INTEGER SMALLINT

NUMBER(38)

FLOAT(b) ²

NUMBER

Table 2 - 3. SQL/DS and DB2 Datatypes Converted to Oracle7 Datatypes

¹ The NUMERIC, DECIMAL, and DEC datatypes can specify only fixed point numbers. For these datatypes, s defaults to 0. ² The FLOAT datatype is a floating point number with a binary precision b. This default precision for this datatype is 126 binary, or 38 decimal. ³ The DOUBLE PRECISION datatype is a floating point number with binary precision 126. ⁴ The REAL datatype is a floating point number with a binary precision of 63, or 18 decimal.

Do not define columns with these SQL/DS and DB2 datatypes because they have no corresponding Oracle7 datatype:

• GRAPHIC

• LONG VARGRAPHIC

• VARGRAPHIC

• TIME

• TIMESTAMP

Datatype Comparison Rules

Number Values

Date Values

Character String Values

• blank-padded comparison semantics

• non-padded comparison semantics

The results of blank-padded and non-padded comparisons is shown in Table 2 - 4. Usually, the results of blank-padded and non-padded comparisons are the same. However, note the exception highlighted in bold in Table 2 - 4 where blanks are considered less than any character, which is true in most character sets.

Blank-Padded

Non-Padded

'ab' > 'aa'

'ab' > 'aa'

'ab' > 'a?'

'ab' > 'a?'

'ab' > 'a'

'ab' > 'a'

'ab' = 'ab'

'ab' = 'ab'

'a?' = 'a'

'a?' > 'a'

Table 2 - 4. Results of Comparisons with Blank-Padded and Non-Padded Comparison Semantics

Blank-Padded Comparison Semantics If the two values have different lengths, Oracle7 first adds blanks to the end of the shorter one so their lengths are equal. Oracle7 then compares the values character by character up to the first character that differs. The value with the greater character in the first differing position is considered greater. If two values have no differing characters, then they are considered equal. This rule means that two values are equal if they differ only in the number of trailing blanks. Oracle7 uses blank-padded comparison semantics only when both values in the comparison are either expressions of datatype CHAR, text literals, or values returned by the USER function.

Non-Padded Comparison Semantics Oracle7 compares two values character by character up to the first character that differs. The value with the greater character in that position is considered greater. If two values of different length are identical up to the end of the shorter one, the longer value is considered greater. If two values of equal length have no differing characters, then the values are considered equal. Oracle7 uses non-padded comparison semantics whenever one or both values in the comparison have the datatype VARCHAR2.

Single Characters

These are some common character sets:

• 7-bit ASCII (American Standard Code for Information Interchange)

• EBCDIC (Extended Binary Coded Decimal Interchange Code) Code Page 500

• ISO 8859/1 (International Standards Organization)

• JEUC Japan Extended UNIX

ASCII Character Set

Decimal value

Symbol

Decimal value

Symbol

32

blank

59

;

33

!

60

<

34

"

61

=

35

#

62

>

36

$

63

?

37

%

64

@

38

&

65-90

A-Z

39

'

91

[

40

(

92

\

41

)

93

]

42

*

94

^^

43

+

95

_

44

,

96

`

45

-

97-122

a-z

46

.

123

{

47

/

124

|

48-57

0-9

125

}

58

:

126

~

Table 2 - 5. ASCII Character Set

EBCDIC Character Set

Decimal value

Symbol

Decimal value

Symbol

64

blank

108

%

74

¢

109

_

75

.

110

>

76

<

111

?

77

(

122

:

78

+

123

#

79

|

124

@

80

&

125

'

90

!

126

=

91

$

127

"

92

*

129-137

a-i

93

)

145-153

j-r

94

;

162-169

s-z

95

¬

193-201

A-I

96

-

209-217

J-R

97

/

226-233

S-Z

Table 2 - 6. EBCDIC Character Set

Data Conversion

Implicit Data Conversion

• When an INSERT or UPDATE statement assigns a value of one datatype to a column of another, Oracle7 converts the value to the datatype of the column.

• When you use a SQL function or operator with an argument with a datatype other than the one it accepts, Oracle7 converts the argument to the accepted datatype.

• When you use a comparison operator on values of different datatypes, Oracle7 converts one of the expressions to the datatype of the other.

The text literal '10' has datatype CHAR. Oracle7 implicitly converts it to the NUMBER datatype if it appears in a numeric expression as in the following statement:

SELECT sal + '10'

    FROM emp

Example II

When a condition compares a character value and a NUMBER value, Oracle7 implicitly converts the character value to a NUMBER value, rather than converting the NUMBER value to a character value. in the following statement, Oracle7 implicitly converts '7936' to 7936:

SELECT ename

    FROM emp 

	WHERE empno = '7936'

If the character value is too short to fit the entire number value, the number value is rounded.

Example III

In the following statement, Oracle7 implicitly converts '12-MAR-1993' to a DATE value using the default date format 'DD-MON-YYYY':

SELECT ename

    FROM emp 

	WHERE hiredate = '12-MAR-1993'

Example IV

In the following statement, Oracle7 implicitly converts the text literal '00002514.0001.0001' to a ROWID value:

SELECT ename

    FROM emp 

	WHERE ROWID = '00002514.0001.0001'

Explicit Data Conversion

TO FROM

CHAR

NUMBER

DATE

RAW

ROWID

CHAR

unnecessary

TO_NUMBER

TO_DATE

HEXTORAW

CHARTOROWID

NUMBER

TO_CHAR

unnecessary

TO_DATE (number,'J')

DATE

TO_CHAR

TO_CHAR (date,'J')

unnecessary

RAW

RAWTOHEX

unnecessary

ROWID

ROWIDTOCHAR

unnecessary

Table 2 - 7. SQL Functions for Datatype Conversion

For information on these functions, see the section "Conversion Functions" .

Note: Note that Table 2 - 7 does not show conversions from LONG and LONG RAW values because it is impossible to specify LONG and LONG RAW values in cases in which Oracle7 can perform implicit datatype conversion. For example, LONG and LONG RAW values cannot appear in expressions with functions or operators. For information on the limitations on LONG and LONG RAW datatypes, see the section "LONG Datatype" .

Implicit vs. Explicit Data Conversion

• SQL statements are easier to understand when you use explicit datatype conversions functions.

• Automatic datatype conversion can have a negative impact on performance, especially if the datatype of a column value is converted to that of a constant rather than the other way around.

• Implicit conversion depends on the context in which it occurs and may not work the same way in every case.

• Algorithms for implicit conversion are subject to change across software releases and among Oracle products. Behavior of explicit conversions is more predictable.