I'm new to Oracle and evaluating some application options for a text-based database. I don't have Oracle yet, but I'm looking for additional info. on Oracle's text-oriented data types...

Some questions --

1) With MS Access, text fields were limited to 255 characters, afterwhich a memo field had to be used (but with some drawbacks for sorting and manipulating, indexing, etc.).

Oracle's VARCHAR2 or NVARCHAR2 field types take much more text (up to 4K characters?), but how are CLOBs & BLOBs used as data types (examples please). I assume BLOBs would be file attachments, but CLOBS? What limitations are there to using CLOBS/BLOBs?

2) What about ODBC connectivity and large text data-typed fields? My experience has been that ODBC tends to "break" when connecting to databases with large text fields. Any comments please on how ORACLE's ODBC connectivity works with BLOBs & CLOBS or other large text-based data. Does it even make sense for ODBC to work with CLOBS & BLOBs or large text-based data? [Most text-based database vendors shy away from ODBC connectivity -- i presume -- due to the difficulties of programming drivers for unstructed text..?]

For that matter Oracle's how are Oracle's "export features" when dealing with CLOBS and BLOB data?

I'm impressed that Oracle bought out a text search company for integration with Oracle 8 (now called "Oracle text" in 9i), but I want to be better informed on just how far Oracle has "bridged the gap" between the RDBMS world's data management capabilities and the general lack of text-based capabilities found in most RDBMSs (searching, manipulating, indexing)....

some of my notes on text-data types...

VARCHAR2(size) - Variable-length character string; max of 4000 bytes (or ~4K characters)

NVARCHAR2(size) - Variable-length character string; max of 4000 bytes (or 4K characters)

LONG - Character data of variable length up to 2 gigabytes, but only one per database (?)

LONG RAW - Raw binary data of variable length up to 2 gigabytes

CHAR(size) Fixed length character data ; maximum size is 2000 bytes.

NCHAR(size) Fixed-length character data; maximum size is 2000 bytes.

CLOBs - character large object

BLOBs - A binary large object