How I can create a table having two long columns?

Question

I am using Oracle 10 g.

I have a table which needs two columns of the Long type:

CREATE TABLE emp
 (
    id1 LONG NULL,
    id2 LONG NULL,
    //
    // Other columns.
);

When I am executing this query, it is giving me this error:

Error report:
SQL Error: ORA-01754: a table may contain only one column of type LONG
01754. 00000 -  "a table may contain only one column of type LONG"
*Cause:    An attempt was made to add a LONG column to a table which already
           had a LONG column. Note that even if the LONG column currently
           in the table has already been marked unused, another LONG column
           may not be added until the unused columns are dropped.
*Action:   Remove the LONG column currently in the table by using the ALTER
           TABLE command.

I searched on Google, but I could not find a proper solution. Why do they not allow two columns of LONG?

Is using number instead of a column a good idea?

How can I achieve this?

Answer 1

LONG columns have not been recommended for a long time; since 8i, I believe. From the 11g documentation:

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

You were only ever allowed one LONG column in a table. I'm not entirely sure, but I think that was because the LONG data was stored alongside the other columns, and more than one would make it even more unmanageable. LOBs are stored differently; see the table here for a comparison.

---

Oracle's LONG datatype is 'Character data of variable length up to 2 gigabytes'. If you're storing numeric data, use NUMBER.

If you're mapping Java data types (guessing from your profile!), this table may be of use.

Answer 2

Since Oracle 8i the LONG datatype is advised not to be used and instead use CLOB or NLOB datatypes.

Just in case check Oracle 10g documentation about datatypes. Also check here for other details