write huge text into clob column in oracle

 varchar2 max character size is 32767 , that's why writing huge texts into column is problem.

CLOB type is used for this, it can store 2,147,483,647 characters.

But it is a bit problematic to write more than 32767 characters into CLOB typed column. For doing that either we should read from file or divide into small chunks.

In example we will do this by chunks

Firstly:

package org.example;

import java.io.*;
import java.util.ArrayList;
import java.util.List;


public class FileChunker {

    private static final int CHUNK_SIZE = 21000; // Size of each chunk

    public static void main(String[] args) {
        String filePath = "/Users/bbb/Documents/aaa/new_license/license_en.html"; // Path to your file
        String outputDir = "/Users/bbb/Documents/aaa/new_license/chunks_en"; // Directory to save chunks
        try {
            List<String> chunks = splitFileIntoChunks(filePath);
            createFilesFromChunks(chunks, outputDir);
            System.out.println("Chunks created successfully.");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static List<String> splitFileIntoChunks(String filePath) throws IOException {
        List<String> chunks = new ArrayList<>();
        try (BufferedReader reader = new BufferedReader(new FileReader(filePath))) {
            StringBuilder stringBuilder = new StringBuilder();
            String line;
            while ((line = reader.readLine()) != null) {
                stringBuilder.append(line).append(System.lineSeparator());
                while (stringBuilder.length() >= CHUNK_SIZE) {
                    chunks.add(stringBuilder.substring(0, CHUNK_SIZE));
                    stringBuilder.delete(0, CHUNK_SIZE);
                }
            }
            // Add any remaining content as the last chunk
            if (stringBuilder.length() > 0) {
                chunks.add(stringBuilder.toString());
            }
        }
        return chunks;
    }

    public static void createFilesFromChunks(List<String> chunks, String outputDir) throws IOException {
        File dir = new File(outputDir);
        if (!dir.exists()) {
            if (dir.mkdirs()) {
                System.out.println("Directory created: " + outputDir);
            } else {
                throw new IOException("Failed to create directory: " + outputDir);
            }
        } else {
            System.out.println("Directory already exists: " + outputDir);
        }

        // Create a file for each chunk
        for (int i = 0; i < chunks.size(); i++) {
            File chunkFile = new File(dir, "chunk_" + (i + 1) + ".txt");
            try (BufferedWriter writer = new BufferedWriter(new FileWriter(chunkFile))) {
                writer.write(chunks.get(i));
            }
            System.out.println("Created file: " + chunkFile.getAbsolutePath());
        }
    }
}

above code will create chunks for large file.

after that we create pl/sql procedure 

CREATE OR REPLACE PROCEDURE UpdateBlobFromVarcharEN AS

    -- Declare a VARCHAR2 variable with the text content

    v_varchar_chunk1 VARCHAR2(32767) := 'copy_and_paste_chunk_here'; -- Replace with your actual data chunk

    v_varchar_chunk2 VARCHAR2(32767) := 'copy_and_paste_chunk_here'; -- Replace with your actual data chunk

    -- Declare a CLOB variable

    v_clob CLOB;

BEGIN

    -- Initialize the CLOB

    DBMS_LOB.CREATETEMPORARY(v_clob, TRUE, DBMS_LOB.SESSION);

    

     -- Convert and append the first chunk to the CLOB

    DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar_chunk1), v_varchar_chunk1);

    

    -- Convert and append the second chunk to the CLOB

    DBMS_LOB.WRITEAPPEND(v_clob, LENGTH(v_varchar_chunk2), v_varchar_chunk2);

    -- Update the CLOB column in the table

    UPDATE VERSIONS_TABLE

    SET licensetext_en = v_clob

    WHERE ID = 1;

    -- Commit the transaction

    COMMIT;

    -- Free the temporary CLOB

    DBMS_LOB.FREETEMPORARY(v_clob);

EXCEPTION

    WHEN OTHERS THEN

        -- Handle exceptions

        ROLLBACK;

        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);

END UpdateBlobFromVarcharEN;

compile procedure.

last step is calling compiled procedure:

begin

  UpdateBlobFromVarcharEN;

end;

column will be updated with new value