Oracle Tips by Burleson

SQL Functions

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 returned.    

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 data type: 

create table customer
(

   customer_name     varchar(50),
   birth_date        date,
   income            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.

char

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.

varchar2

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 problematic.

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” < “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.

long

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.

number(p,s)

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 optional.

The ANSI  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).

date

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.

timestamp(p)

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:

Easy Oracle SQL

Get Started Fast writing SQL Reports with SQL*Plus

ISBN 0-9727513-7-8

Col. John Garmany 

http://www.rampant-books.com/book_2005_1_easy_sql.htm