I have to produce a table in Oracle which will store a number of large text files. After searching in the Oracle datatype's it's unclear what kind I ought to use to keep the files.
The limits around the text specific types like VARCHAR2 appear really small (32K). Another types don't appear to become the best match for any text file (e.g. BFILE).
Does anybody come with an opinion around the right type to make use of?
The main difference between CLOB and BLOB/BFILE is the fact that CLOBs are treated as Text. That's, should you pull a CLOB in the database it is going to do any conversion necessary in the database character set towards the client character set (eg getting rid of a highlight from an ê). Similarly, whenever a CLOB is produced with a client, there might be a conversion in the client character set towards the database character set. If both client and database character sets are identical, then no conversion is required or carried out.
NCLOB is much like CLOB with the exception that as opposed to the database character set, the conversion uses the NLS NCHAR characterset.
A BLOB/BFILE won't be susceptible to the conversion rules.
So GENERALLY I'd make use of a CLOB for text, but when there's some checksum/audit trail logic where I'm not going the smallest chance of a personality set conversion, I may opt to use a BLOB/BFILE. I would not think about a Lengthy or Lengthy RAW.
Is dependent on which version of Oracle you're using Either CLOB or Lengthy
The LOB datatypes for character data are CLOB and NCLOB. They are able to store as much as 8 terabytes of character data (CLOB) or national character set data (NCLOB).
Later Oracle suggests that you simply convert existing Lengthy RAW posts to LOB posts. LOB posts are susceptible to far less limitations than Lengthy posts. Further, LOB functionality is enhanced in each and every release, whereas Lengthy RAW functionality continues to be static for many releases.