what is the difference between long and long raw data types in Oracle?

Question

My understanding is Long data type can store the actual string(chars), while Long raw data type stores the binary values of the string(chars). Is it right? Can a table have only one long type column?

Answer 1

The data types are described in the documentation; LONG is explained here (or the 11gR2 version):

LONG columns store variable-length character strings containing up to 2 gigabytes -1, or 231-1 bytes. LONG columns have many of the characteristics of VARCHAR2 columns. You can use LONG columns to store long text strings.

And LONG RAW is here:

The RAW and LONG RAW datatypes store data that is not to be interpreted (that is, not explicitly converted when moving data between different systems) by Oracle Database. These datatypes are intended for binary data or byte strings. For example, you can use LONG RAW to store graphics, sound, documents, or arrays of binary data, for which the interpretation is dependent on the use.

So a RAW or LONG RAW can contain the binary representation of characters, but won't be subject to character set conversion etc. so probably isn't all that useful for that; an can contain any other binary data - anything that isn't supposed to represent text.

From the same LONG section:

A table can contain only one LONG column.

However, LONG is deprecated in favour of LOB (CLOB or NCLOB for text, BLOB for everything else), so you shouldn't be using them for new work, and should at least be considering replacing any you already have. Again from that same section on LONG:

Do not create tables with LONG columns. Use LOB columns (CLOB, NCLOB, BLOB) instead. LONG columns are supported only for backward compatibility.

Oracle also recommends that you convert existing LONG columns to LOB columns.

This documentation on migrating from LONG to LOB might be of interest.