All databases stand on a tripod of datatypes: strings, numbers and dates. And so I’d imagine that by Day 2 or so of dabbling with Oracle you’d be starting to get reasonably comfortable with the VARCHAR2, NUMBER and TIMESTAMP data types. And that’s a good thing, familiarity with those three data types will take you a long way into a career of working with the Oracle database.

However, there are a legion of ‘second string’ data types that you’ll probably want to be passingly familiar with too. It’s kinda like when you meet someone, and fall in love. Sure you want to know every single thing about the gorgeous person you’re in love with – but to really know them, don’t you need to know a bit about the family they come from too?

So here are some of the other Oracle data types – here are some of their quirks, and here are the instances when you might want to use them (I’ll only be talking about character and number data types, along with a little bit on large objects; I’ve written a whole separate article about the various date data types).

Character Data Types

CHAR

The CHAR data type is rather similar to its more popular cousin, VARCHAR2. The one difference between them is that while VARCHAR2 is a variable chararacter data type, CHAR is always a fixed length. And when the string falls short of the set fixed length, CHAR pads it out with blank spaces.  I’ll show you what I mean; let’s knock up a table with a CHAR and a VARCHAR2 column.

If we insert a string of less than 10 characters into both columns and query the table we’ll notice no immediate difference.

However, if we ask Oracle the length of each string, we’ll notice something curious.

The reason for this discrepancy becomes obvious if we concatenate a character, let’s use an x, to the start and end of each field.

CHAR columns, as I said earlier, are right-padded with blank spaces. This may come in handy one day in your career (I’ve occasionally needed to generate fixed-length files and have used CHAR in those instances), however, many experts would urge you to avoid CHAR. Because of its inflexible length it can gobble up disk space (compared to VARCHAR2) and there’s nothing it can do that rpad (varchar2) can’t do. 

CHAR, however, isn’t the most reviled character data type. That dubious honour is reserved for…

LONG

The very first words in the Oracle documentation on the LONG data type are “Do not create tables with LONG columns.” This is because the data type has been deprecated and is only included in each subsequent database version to ensure backward compatibility. However, I must have done something really bad in a past life, cos I’ve often worked with databases where other developers have decided, in their ‘wisdom’, to use LONG columns. 

So what’s so bad about LONGs, you ask. 

For starters you can only have one LONG column in a table. And if that’s not bad enough, you then can’t use that LONG column in your where clause; it just isn’t allowed. Neither is it allowed in your group by clause, or order by, or in a distinct. And that’s not all – stored functions cannot return a LONG.

So never use LONGs. And if you ever come across a LONG column in a database you’re working with, take a minute to curse the fool who used it, and then be aware of its limitations.

For the sake of completeness, I probably should tell you what the features of LONGs are: LONGs hold variable-length character data, very much like VARCHAR2. However, compared to VARCHAR2’s paltry 32767 character limit, LONG can hold up to 2 gigabytes of information.

Do not create tables with LONG columns, the Oracle documentation reads; use LOB columns (CLOB, NCLOB) instead.

CLOB

Character Large Objects – CLOBs to their friends – can hold up to 4gb of data. That’s a crazy amount of data – around 260,000 MS Word pages; it makes the 32767 characters VARCHAR2 can hold look as tiny as a two year old girl crying in the rain. 

I don’t know about you, but I don’t actually have many 260,000-page Word documents that I need to save, so my advice is that unless you know that your text will breach the 32767 character limit you should stick with VARCHAR2.  CLOBs do not suffer from the stupid restrictions that blight LONGs, but they still incur slight performance costs compared to VARCHAR2. 

Beyond that, you can actually use a number of the SQL constructions that you use with VARCHAR2 with CLOBs. I’ll show you; let’s start off by creating a table with 2 CLOB columns (which is something we wouldn’t have been able to do with LONGs).

Now let’s try a few of the everyday operations that we might use with a VARCHAR2 column.

That runs happily, and it seems that there’s no difference between CLOBs and VARCHAR2s. But if you get complacent and try the following seemingly-innocent query:

You’ll be hit with an ORA-00932 – inconsistent datatypes – error. And that’s because CLOBs aren’t just super-sized VARCHAR2s and there are some things you can’t do easily with them, and that list includes comparisons. 

To get around this limitation Oracle has given us the dbms_lob package. It contains a number of subprograms that make manipulating LOBs – CLOBs as well as Binary Large Objects – a breeze. One of the functions, for example, allows us rewrite our erroring query.

This is because dbms_lob.compare returns 0 where the CLOBs are exact matches. It returns -1 if the first parameter is less than the second, and 1 if the second is less than the first.

Other functions that live in dbms_lob include getlength, append, substr and instr. What they do is probably obvious from their names; you don’t need me to spoonfeed you. However, here’s a complete list of the dbms_lob subprograms, and here’s a table that tells you which CLOB operations you can carry out in SQL and which ones need PL/SQL.

Number Data Types

Other databases might have their exotic numeric data types – tinyint, smallint, bigint and whatever – but Oracle is able to get pretty much everything done with just the NUMBER data type.  It stores both fixed and floating point numbers. And its got such range that it can hold practically any number that you’ll ever need to record.

So that’s it, right, there’s nothing else you need to know, right? Don’t be too hasty, let me introduce you to…

PLS_INTEGER

OK, I’m cheating. PLS_INTEGER is not a SQL data type; it’s  PL/SQL data type and can only be used in PL/SQL. Try using it in plain old SQL and you’ll get an ORA-00902: invalid datatype error. You cannot use PL/SQL data types in SQL – for table columns or whatever. It’s like trying to speak Chinese to an Eskimo.

However, the reason I’m mentioning PLS_INTEGER is that when you are writing PL/SQL, it is preferable to NUMBER in a few instances.  It, as its name suggests, is a data type for integers; and it is faster than NUMBER in calculations. It also requires less storage. 

If we’re being honest, modern processors are pretty fast and modern storage is pretty cheap, so you mightn’t think that getting a teensy bit faster or that saving a molecule of storage is worth any hassle. Fair point. But when you’re writing PL/SQL and working with integers using PLS_INTEGER rather than NUMBER is a good habit to get into.

Large Object Data Types

We’ve already met one member of the LOB family; it’s time to introduce you to the rest of the gang. Databases, I’ve said, stand on a tripod of data types – strings, numbers and dates. However, not all data falls neatly into one of those categories; we’ve got music and video files, for instance, and photographs, and stuff like that. We obviously need some other way to hold that data. They allow you to store large blocks of unstructured data in binary form (or in character form, in the case of CLOBs).

Ah, speaking of binary data…

BLOB

We’ve already talked about how you can manipulate BLOBs using the dbms_lob package. That’s important to remember, since, because it isn’t comprised of character data, you cannot treat it like a VARCHAR2 the way you can with a CLOB. 

How about an example? Let’s say you wanted to load a photo of your pet rabbit, Mr Tickles, to the database. We might start by creating a table.

Now we’ve got our table, we can upload Mr Tickles’ photo to it using a dbms_lob subprogram. However, before we do that, let me speak to you about…

BFILE

The BFILE data type is a large object data type that allows you store binary data in an operating system file outside the database. There are a few commonsense rules that go with BFILEs: the file must exist, and Oracle must have the right operating system privileges to read it. Also, BFILEs only provide read-only access to files.

You can create table columns with the BFILE data type, and you can connect it to files using dbms_lob and Oracle directories (which is a topic outside the scope of this article). You can also use a BFILE as a LOB locator to point at the image of Mr Tickles that we want to upload into our BLOB column…

OK, that’s a lot of code, and I know that my inline comments aren’t the most in-depth explanation possible, but hopefully they’re enough to demonstrate how BLOBs and BFILEs work.

Indeed the intention of this whole article has been to expose you to some of the data types that while they might live in the shadows of VARCHAR2, NUMBER and TIMESTAMP are important to know. There’s a good chance that you won’t need them every day, but when the day comes that you do need them, you’ll be glad that you stuck with me to the end of this article.