LONG data type is not used more in Oracle (use not recommended and may cause performance issues)

Openfire Openfire Support
1

Long is no longer used in Oracle and only exists for compatibility with older systems.

You can only have one long field per table.

Solution: switch to CLOB or NCLOB.

Official documentation:

  • Oracle Data Types
  • Datatypes

Would like some openoffice developer exchange both Openfire tables as plugins the LONG type by type recommended by Oracle (LOB columns (CLOB, NCLOB, BLOB))?

LONG Datatype

LONG columns store variable-length character strings containing up to 2 gigabytes -1, or 231-1 bytes. LONG columns have many of the characteristics of VARCHAR2 columns. You can use LONG columns to store long text strings. The length of LONG values may be limited by the memory available on your computer. LONG literals are formed as described for “Text Literals”.

Do not create tables with LONG columns. Use LOB columns (CLOB, NCLOB, BLOB) 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 thanLONG columns. Further, LOB functionality is enhanced in every release, whereas LONG functionality has been static for several releases. See the modify_col_properties clause of ALTER TABLE and TO_LOB for more information on converting LONG columns to LOB.

You can reference LONG columns in SQL statements in these places:

  • SELECT lists

  • SET clauses of UPDATE statements

  • VALUES clauses of INSERT statements
    The use of LONG values is subject to these restrictions:

  • A table can contain only one LONG column.

  • You cannot create an object type with a LONG attribute.

  • LONG columns cannot appear in WHERE clauses or in integrity constraints (except that they can appear in NULL and NOT NULLconstraints).

  • LONG columns cannot be indexed.

  • LONG data cannot be specified in regular expressions.

  • A stored function cannot return a LONG value.

  • You can declare a variable or argument of a PL/SQL program unit using the LONG datatype. However, you cannot then call the program unit from SQL.

  • Within a single SQL statement, all LONG columns, updated tables, and locked tables must be located on the same database.

  • LONG and LONG RAW columns cannot be used in distributed SQL statements and cannot be replicated.

  • If a table has both LONG and LOB columns, then you cannot bind more than 4000 bytes of data to both the LONG and LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the LONG or the LOB column.
    In addition, LONG columns cannot appear in these parts of SQL statements:

  • GROUP BY clauses, ORDER BY clauses, or CONNECT BY clauses or with the DISTINCT operator in SELECT statements

  • The UNIQUE operator of a SELECT statement

  • The column list of a CREATE CLUSTER statement

  • The CLUSTER clause of a CREATE MATERIALIZED VIEW statement

  • SQL built-in functions, expressions, or conditions

  • SELECT lists of queries containing GROUP BY clauses

  • SELECT lists of subqueries or queries combined by the UNION, INTERSECT, or MINUS set operators

  • SELECT lists of CREATE TABLE … AS SELECT statements

  • ALTER TABLE … MOVE statements

  • SELECT lists in subqueries in INSERT statements
    Triggers can use the LONG datatype in the following manner:

  • A SQL statement within a trigger can insert data into a LONG column.

  • If data from a LONG column can be converted to a constrained datatype (such as CHAR and VARCHAR2), then a LONG column can be referenced in a SQL statement within a trigger.

  • Variables in triggers cannot be declared using the LONG datatype.

  • :NEW and :OLD cannot be used with LONG columns.
    You can use Oracle Call Interface functions to retrieve a portion of a LONG value from the database.

See Also:

Oracle Call Interface Programmer’s Guide

*Source: Datatypes *

It may be that one of the reasons not to store all the messages in Openfire, is this.