Re: Oracle data type

Uthuras wrote:
> Greetings,
>
> Is there any known case of oracle allowing only one column of LONG
> data type in a table? The orcale 8 and 9 does not allow us to create
> more than one LONG data type in a particular table.
>
> Why is it so ? Is there any limitation to it ?
>
> Please advice.
>
> Thanks
>
> Uthuras

It's been that way for practically ever. It is one of the limitations of the LONG and LONG RAW data types that you can only have one column of either in a table... and, because the data is stored in-line with the rest of the row data, if you've got any sense you make it the last column of the table definition.

This is precisely one reason why Oracle very, very strongly recommends that you do NOT use LONG or LONG RAW data types any more. Since version 8.0, there have been CLOB and BLOB data types to replace them (and NCLOBs if you are using national character data).

With CLOBs, you can have as many of the things in a table as you desire, they store double the amount of data that a LONG can manage (even more in 10g), and the data over 4000 bytes is stored out of line, so the column order doesn't matter. Even under 4000 bytes you can choose to store out of line if you wish, though it's not the default.

There are all sorts of other restrictions with LONG that largely disappear with CLOBs, too (such as partitioning issues). There are also performance issues (CLOBs can be indexed, LONGs can't be; CLOBs can therefore have randomised access, LONGs are read serially etc etc etc).

In short, you shouldn't be developing new applications with LONGs in them any more.

Regards
HJR Received on Tue May 04 2004 - 23:59:38 CDT