Friday, May 03, 2013

Raw Columns

This was tested on Oracle 11.2. You can use a raw column to store binary data:

SQL> create table my_table (raw_column raw(20))
  2  /

Table created.


If you try to insert character data into it directly, you get an ORA-01465:

SQL> insert into my_table values ('Andrew Reid')
  2  /
insert into my_table values ('Andrew Reid')
ERROR at line 1:
ORA-01465: invalid hex number


You have to convert it to binary first as follows:

SQL> insert into my_table values
  2  (utl_raw.cast_to_raw('Andrew Reid'))
  3  /

1 row created.


Conversely, if you try to read the data, it is displayed in hexadecimal format:

SQL> select * from my_table
  2  /



… but you can read it like this:

SQL> select utl_raw.cast_to_varchar2(raw_column)
  2  from my_table
  3  /

Andrew Reid


If you have an Oracle book on Amazon, which you would like to advertise here for free, please write to me at


Anonymous said...

Do you have any suggestion on how this could be useful?

Andrew Reid said...

Not at this stage, this whole blog is very much a learning curve for me. As soon as I find out why you might use a RAW column, you will be the first to hear about it!