As you have learned in earlier chapters, every column must be defined to be a valid Oracle datatype. You have already seen many examples of how to use SQL; you've also seen some of the fundamental datatypes: NUMBER, VARCHAR2, and DATE.
This chapter takes a closer look at Oracle's datatypes, examines how they are used in applications, and describes the behavior of columns based on them. In Chapter 11, "Manipulating Strings," Chapter 12, "Dealing with Dates," and Chapter 13, "Handling Numbers," you'll learn about the built-in Oracle functions and operators that can be used for each datatype.
Oracle offers several datatypes for storing numeric data; each is suited for a different purpose.
The NUMBER datatype offers the greatest flexibility for storing numeric data. It accepts positive and negative integers and real numbers and has from 1 to 38 digits of precision. The syntax for specifying the NUMBER datatype is
NUMBER (precision, scale)
where precision is the maximum number of digits to be stored and scale indicates the position of the decimal point number of digits to the right (positive) or left (negative) of the decimal point. The scale can range from X84 to 127.
The INTEGER datatype can store integers that contain up to 38 digits. If you try to store a real number in an INTEGER column, Oracle will round the real number to the nearest integer. The following INSERT statement shows how Oracle rounds the value 120.501 to 103 when it is stored in an INTEGER column.
SQL> insert into integer_demo
2 (record_no, int_value)
3 values
4 (101, 102.501);
1 row created.
SQL> select record_no, int_value
2 from integer_demo;
RECORD_NO INT_VALUE
--------- ---------
101 103
Oracle can store from 1 to 38 digits of precision for a number. The number of bytes required to store a number in an Oracle database depends on how many digits are used to express the number.
The following example inserts a very precise number into a NUMBER column:
SQL> insert into number_demo 2 (record_no, value) 3 values 4 (101, 1234567890.123456789012345678901234567); 1 row created.
If you use SQL*Plus to look at what was actually stored, you may be misled; it appears as though Oracle truncated the fractional part of the number that was inserted.
SQL> select record_no, value
2 from number_demo;
RECORD_NO VALUE
---------- ----------
101 1234567890
However, the apparent truncation is caused by a SQL*Plus system variable--;NUMWIDTH--;whose default value isn't large enough to display the full precision of the value. To see the full precision, you can increase NUMWIDTH.
SQL> set numwidth 38
SQL> select record_no, value
2 from number_demo;
RECORD_NO VALUE
-------------------------------------- --------------------------------------
101 1234567890.123456789012345678901234567
If you limit the precision, Oracle limits the values that can be stored in the column to the defined precision. For instance, suppose you define a table in the following way:
SQL> create table number_digits_demo ( 2 record_no int not null, 3 value number(4)); Table created.
If you store values that meet and exceed the specified number of digits, here's what occurs:
SQL> insert into number_digits_demo
2 (record_no, value)
3 values
4 (101, 9999);
1 row created.
SQL> insert into number_digits_demo
2 (record_no, value)
3 values
4 (101, 10000);
(101, 10000)
*
ERROR at line 4:
ORA-01438: value larger than specified precision allows for this column
The following example shows the use of scale and precision in a numeric column. The table is defined as
SQL> create table scale_precision_demo ( 2 record_no int not null, 3 value number(6,2)); Table created.
In the NUMBER column the precision is 6 and the scale is 2. In other words, out of a total of six digits of precision, Oracle will reserve two digits to the right of the decimal point, leaving a maximum of four digits to the left of the decimal point. Also, the column cannot store more than six digits of precision for any number.
The number 1234.5 can be stored in the NUMBER column:
SQL> insert into scale_precision_demo 2 (record_no, value) 3 values 4 (901, 1234.5); 1 row created.
However, as the following error message indicates, the number 12345.1 can't be stored because it contains five digits to the left of the decimal point.
SQL> insert into scale_precision_demo
2 (record_no, value)
3 values
4 (901, 12345.1);
(901, 12345.1)
*
ERROR at line 4:
ORA-01438: value larger than specified precision allows for this column
Similarly, the number 12345, even though it has only five digits of precision, can't be stored in the NUMBER column because it has five digits to the left of the decimal point.
SQL> insert into scale_precision_demo
2 (record_no, value)
3 values
4 (901, 12345);
(901, 12345)
*
ERROR at line 4:
ORA-01438: value larger than specified precision allows for this column
On the other hand, the number 123.56 is stored successfully:
SQL> insert into scale_precision_demo 2 (record_no, value) 3 values 4 (901, 1234.56); 1 row created.
If you try to store the number 1234.567 in the column, Oracle rounds the fractional portion .567 to .57 so that it fits into two digits--;the specified scale for the column.
SQL> insert into scale_precision_demo
2 (record_no, value)
3 values
4 (901, 1234.567);
1 row created.
SQL> select record_no, value
2 from scale_precision_demo;
RECORD_NO VALUE
---------- ----------
901 1234.5
901 1234.56
901 1234.57
The DECIMAL datatype is really just a synonym for the NUMBER datatype. In the following example, the Decimal_Test table is created with a column named my_decimal defined as decimal(5). If you then describe the table, you'll see that the column is defined as NUMBER(5).
SQL> create table decimal_test ( 2 record_no int not null, 3 my_decimal decimal(5)); Table created. SQL> desc decimal_test Name Null? Type ------------------------------- -------- ---- RECORD_NO NOT NULL NUMBER(38) MY_DECIMAL NUMBER(5)
Oracle's FLOAT datatype is compatible with the ANSI FLOAT type. See Chapter 13 for more details on the FLOAT datatype.
You can assign a string that contains a number to a number column. The NUMBER column in the following table illustrates this type of assignment:
SQL> create table number_demo ( 2 record_no int not null, 3 value number); Table created.
First, insert a single row into the table.
SQL> insert into number_demo 2 (record_no, value) 3 values 4 (101, 2.333); 1 row created.
Next, update this column by setting its value to a string.
SQL> update number_demo
2 set value = '33.33';
1 row updated.
SQL> select record_no, value
2 from number_demo;
RECORD_NO VALUE
--------- ---------
101 33.33
Notice that Oracle automatically converted the string '33.33' to the value 33.33.
Oracle will automatically convert numeric values to strings in the same way.
SQL> desc number_demo
Name Null? Type
------------------------------- -------- ----
RECORD_NO NOT NULL NUMBER(38)
VALUE NUMBER
TEXT VARCHAR2(30)
SQL> update number_demo
2 set text = 123.44;
1 row updated.
SQL> select * from number_demo;
RECORD_NO VALUE TEXT
---------- ---------- ------------------------------
101 1234567890 123.44
Strings and dates must be enclosed in single quotes to be converted to a string value. Otherwise, Oracle tries to interpret the text as a column name or expression, as shown in the next two examples:
SQL> update number_demo
2 set text = abc123;
set text = abc123
*
ERROR at line 2:
ORA-00904: invalid column name
SQL> update number_demo
2 set text = 123abc;
set text = 123abc
*
ERROR at line 2:
ORA-00933: SQL command not properly ended
To store character data, you can choose from several datatypes: CHAR
VARCHAR
VARCHAR2
LONG
You will find that the bulk of many databases is character data; the advantages and
disadvantages of each datatype are explained in the following paragraphs.
The CHAR datatype stores fixed-length character strings of up to 255 characters. If you do not specify a length, a CHAR column will store a single character.
You probably don't want to use the VARCHAR datatype at all. (Oracle warns that the VARCHAR datatype may not be supported in future releases.) The VARCHAR2 datatype was introduced with Oracle7 and is meant to replace VARCHAR. If you're building a new Oracle application, you won't have any reason to use the VARCHAR datatype.
VARCHAR2 will store up to 2,000 characters. If you absolutely must store more than that, you need to consider using the LONG datatype.
You can store slightly more than 2GB of characters in a LONG column. However, you cannot use any of Oracle's built-in functions or operators with LONG columns. The best way to think of a LONG column is as a very large black box. You cannot search the contents of this box unless the box is emptied.
Follow these guidelines for choosing an appropriate datatype for a column that will store character data:
If you're trying to store more than 2,000 characters, don't create multiple VARCHAR2 columns to store the data. Assembling, searching, or manipulating the contents of multiple columns is impractical. This example should help to explain the rationale.
Suppose you need a table that can store up to 6,000 characters of text. You also want to use Oracle's built-in functions and operators, so you decide not to use a LONG column. Here is the initial table:
SQL> create table text_demo ( 2 record_no int not null, 3 employee_id int not null, 4 text1 varchar2(2000), 5 text2 varchar2(2000), 6 text3 varchar2(2000)); Table created.
Assuming that the table contains text that you're interested in searching, you submit the following query:
SQL> select record_no, text1, text2, text3
2 from text_demo
3 where
4 text1 like '%there%' or
5 text2 like '%there%' or
6 text3 like '%there%';
RECORD_NO
---------
TEXT1
-------------------------------------------------------------------------------
TEXT2
-------------------------------------------------------------------------------
TEXT3
-------------------------------------------------------------------------------
1001
This is the most amazing story. Three pigs left their home to find their fortun
Once upon a time, in a cottage in the forest, there lived three bears - a papa
A long time ago, in a galaxy far, far away - lived three creatures named ...
If you had to search 50 columns instead of just 3, the query would become quite unwieldy. Instead of using a table in which each text line was stored as a separate column, you could create a separate table so that the two tables looked like the following:
SQL> create table text_master ( 2 record_no int not null, 3 employee_id int not null); Table created. SQL> create table text_detail ( 2 record_no int not null, 3 line_no int, 4 text_line varchar2(2000)); Table created.
Now you can reduce your query to
SQL> select line_no, text_line
2 from text_detail
3 where
4 record_no = 1001 and
5 text_line like '%the%';
LINE_NO
---------
TEXT_LINE
-------------------------------------------------------------------------------
1
This is the most amazing story. Three pigs left their home to find their fortun
2
Once upon a time, in a cottage in the forest, there lived three bears - a papa
The alternative to this approach is to use a LONG column and perform all manipulation and searching outside of SQL. For instance, if you're using PowerBuilder as a development tool, you would write an event script, in PowerScript, that stores the contents of the LONG column in a PowerBuilder binary large object (BLOB) variable. Then it could be manipulated using PowerScript functions and operators, and the search results could be presented to the user. Choosing between one LONG column or shorter VARCHAR2 columns can be difficult; you need to carefully evaluate the specific requirements of the application.
Because the CHAR datatype stores fixed-length columns, use it when you are defining columns that will contain a single character. Using the CHAR datatype to store larger strings isn't efficient because you will waste storage space.
Because it stores variable length strings, the VARCHAR2 datatype is the preferred datatype for storing strings. This datatype can store up to 2,000 characters. In Chapter 11 you'll analyze the many ways to use Oracle's built-in string functions and operators.
One of Oracle's strengths is its DATE datatype. The DATE datatype should really be named DATETIME because it provides storage for both date and time information. Oracle always allocates a fixed seven bytes for a DATE column, even if you're using a DATE column to store date information only or time information only.
Oracle has quite a few built-in functions specifically for manipulating DATE values and expressions. Chapter 12 presents many detailed examples of how to use these functions.
The DATE datatype enables you to store dates in the range of January 1, 4712 B.C., to December 31, 4712 A.D. Oracle uses the default format DD-MON-YY for entering and displaying dates.
SQL> create table date_demo ( 2 record_no int not null, 3 date_of_birth date); Table created. SQL> insert into date_demo 2 (record_no, date_of_birth) 3 values 4 (101, '09-MAY-57'); 1 row created.
However, if you use a different format for the date without telling Oracle what the format is, the following error occurs:
SQL> insert into date_demo
2 (record_no, date_of_birth)
3 values
4 (102, 'SEP 08, 1955');
(102, 'SEP 08, 1955')
*
ERROR at line 4:
ORA-01858: a non-numeric character was found where a numeric was expected
As was mentioned earlier, you must use Oracle's LONG datatype to store more than 2,000 characters in a single column. A LONG column can accommodate up to 2GB of characters. However, you will face a number of restrictions on the use of LONG columns in SQL. To investigate the use of LONG columns, use this simple table:
SQL> desc long_demo Name Null? Type ------------------------------- -------- ---- RECORD_NO NOT NULL NUMBER(38) COMMENTS LONG
Put a row in the Long_Demo table.
SQL> insert into long_demo 2 (record_no, comments) 3 values 4 (100, 5 'We will investigate the capabilities and restrictions 6 of a LONG column by entering data into this column');
1 row created.
If you select the contents of this table, SQL*Plus returns the following:
SQL> select *
2 from long_demo;
RECORD_NO COMMENTS
--------- ---------------------------------------------------------------------
100 We will investigate the capabilities and restrictions of a LONG colum
Notice that SQL*Plus has truncated the contents of the Comments column to 80 characters. SQL*Plus uses the system variable LONG to determine how many characters in a LONG column to display to the user. The default setting for this parameter is 80. Increase the LONG variable to 200 and perform the query again. Also, you'll need to increase the width of the SQL*Plus screen buffer so that the results can be displayed.
SQL> set long 200
SQL> select *
2 from long_demo;
RECORD_NO COMMENTS
--------- ---------------------------------------------------------------------
100 We will investigate the capabilities and restrictions of a LONG
column by entering data into this column
Now you see the complete contents of the Comments column.
You can't use Oracle functions or operators to search or modify the contents of a LONG column. If you try to search a LONG column, this is what will happen:
SQL> select record_no, comments 2 from long_demo 3 where 4 comments like '%LONG%'; comments like '%LONG%' * ERROR at line 4: ORA-00932: inconsistent data types
That's right; you can't search the contents of a LONG column. Here's what happens if you try to apply a function to a LONG column.
SQL> select record_no, substr(comments,1,5)
2 from long_demo;
select record_no, substr(comments,1,5)
*
ERROR at line 1:
ORA-00932: inconsistent data types
Again, Oracle won't enable you to apply a function to a LONG column. In a sense, you can think of a LONG column as a large container into which you can store or retrieve data--;but not manipulate it.
Remember: You can have only one LONG column per table.
As you're probably aware, most databases provide for the storage of BLOBs. BLOBs include documents, graphics, sound, video--;actually, any type of binary file you can think of. The Oracle LONG RAW datatype is designed for BLOB storage.
When you want to associate a BLOB with a "normal" row, you can do either of the following:
Some developers are more comfortable with the first method. They think that the BLOB is more readily available if it's stored in the file system, instead of in the Oracle database. They reason that they gain very little by storing a BLOB in a table column if the Oracle database can't (or shouldn't) manipulate the BLOB.
However, other developers see an advantage in centralizing all data storage in the Oracle database. They argue that this approach provides greater portability; removing references to a directory and filename leaves fewer OS-specific issues to deal with.
A column defined as LONG RAW can accommodate up to 2GB for each row. Obviously, a LONG RAW column can store extremely large BLOBs, such as documents, images, sound, and even video.
Like LONG columns, LONG RAW columns have a number of limitations. For example, you cannot use any of the built-in functions with a LONG RAW column.
Oracle also provides the RAW datatype, which can accommodate up to 255 bytes of binary data. Because of this storage restriction, a RAW column is less useful than a LONG RAW column. Nevertheless, I've created a sample table to demonstrate the use of a RAW column.
SQL> create table raw_demo 2 (record_no integer not null, 3 byte_stream raw(255)); Table created.
Now that the table exists, you can use the INSERT statement to put a record in it.
SQL> insert into raw_demo 2 (record_no, byte_stream) 3 values 4 (100, 'abcd'); 1 row created.
At first glance, a RAW column appears to accommodate strings and to convert them into an internal binary format. Now try inserting another row.
SQL> insert into raw_demo
2 (record_no, byte_stream)
3 values
4 (101, 'xyz');
(101, 'xyz')
*
ERROR at line 4:
ORA-01465: invalid hex number
As you see, you must use a valid hexadecimal number to insert a value into a RAW column. You also have the option of using a built-in function called HEXTORAW, which converts a hexadecimal number into a binary value.
Here's an example of how to select values from a RAW column:
SQL> select record_no, byte_stream
2 from raw_demo;
RECORD_NO
---------
BYTE_STREAM
-------------------------------------------------------------------------------
100
ABCD
102
012345ABCDEF
As you see, Oracle displays the contents of a RAW column as hexadecimal digits.
Keep the following concepts in mind when you work with Oracle datatypes: