Oracle Data Types

1
Oracle Data Types
Kristin Higgins
2
Data Storage and Variable Types

• The bits that are stored in memory are
  interpreted in different ways depending on the
  application interpreting them.
• When creating a table you must assign a data type
  to each column to specify what kind of data will
  be stored in the field

3
Reasons for specifying Data Type 

• Data types dictate the amount of storage space
  allocated
• Data types provide for a means of error checking.
  For example
• You can not store character data such as
• Hello into a Numeric data field

4
Oracle Data Types

• Character Data Types
• char, varchar2, clob, long
• stores character data in ASCII format
• ASCII is an 8 bit binary format that can
  represent a total of 256 characters
• 1 byte / character
• nchar, nvarchar2, nclob
• stores character data in Unicode (16 bit format)
• Alternate alphabets require thousands of
  characters (e.g. Japanese Kanji character set)
• Using 16 bits 216 56,536 characters
• 2 bytes / character

5
Character Data Types

• char(n)
• Fixed length character string
• e.g. char(20) will take up 20 bytes regardless of
  how long the string actually is

6
Character Data Types

• varchar2(n)
• Variable length character string
• Up to 4000 bytes long
• Must specify a field size (n)
• If data entered is smaller than (n) only the data
  will be stored (no padded spaces)
• If data entered is larger than (n) an error is
  returned

7

• varchar2(n) contd
• Why not make all character fields varchar2 ?
• Oracles indexing system limit keys to about
  700 bytes so varchar2 fields larger than this may
  not be indexed

8
Character Data Types

• clob (character large object)
• Variable length character string
• Up to 4 gigabytes long
• Useful for accepting large amounts of unformatted
  textual data
• Regular string functions dont work on clobs
  (but there are identically named functions
  availabe in the DBMS_LOB package same
  parameters in different order ? boo hoo!)

9
Character Data Types

• long (long character data)
• Variable length character string
• Up to 2 gigabytes long
• Useful for accepting large amounts of unformatted
  textual data

10
Numeric Data Types

• Number
• Oracle has only one internal data type that is
  used for storing numbers negative, positive,
  fixed and floating point
• Numbers in the range of 10-130 to 10126 with
  38 digits of precision

11
Numeric Data Types

• number(p)
• Integer
• No digits to the right of the decimal place
• Precision (total number of digits)
• of p
• example
• student_id number(7)

12
Numeric Data Types

• number(p,s)
• Fixed point number
• Precision (total number of decimal digits) of p,
  and scale (digits to the right of the decimal
  point) of s
• example
• item_cost number(9,2)
• will hold a 9 digit number
• 7 positions to the left of the decimal
• 2 positions to the right of the decimal

13
Numeric Data Types

• number
• Floating point value
• Decimal point can appear anywhere in the value
• Allows you to store precise numbers
• example
• student_average number

14
Numeric Data Types

• integer
• Integer
• No digits to the right of the decimal place
• No different in terms of storage and behaviour
  from number(38) but it reads better than
  number(38)
• More in line with ANSI SQL

15
Date Data Type

• date
• Point in time recorded with one-second precision
• Stores century, year, month, day, hour, minute
  and second
• Stores dates in the range of
• January 1, 4712 BC to December 31, 4712 AD

16

• Date contd
• Put in values using to_date function
• Query values using to_char
• Without using functions you are limited to
  specifying the date with the default format mask
• typically DD-MON-YY
• this leads to Y2K bugs
• i.e. is YY of 02 1902 or 2002

17
Binary Data Types

• blob
• Stands for Binary Large OBject
• Stores up to 4 gigs of binary data
• Created to permit the storage of images, sound
  recordings and other inherently binary data

18
Binary Data Types

• bfile
• A binary file
• Stored by the operating system (typically Unix)
  and kept track of by Oracle
• Located outside of the database
• Used by an app that needs information from SQL
  but can only read from standard file