Ixora Answers - Storing null bytes in the database

From:	Steve Adams
Date:	08-Nov-2001 14:37
Subject:	Storing null bytes in the database

There is no problem with storing null bytes in a VARCHAR2 column as the following example demonstrates. It is just highly unusual.

    SQL> create table test (string varchar2(12));

    Table created.

    SQL> insert into test values ('Hello there');

    1 row created.

    SQL> insert into test values ('Hello'||chr(0)||'there');

    1 row created.

    SQL> select string, length(string), dump(string) from test;

    STRING       LENGTH(STRING) DUMP(STRING)
    ------------ -------------- ------------------------------------------------------------
    Hello there              11 Typ=1 Len=11: 72,101,108,108,111,32,116,104,101,114,101
    Hello there              11 Typ=1 Len=11: 72,101,108,108,111,0,116,104,101,114,101

    SQL>

You are losing the bytes after the null because of the null-termination semantics of the OCIString datatype. However, I doubt that you will get away with binding an OCIRaw variable where an OCIString variable is expected. So RAW may be the only way to go.

I am developing an application which needs to store \0 in the database. am using OCI. I used VARCHAR2 but the characters after the \0 could not be stored in the database. Should I change VARCHAR2 to RAW?