Oracle Database - Long (Text) and Long Raw Datatype
Table of Contents
1 - Warning
Do not create tables with LONG columns. Use LOB columns (CLOB, NCLOB) instead. LONG columns are supported only for backward compatibility.
Oracle also recommends that you convert existing LONG columns to LOB columns. LOB columns are subject to far fewer restrictions than LONG columns. Further, LOB functionality is enhanced in every release, whereas LONG functionality has been static for several releases.
When streaming a LONG or LONG RAW, all the data always moves across the network but it's not the case with LOBs and BFILEs that only move the necessary bits of data across the network.
2 - Articles Related
3 - Long datatype
Columns defined as LONG can store variable-length character data containing up to 2 gigabytes of information. LONG data is text data that is to be appropriately converted when moving among different systems.
LONG datatype columns are used in the data dictionary to store the text of view definitions. You can use LONG columns in SELECT lists, SET clauses of UPDATE statements, and VALUES clauses of INSERT statements.
4 - LONG Restrictions vs LOB
The following list compares the semantics of LONG and LOB datatypes in various application development scenarios:
- The number of LONG type columns is limited. Any given table can have a maximum of only one LONG type column. The number of LOB type columns in a table is not limited.
- You can use the data interface for LOBs to enable replication of tables that contain LONG or LONG RAW columns. Replication is allowed on LOB columns, but is not supported for LONG and LONG RAW columns. The database omits columns containing LONG and LONG RAW datatypes from replicated tables.
If a table is replicated or has materialized views, and its LONG column is changed to LOB, then you may have to manually fix the replicas.
5 - Management
5.1 - TO_LOB
TO_LOB function can be use in a create table statement.
CREATE TABLE tmp_virt AS SELECT TABLE_NAME, TO_LOB(data_default) AS data_default FROM all_tab_cols WHERE virtual_column = 'YES' AND owner = USER ;
6 - Using RAW and LONG RAW Datatypes
The RAW and LONG RAW datatypes store data that is not interpreted by Oracle Database, that is, it is not converted when moving data between different systems. These datatypes are intended for binary data and byte strings. For example, LONG RAW can store graphics, sound, documents, and arrays of binary data; the interpretation is dependent on the use.
Oracle Net and the Export and Import utilities do not perform character conversion when transmitting RAW or LONG RAW data. When Oracle Database automatically converts RAW or LONG RAW data to and from CHAR data, as is the case when entering RAW data as a literal in an INSERT statement, the database represents the data as one hexadecimal character representing the bit pattern for every four bits of RAW data. For example, one byte of RAW data with bits 11001011 is displayed and entered as CB.
You cannot index LONG RAW data, but you can index RAW data. In earlier releases, the LONG and LONG RAW datatypes were typically used to store large amounts of data. Use of these types is no longer recommended for new development. If your application still uses these types, migrate your application to use LOB types. Oracle recommends that you convert LONG RAW columns to binary LOB (BLOB) columns and convert LONG columns to character LOB (CLOB or NCLOB) columns. LOB columns are subject to far fewer restrictions than LONG and LONG RAW columns.