How to store an array of bytes in Oracle?

问题:

I want to store a short array of 64 bytes in Oracle database (a password hash). I thought char(64 byte) is what I need, but it seems like it doesn't work. In Microsoft SQL, I use binary and varbinary types. What type do I need to use in Oracle?

Every example I've found uses blob to store binary data, but I suppose blob is intended only for large objects, not for fixed size short arrays.

When updating the data, is the code like this appropriate:

byte[] passwordHash = GenerateHash();

using (OracleCommand updateHash = new OracleCommand("update A set passwordHash = :hash where EntryId = :id", oracleConnection))
{
    updateHash.Parameters.Add(":hash", passwordHash);
    updateHash.Parameters.Add(":id", entryId);

    if (updateHash.ExecuteNonQuery() != 1)
    {
        // ...
    }
}

or am I missing something and byte array parameters cannot be added like this?

回答1:

In addition to blob, Oracle also has the RAW datatype,

RAW is a variable-length datatype like VARCHAR2

-- Oracle 10g Release 2 Datatypes

RAW can be set up to a max size of 2000 bytes, with LONG RAW having a max size of 2GB.

However:

Oracle strongly recommends that you convert LONG RAW columns to binary LOB (BLOB) columns. LOB columns are subject to far fewer restrictions than LONG columns.

回答2:

In Oracle, The RAW datatype is appropriate for storing binary values.

The issue with using character datatypes for storing binary data is that the values are subject to character set translation.

If the client character set does not match the database character set, then values are subject to translation. (That is, a binary value in one encoding represents a particular character, but that character can be represented by a different binary value in another characterset.

With the character datatype, Oracle preserves the "character" value, not the encoded binary value.

If you want to use a character (e.g. CHAR or VARCHAR2) datatype to store binary values, then you really need to encode the binary value as plain text, and store and retrieve the encoded value. Two popular binary-to-text encodings are hexadecimal and base64 (uuencode).

Oracle provides builtin functions RAWTOHEX and HEXTORAW for encoding and decoding binary (RAW datatype) as hexadecimal strings (VARCHAR2 datatype).

回答3:

The best solution is to use the RAW datatype, which is intended to store binary data. However RAW data is inserted and retrieved as hexadecimal values, so some conversion may be required.

You should be able to use CHAR or VARCHAR2 as well, but those datatypes will not constrain the field to just binary data. If you tell us what error you were receiving when you tried to insert into the CHAR column, we may be able to help you work that out...

Keep in mind, if you use the CHAR datatype, that CHAR values are always padded to the length of the field, so you may need to trim smaller values before using them in some cases.