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.<\/p>\n
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\u00a0really<\/em> know them, don’t you need to know a bit about the family they come from too?<\/p>\n 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<\/a>).<\/p>\n The CHAR data type is rather similar to its more popular cousin, VARCHAR2. The one difference between them is that while VARCHAR2 is a\u00a0var<\/em>iable\u00a0char<\/em>aracter 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. \u00a0I’ll show you what I mean; let’s knock up a table with a CHAR and a VARCHAR2 column.<\/p>\n If we insert a string of less than 10 characters into both columns and query the table we’ll notice no immediate difference.<\/p>\n However, if we ask Oracle the\u00a0length<\/em> of each string, we’ll notice something curious.<\/p>\n 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.<\/p>\n 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<\/a>\u00a0(varchar2)<\/em> can’t do.\u00a0<\/p>\n CHAR, however, isn’t the most reviled character data type. That dubious honour is reserved for…<\/p>\n The very first words in the Oracle documentation<\/a> 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.\u00a0<\/p>\n So what’s so bad about LONGs, you ask.\u00a0<\/p>\n 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\u00a0clause; 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n Do not create tables with LONG columns<\/em>, the Oracle documentation reads; use LOB columns (CLOB, NCLOB) instead<\/em>.<\/p>\n C<\/strong>haracter L<\/strong>arge Ob<\/strong>jects – 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.\u00a0<\/p>\n 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. \u00a0CLOBs do not suffer from the stupid restrictions that blight LONGs, but they still incur slight performance costs compared to VARCHAR2.\u00a0<\/p>\n 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).<\/p>\n Now let’s try a few of the everyday operations that we might use with a VARCHAR2 column.<\/p>\n 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:<\/p>\n You’ll be hit with an ORA-00932 – inconsistent datatypes – error. And that’s because CLOBs\u00a0aren’t<\/em> just super-sized VARCHAR2s and there are some things you can’t do easily with them, and that list includes comparisons.\u00a0<\/p>\n 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 B<\/strong>inary L<\/strong>arge Ob<\/strong>jects – a breeze. One of the functions, for example, allows us rewrite our erroring query.<\/p>\n 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.<\/p>\n 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<\/a>, and here’s a table that tells you which CLOB operations you can carry out in SQL and which ones need PL\/SQL<\/a>.<\/p>\n Other databases might have their exotic numeric data types – tinyint, smallint, bigint<\/em> and whatever – but Oracle is able to get pretty much everything done with just the So that’s it, right, there’s nothing else you need to know, right? Don’t be too hasty, let me introduce you to…<\/p>\n OK, I’m cheating. PLS_INTEGER is\u00a0not<\/span><\/em><\/strong> a SQL data type; it’s \u00a0PL\/SQL data type and can only be used in PL\/SQL. Try using it in plain old SQL and you’ll get an\u00a0 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. \u00a0It, as its name suggests, is a data type for integers; and it is faster than NUMBER in calculations. It also requires less storage.\u00a0<\/p>\n 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.<\/p>\n 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).<\/p>\n Ah, speaking of binary data…<\/p>\n 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.\u00a0<\/p>\n 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.<\/p>\n 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…<\/p>\n The BFILE data type is a large object data type that allows you store binary data in an operating system file\u00a0outside the database.<\/em> 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.<\/p>\nCharacter Data Types<\/h5>\n
CHAR<\/h6>\n
CREATE TABLE test_table\r\n(varchar2_col \u00a0VARCHAR2(10),\r\nchar_col CHAR(10)\r\n);<\/pre>\n
INSERT INTO test_table (varchar2_col, char_col)\r\nVALUES ('sausage','sausage');<\/pre>\nSELECT varchar2_col, char_col\r\nFROM test_table;\r\n\r\nvarchar2_col char_col\r\n------------ ----------\r\nsausage sausage<\/pre>\n
SELECT length(varchar2_col) varchar2_col, length(char_col) char_col\r\nFROM test_table;\r\n\r\nvarchar2_col char_col\r\n------------ ----------\r\n7 10\r\n<\/pre>\n
SELECT 'x'||varchar2_col||'x' varchar2_col, 'x'||char_col||'x' char_col\r\nFROM test_table;\r\n\r\n\r\nvarchar2_col char_col\r\n------------ ----------\r\nxsausagex xsausage x<\/pre>\n
LONG<\/h6>\n
CLOB<\/h6>\n
CREATE TABLE clob_test(\r\n clob_col1 \u00a0CLOB,\r\n clob_col2 CLOB\r\n);<\/pre>\n
INSERT INTO clob_test (clob_col1, clob_col2)\r\nVALUES ('sausages','eggs');<\/pre>\nSELECT clob_col1||clob_col2 \"Concat\", \r\n UPPER(clob_col1) \"Upper\", \r\n LENGTH(clob_col1) \"Length\", \r\n NVL(clob_col1,clob_col2) \"NVL\", \r\n SUBSTR(clob_col2,1,3) \"Substr\"\r\nFROM clob_test;\r\n\r\nConcat Upper Length NVL Substr\r\n----------- ------- ------ -------- ------ \r\nSausageseggs SAUSAGES 8 Sausages egg\r\n<\/pre>\n
SELECT clob_col1\r\nFROM clob_test\r\nWHERE clob_col1 = clob_col2;<\/pre>\n
SELECT clob_col1\r\nFROM clob_test\r\nWHERE dbms_lob.compare(clob_col1,clob_col2) = 0;<\/pre>\n
Number Data Types<\/h5>\n
NUMBER<\/code> data type. \u00a0It stores both fixed\u00a0and<\/em> floating point numbers. And its got such range that it can hold practically any number that you’ll ever need to record.<\/p>\nPLS_INTEGER<\/h6>\n
ORA-00902: invalid datatype<\/code> 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.<\/p>\nLarge Object Data Types<\/h5>\n
BLOB<\/h6>\n
CREATE TABLE blob_test (\r\n id \u00a0 NUMBER,\r\n text VARCHAR2(100),\r\n blob_col BLOB\r\n);<\/pre>\n
BFILE<\/h6>\n