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.

SQL>

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

SQL>

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.

SQL>

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

SQL> select * from my_table
  2  /

RAW_COLUMN
----------------------------------------
416E647265772052656964

SQL>

… but you can read it like this:

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

UTL_RAW.CAST_TO_VARCHAR2(RAW_COLUMN)
-------------------------------------------------------
Andrew Reid

SQL>

2 comments:

  1. Anonymous7:54 pm

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

    ReplyDelete
  2. 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!

    ReplyDelete