CLOB

MySQL

Example

CREATE TABLE test_table (
    id INT NOT NULL AUTO_INCREMENT,
    file_name VARCHAR(50) NOT NULL,
    file_content TEXT,
    PRIMARY KEY (id)
);

INSERT INTO test_table(file_name, file_content)
VALUES ('test_file', 'This is a test content for CLOB');

Output

Query OK, 1 row affected (0.00 sec)

Query OK, 1 row affected (0.01 sec)

Explanation

This example creates a test_table with a CLOB (Character Large Objects) field file_content and other fields like id and file_name. It then inserts a row into the table with test_file as the file name and ‘This is a test content for CLOB’ as the large text content. This serves as an example to handle and store large textual data in MySQL using CLOB.

PostgreSQL

Example

CREATE TABLE documents (
   id SERIAL PRIMARY KEY,
   content TEXT
);

INSERT INTO documents (content)
VALUES ('This is a test for CLOB data in PostgreSQL');

Output

You won’t get an explicit output from the SQL statements above. You can confirm that the data is saved correctly in the documents table by running a SELECT command.

For example:

SELECT * FROM documents;

The output will be:

 id |                 content
----+------------------------------------------
  1 | This is a test for CLOB data in PostgreSQL
(1 row)

Explanation

PostgreSQL doesn’t have an explicit CLOB data type, instead, it offers large object data types such as TEXT and BYTEA which can store up to 1 GB of text or binary data, fulfilling the aim of a CLOB. The example creates a table named documents, and then inserts a row containing a large string of text into the content column of the table. The SELECT statement verifies that the data was saved correctly.

SQL Server

Example

DECLARE @CLOB AS NVARCHAR(MAX);
SET @CLOB = 'This is a CLOB example';
SELECT @CLOB;

Output

'This is a CLOB example'

Explanation

In SQL Server, Large Object (LOB) data types can store large amounts of data. CLOB (Character Large Object) is one type of LOB that can store large amounts of character data. In SQL Server, the NVARCHAR(MAX) data type is typically used to represent CLOB. The provided code declares a variable @CLOB as NVARCHAR(MAX), sets its value to ‘This is a CLOB example’, then selects the value of the variable to output it.

Oracle

Example

DECLARE
    large_text CLOB;
BEGIN
    large_text := TO_CLOB('This is a large amount of text that is being stored in a CLOB data type in Oracle.');
    DBMS_OUTPUT.PUT_LINE(large_text);
END;
/

Output

This is a large amount of text that is being stored in a CLOB data type in Oracle.

Explanation

In this example, a CLOB data type is declared with the variable name large_text. The TO_CLOB function is used to convert a long string of characters into a CLOB data type which can hold up to 4 GB in Oracle. ‘DBMS_OUTPUT.PUT_LINE’ is used to display the contents of the CLOB data type.

SQLite

Example

CREATE TABLE documents (
   id INTEGER PRIMARY KEY,
   content TEXT
);

INSERT INTO documents (content)
VALUES ('This is a CLOB example. CLOB stands for Character Large Object. CLOB data type is used to store large amount of textual data.');

Output

Query OK, 0 rows affected (0.08 sec)

Explanation

In SQLite, the TEXT datatype is used instead of CLOB to store large amounts of text. The above code declares a new table named “documents”, with one of the columns set as TEXT to hold the CLOB data. This could include large text files, JSON objects, XML documents and so on. It further inserts a sentence into the created table. There’s no visible output from the INSERT statement itself, other than a report that the query ran successfully.