Monday, November 21, 2005

RAW Datatype

Earlier I mentioned the idea of using a RAW datatype for your table's primary key. I stumbled upon a minor inconvenience with using the RAW datatype today, so I thought I would take a step back and talk a little bit more about the RAW datatype.

RAW(size) is used for raw binary data or byte strings of length "size" bytes (must be specified). The maximum is 2000 bytes, but in PL/SQL it is 32767, which can lead to interesting situations.

Think of RAW very much like VARCHAR2, but unlike VARCHAR, RAW data will not be converted depending on the character set (by import/export, for example). More generally, Oracle will treat this as binary data and will not interpret it as a number, character, or anything else (generally done when moving data from one system/session to another).

RAW data can still be viewed as characters (2 characters per byte). Something like SQL*Plus does this for you automatically when you query. But RAW data is a little harder to use in pure SQL, because you often have to use HEXTORAW or RAWTOHEX to do your work (example shortly). However in PL/SQL you can just use the UTL_RAW package.

Now back to the story.

In the aforementioned article I mentioned using it as a primary key, using SYS_GUID. The advantages are you get a (generally) unique and randomly-dispersed key that you should never have to change. Please note: I do not want to get dragged into a debate on whether or not it is a good idea, I'm just saying its possible, and has some advantages. And yes, RAW data can be indexed just fine.

Now let me give you an example of the type of minor annoyance you have to deal with when using RAW datatypes.

Associative arrays, also referred to as:
1. Hash arrays: in other languages, like Perl, that is what they are called
2. "Index-by tables": because they are declared just like nested tables except with "index by" added to it.
Can not use RAW datatypes as their keys.

For this example I will use "NUMBER" instead of the %ROWTYPE you would have if using RAW(16) as your primary key.

test_ass ASS_TYPE;
test_ass(SYS_GUID()) := 1;
test_ass(SYS_GUID()) := 2;
test_ass(SYS_GUID()) := 3;

PLS-00315: Implementation restriction: unsupported table index type

So you have to convert it to VARCHAR2 in order to use it, using RAWTOHEX. You can use HEXTORAW to turn it back. Here is a working example:

test_ass ASS_TYPE;
test_ass(RAWTOHEX(SYS_GUID())) := 1;
test_ass(RAWTOHEX(SYS_GUID())) := 2;
test_ass(RAWTOHEX(SYS_GUID())) := 3;


Related Info:

There is some additional information the Oracle SQL Reference and Oracle Application Developer's Guide - Fundamentals.

Other binary data types, depending on your version, include BLOB, Bfile, LONG RAW, and others.

Here is a link to an AskTom discussion on RAW

This comment has been removed by a blog administrator.
Great, concise post.
If I use a guid surrogate pk I save table space by using raw(16) but lose it by indexing on rawtohex(16), right?
Great,info on RAW datatype,
How about it using it in DBI(Perl) for binding raw values?

Is there any support for RAW in DBI?
If i am right SQL_BINARY or SQL_VARBINARY are bound as RAW in Oracle when using DBD::Oracle(Oracle Driver).

I read using RAW datatype as PKs is useful in making scalable,distributed applications .Generation of PKs is done in middleware. How far is this true?
This comment has been removed by a blog administrator.
Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?