||Oracle Tips by Burleson
In this chapter, we are going to introduce
Oracle’s data types. This defines the set of data that can be stored
in the Oracle database. Next, we will introduce Oracle functions,
starting with single row functions, date functions and progressing
through multi-row functions. Oracle functions provide an
extraordinary capability to manipulate data as it is queried and
But first, we need to discuss Oracle data types.
Oracle has three basic data types; numbers, characters (or strings),
and dates. Each data type has a different set of functions for
manipulation and conversion.
Oracle Data Types
SQL has several standard data types, and we need
to match the data types when we start writing SQL. In this section,
we will introduce the set of data types that are part of the Oracle
database. These data types are called basic or built-in data types
. As we will see near the end of this section, you can use these
basic data type
to create your own data types.
When you create a table, you must define a data
type for each column in that table. For example, here we define a
table with a variable character data type
(VARCHAR), a date data type and a number
The basic Oracle data types fall into the
following categories: character; number; date; LOBs; and RAW. The two
main character data types are the char and the varchar2.
The char data type
defines a fixed length character string. This string is a list of
characters and can be zero or more characters long. A char data type
is stored in the database as the size it was created. If I have a
table column called first_name and I define it as a char(30), I can
store any name in that column as long as it does not exceed 30
characters. If I store ‘Sam’ in that column, Oracle will pad Sam with
spaces to fill the 30 characters.
Currently, Oracle limits the size of a char data
type to 2000 characters (char(2000)). When the database
compares a char data type, the trailing spaces are ignored.
The varchar2 is the most common and
flexible character data type. The varchar2 is similar to the char
except it does not pad the data with spaces. If I create a column
called last_name as a varchar2(60), and I load the name “Thumb”, only
five character spaces are used. If I later modify the data to “Thumbbanger,”
the field in the database will grow to hold the larger name.
The advantage is that space is not wasted in the
database filling the field with spaces. If I create a varchar2(60)
and a varchar2(1000) and place ‘Sam’ in both, they will take up the
same amount of space in the database.
The maximum size of a varchar2 in a table column
is 4000 characters. So, why not make all my character columns
varchar2(4000)s? Because you don’t want that much data in each of
those fields. You would have to insure that your application could
handle a returning 4000 byte field. This would quickly become
Because the database does not pad the field with
spaces, it assumes that a space at the end of the field was placed
there on purpose and includes it in any comparison operation (“Bob” <
Note that varchar and varchar2 are synonyms.
However, varchar has been depreciated and may be removed from future
versions, so you should not use it.
nchar and nvarchar2
National character set strings. Both of these
are char and varchar2 data types support native languages of the
users. These are used if we have to support multi-byte character sets
like Chinese Kanji.
The long data type is
depreciated and may not be available in future releases. You should
not use Long data types. It is a bigger version of a varchar2 that
could hold up to 2 Gigabytes.
A number data type is
very versatile. In most languages, you must define the precision of a
number by declaring it as an integer, double, flout, etc. In the
Oracle database, all of these types are incorporated into the number
data type. It stores zero, positive, negative, fixed or
floating-point numbers. Precision can range from one to 38. Scale
can range from –84 to 127. Oracle numbers can sometimes be
confusing. The database stores the number as entered. Precision and
scale settings do not affect how the number is stored in the database;
however, if you exceed the defined precision the database will return
a numeric error. This can lead to cases where a value returned has
some of its fractional part rounded (scale too small), but when that
field is used in an equation, the fractional part is still there.
Remember, defining the precision and scale are
numbered data types are supported;
however, they are mapped to a number data type
. For example, if you create a column as an INTEGER (an ANSI data
type), the database would create it as a number(38).
The date data type was
introduced in the last chapter. It stores date and time as an
internal number and can store a date up to a one-second time
precision. However, if you try to use a date data type to time
events, you cannot capture the fractional second data. In SQL*Plus and some
applications, you insert a date by converting the character
representation into the date using a function called to_date. Some
languages (Java for instance) can pass a date object directly into the
database with having to convert it.
The timestamp data type
was introduced in Oracle9i. It functions similarly to the date data
type, except that it maintains fractional seconds to a
precision from zero to nine, with the default being six. The
timestamp data type is only used when fractional seconds are needed,
such as records of very small units of time like milliseconds.
The above book excerpt is from:
Fast writing SQL Reports with SQL*Plus
Col. John Garmany