Re: BLOB versus VARCHAR

"Sharpster" <sabsharpe_at_gmail.com> wrote:
> We are about to port a database from InterBase 7.5 to ORACLE 10g and
> have a number of InterBase BLOB fields to store text comments that
> could range from length zero to whatever. Most comments are probably
> less than 300 characters but we did it this way to make use of the BLOB
> type and therefore never have to worry about limits.

What is the maximum actual length of a comment in the current database? If the system has been used for years and no comment has approached the VARCHAR limit, then that would suggest you can safely down-size your specification. On the other hand, if they had sufficient reason to choose the unlimited version in the first place....

> In ORACLE would it be best to
>
> a) continue using the BLOB field type

probably CLOB, not BLOB

> b) define a VARCHAR(2000) (or similar limit)

In Oracle, you should use varchar2 rather than varchar.

> c) other..

If you are going to do the switch, why switch to something lower than the maximum possible limit? If you can't think of a reason, then make it a varchar2(4000)

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB