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>