Split Large Text/CSV File into Multiple Files in PL SQL

Sometimes, you may encounter a situation where you have a very large text or CSV file that is cumbersome to open or process all at once. In such cases, it is often useful to split the large file into several smaller files. This not only makes processing faster and more manageable but also helps to avoid performance issues that can occur when handling large files.

In this article, I will provide a practical example of how you can split a large text or CSV file into multiple smaller files using a PL/SQL stored procedure.

Key Steps to Split a Large File in PL/SQL

The PL/SQL procedure requires two input parameters:

1. Database Directory Object Name: The Oracle directory object where your text files are located.
2. Source File Name: The name of the file you want to split.

Creating the Oracle Directory Object

Before you can use the procedure, ensure that there is an Oracle directory object pointing to the location of your text files. If it does not exist, you can create it as follows:

For Windows systems:

For windows:
CREATE OR REPLACE DIRECTORY CSV_FILE_DIR AS 'D:\plsql\text_files';

For Linux/Unix systems (note the difference in the file path format):

For Linux/Unix (due to difference in path):
CREATE OR REPLACE DIRECTORY CSV_FILE_DIR AS '/plsql/text_files';

Make sure to replace the directory path with the actual location where your files are stored.

PL/SQL Procedure to Split the File

Once your directory object is set up, you can create the following PL/SQL procedure to handle the file splitting:

CREATE OR REPLACE PROCEDURE split_file (p_db_dir IN VARCHAR2,
 p_file_name IN VARCHAR2)
IS
 read_file UTL_FILE.file_type;
 write_file UTL_FILE.file_type;
 v_string VARCHAR2 (32767);
 j NUMBER := 1;
BEGIN
 read_file := UTL_FILE.fopen (p_db_dir, p_file_name, 'r');

WHILE j > 0
 LOOP
 write_file := UTL_FILE.fopen (p_db_dir, j || '_' || p_file_name, 'w');

FOR i IN 1 .. 100
 LOOP -- example to dividing into 100 rows for each file.. you can increase the number as per your requirement
 UTL_FILE.get_line (read_file, v_string);
 UTL_FILE.put_line (write_file, v_string);
 END LOOP;

UTL_FILE.fclose (write_file);
 j := J + 1;
 END LOOP;
EXCEPTION
 WHEN OTHERS
 THEN
 -- this will handle if reading source file contents finish
 UTL_FILE.fclose (read_file);
 UTL_FILE.fclose (write_file);
END;

How the Procedure Works

• The procedure reads the original file line by line.
• It writes a specified number of lines (in this example, 100) to each new file.
• Each smaller file is named with a prefix number followed by the original file name (e.g., 1_text_file.csv, 2_text_file.csv, etc.).
• Once the specified number of lines is written, the current output file is closed and a new file is started for the next set of lines.
• The process continues until the entire source file has been read and split.

Note:
You can adjust the number 100 in the FOR i IN 1 .. 100 loop to control how many lines each output file will contain. Increase or decrease this value as needed based on your requirements.

Executing the Procedure

To execute the procedure, simply call it with the appropriate directory object name and file name as parameters:

BEGIN
 split_file ('CSV_FILE_DIR', 'text_file.csv');
END;

After running the procedure, check the directory specified by your Oracle directory object (CSV_FILE_DIR). You will find multiple files named in sequence, such as:

• 1_text_file.csv
• 2_text_file.csv
• 3_text_file.csv
• ...and so on.

Each of these files will contain up to 100 lines from the original file, making them much easier to work with.

Summary

Splitting large text or CSV files into smaller chunks can be extremely useful when processing or transferring large datasets. With this PL/SQL procedure, you can automate the process directly within Oracle, ensuring efficient and reliable file management. Adjust the file chunk size and directory settings to suit your specific needs, and streamline your data workflows with ease.