Menu
I need to create a table in Oracle that will store a series of large text files. After looking at the Oracle datatype's it is unclear what type I should use to store the files.
The limits on the text specific types like VARCHAR2 seem very small (32K). The other types don't seem to be the right match for a text file (e.g. BFILE).
Does anyone have an opinion on the right type to use?
941
The four TEXT types are TINYTEXT , TEXT , MEDIUMTEXT , and LONGTEXT . These correspond to the four BLOB types and have the same maximum lengths and storage requirements. See Section 11.7, “Data Type Storage Requirements”.
Execute code C:/My Code/code1. txt; oracle.
The TEXT data type stores any kind of text data. It can contain both single-byte and multibyte characters that the locale supports. The term simple large object refers to an instance of a TEXT or BYTE data type.
The difference between CLOB and BLOB/BFILE is that CLOBs are treated as Text. That is, if you pull a CLOB from the database it will do any conversion necessary from the database character set to the client character set (eg removing an accent from an ê). Similarly, when a CLOB is created by a client, there can be a conversion from the client character set to the database character set. If both client and database character sets are the same, then no conversion is needed or performed.
NCLOB is like CLOB except that rather than the database character set, the conversion uses the NLS NCHAR characterset.
A BLOB/BFILE will not be subject to the conversion rules.
So GENERALLY I would use a CLOB for text, but if there is some checksum/audit trail logic where I don't want even the slightest possibility of a character set conversion, I might opt for a BLOB/BFILE. I wouldn't consider a LONG or LONG RAW.
86
Depends on what version of Oracle you are using Either CLOB or Long
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
