For each column that you define for a table, you must specify the data type of that column. Here are your options:
- Character Data
char(n) A fixed-length character string, e.g., char(200) will take up 200 bytes regardless of how long the string actually is. This works well when the data truly are of fixed size, e.g., when you are recording a user's sex as "m" or "f". This works badly when the data are of variable length. Not only does it waste space on the disk and in the memory cache, but it makes comparisons fail. For example, suppose you insert "rating" into a comment_type column of type char(30) and then your Tcl program queries the database. Oracle sends this column value back to procedural language clients padded with enough spaces to make up 30 total characters. Thus if you have a comparison within Tcl of whether $comment_type == "rating", the comparison will fail because $comment_type is actually "rating" followed by 24 spaces.
The maximum length char in Oracle8
is 2000 bytes.
varchar(n) A variable-length character string, up to 4000 bytes long in Oracle8. These are stored in such a way as to minimize disk space usage, i.e., if you only put one character into a column of type varchar(4000), Oracle only consumes two bytes on disk. The reason that you don't just make all the columns varchar(4000) is that the Oracle indexing system is limited to indexing keys of about 700 bytes.
clob A variable-length character string, up to 4 gigabytes long in Oracle8. The CLOB data type is useful for accepting user input from such applications as discussion forums. Sadly, Oracle8 has tremendous limitations on how CLOB data may be inserted, modified, and queried. Use varchar(4000) if you can and prepare to suffer if you can't.
In a spectacular demonstration of what happens when companies don't follow the lessons of The Mythical Man Month, the regular string functions don't work on CLOBs. You need to call identically named functions in the DBMS_LOB package. These functions take the same arguments but in different orders. You'll never be able to write a working line of code without first reading the DBMS_LOB section of the Oracle8 Server Application Developer's Guide.
nchar, nvarchar, nclob The n prefix stands for "national character set". These work like char, varchar, and clob but for multi-byte characters (e.g., Unicode; see http://www.unicode.org/).
- Numeric Data
number Oracle actually only has one internal data type that
is used for storing numbers. It can handle 38 digits of precision and exponents
from -130 to +126. If you want to get fancy, you can specify precision and
scale limits. For example, number(3,0) says "round everything to an
integer [scale 0] and accept numbers than range from -999 to +999". If
you're American and commercially minded, number(9,2) will probably work well
for storing prices in dollars and cents (unless you're selling stuff to Bill
Gates, in which case the billion dollar limit imposed by the precision of 9
might prove constraining). If you are Bill Gates, you might not want to get
distracted by insignificant numbers: Tell Oracle to round everything to the
nearest million with number(38,-6).
integer In terms of storage consumed and behavior, this is not any different from number(38) but I think it reads better and it is more in line with ANSI SQL (which would be a standard if anyone actually implemented it).
- Dates and Date/Time Intervals
date A point in time, recorded with one-second precision,
between January 1, 4712 BC and December 31, 4712 AD. You can put in values with
the to_date function and query them out using the to_char function. If you
don't use these functions, you're limited to specifying the date with the
default system format mask, usually 'DD-MON-YY'. This is a good recipe for a
Year 2000 bug since January 23, 2000 would be '23-JAN-00'. On
ArsDigita-maintained systems, we reset Oracle's default to the ANSI default:
'YYYY-MM-DD', e.g., '2000-01-23' for January 23, 2000.
number Hey, isn't this a typo? What's number doing in the date section? It is here because this is how Oracle represents date-time intervals, though their docs never say this explicitly. If you add numbers to dates, you get new dates. For example, tomorrow at exactly this time is sysdate+1. To query for stuff submitted in the last hour, you limit to submitted_date > sysdate - 1/24.
- Binary Data
blob BLOB stands for "Binary Large OBject". It
doesn't really have to be all that large, though Oracle will let you store up
to 4 GB. The BLOB data type was set up to permit the storage of images,
sound recordings, and other inherently binary data. In practice, it also gets
used by fraudulent application software vendors. They spend a few years
kludging together some nasty format of their own. Their MBA executive customers
demand that the whole thing be RDBMS-based. The software vendor learns enough
about Oracle to "stuff everything into a BLOB". Then all the
marketing and sales folks are happy because the application is now running from
Oracle instead of from the file system. Sadly, the programmers and users don't
get much because you can't use SQL very effectively to query or update what's
inside a BLOB.
bfile A binary file, stored by the operating system (typically Unix) and kept track of by Oracle. These would be useful when you need to get to information both from SQL (which is kept purposefully ignorant about what goes on in the wider world) and from an application that can only read from standard files (e.g., a typical Web server). The bfile data type is pretty new but to my mind it is already obsolete: Oracle 8.1 (8i) lets external applications view content in the database as though it were a file on a Windows NT server. So why not keep everything as a BLOB and enable Oracle's Internet File System?
Note: In Oracle-SQL there is no data type boolean. It can, however, be simulated by using either char(1) or number(1).