Oracle Long Datatype

by
using the long datatype in oracle 21c

In this Oracle tutorial, we will learn about the Long datatype in the oracle database. Also, we will illustrate how to use it to create Long columns for a table in oracle.

Introduction to Oracle Long Datatype

Oracle 21c is a powerful database management system that offers a wide range of data types to store and manipulate data efficiently. One such data type is the LONG data type.

Variable-length character data can be stored in files up to 2GB in size using the LONG data type. It is an older data type in Oracle, and its use in fresh applications is not advised. Although the LONG data type was first introduced in earlier versions of Oracle, Oracle 21c continues to support it for backward compatibility.

Also, check: Oracle Create Sequence Tutorial

Advantages of Long Datatype in Oracle 21c

  • Storage Effectiveness: Character data with varied lengths up to 2GB in size can be stored using the LONG data type. As a result, it may effectively store substantial amounts of text or other character data while consuming little storage space.
  • Backward Compatibility: For backward compatibility, Oracle 21c continues to support the LONG data type, an older data type. As a result, if you already utilize the LONG data type in your application, there is no need to change your code.
  • The LONG data type is simple to use and can be specified as a variable in a program or as a column in a table. Additionally, it supports common SQL procedures like SELECT, INSERT, and UPDATE, making it easy to manipulate data stored in LONG data-type columns.
  • Flexibility: The LONG data type can be used to store not only character data but also binary data such as images, videos, and audio files. However, it is not recommended to store binary data in the LONG data type.
  • Compatible with Legacy Systems: Many legacy systems still use the LONG data type to store large amounts of text data. If your organization needs to integrate with such systems, the LONG data type can be used to ensure compatibility and seamless data exchange.

Oracle Long datatype Syntax

The syntax for creating a table with a LONG column is as follows:

CREATE TABLE table_name (
   column_name LONG
);

Read: Date Datatype in Oracle Database

Example of Long Datatype in Oracle 21c:

Here’s an example of creating a table with a LONG column, inserting data into it, and retrieving data from it.

CREATE TABLE long_text (
  id NUMBER,
  long_description LONG
);

Now we will insert the values into the following given table

INSERT INTO long_text (id, long_description)
VALUES (1, 'This is a long text that can contain up to 2GB of data. It can be used to store large blocks of text such as memos, paragraphs, or other lengthy documents.');

SELECT long_description FROM long_text WHERE id = 1;
using the long datatype in oracle 21c
using the long datatype in oracle 21c

In this example, we create a table called long_text with two columns: id of NUMBER datatype, and long_description of LONG datatype. We then insert a row into the table with an ID of 1 and a long description string. Finally, we retrieve the long text from the table where the ID is 1 using the SELECT statement.

Also, check: Oracle rowid Datatype

Features of Long Datatype in oracle 21c

  • Maximum size: LONG columns can hold character data with varied lengths up to 2 GB in capacity.
  • Restricted functionality: SELECT statements cannot index LONG columns or use them in the WHERE clause.
  • Oracle automatically converts LONG columns into CLOBs or NCLOBs when they are used in expressions.
  • LONG columns can be used with Oracle versions 8 and older, however, they are no longer supported in Oracle 21c as they have been deprecated since Oracle 8i.
  • Performance: LONG columns can have performance issues due to their large size and limited functionality. CLOB and BLOB columns provide better performance and functionality for large text and binary data.

Disadvantages of Long Datatype in oracle 21c

  • Restricted functionality: SELECT statements cannot index or use LONG columns in the WHERE clause, which reduces their effectiveness in database operations.
  • Performance problems: Considering their size and functionality limitations, LONG columns can perform poorly, especially when compared to CLOB or BLOB columns.
  • Oracle automatically converts a LONG column to a CLOB or NCLOB when it is used in an expression, which can result in data type conversion issues.
  • Data retrieval: Using the TO LOB function to retrieve data from a LONG column might be time-consuming and ineffective.
  • Compatibility issues: The LONG datatype is deprecated and no longer supported in Oracle 21c, which can create compatibility issues with newer applications and systems.
  • Data storage: LONG columns can store variable-length character data up to 2 GB in size, but this large size can result in inefficient use of storage space.

Storage and performance

Storage:
Variable-length character data of up to 2 GB can be stored using the LONG datatype. However, because Oracle allots space in increments of 2K, the actual storage utilized by a LONG column may be significantly larger than the data being stored.

Because of this, storage capacity may be used inefficiently, especially when working with smaller amounts of data. The overall size of the database may be affected by the storage needs of LONG columns, which may slow down backup and restoration processes.

Performance:
LONG columns’ performance may be slower than that of other data types due to their size and functionality restrictions. For instance, LONG columns cannot be indexed or used in a SELECT statement’s WHERE clause, which may affect the speed of the query. Data from a LONG column must be retrieved using the TO LOB function, which can be time-consuming and ineffective.

Conclusion

In this oracle tutorial, we have covered the Long datatype in the oracle database. Also, we covered illustrated how to use it to create Long columns for a table in oracle.

Also, take a look at some more Oracle tutorials.