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

Sometimes it happens that you have a very large text or CSV file to process, but first you want to make smaller files of that large file.  Because that large file may take too much time to process or open. So I am giving an example below to split large text/CSV file into multiple files in PL SQL using stored procedure.

You just need to pass two parameters to this PL SQL procedure, first is database directory object name, where the text files are residing and the second is the source file name (the file which you want to split).

If Oracle directory object is not exists for the location of text files, then you can create it as shown below:

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

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

Change the path above according to yours files location. Then create the below procedure by executing its script:

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;

This procedure splitting 100 rows for each file, which you can modify as per your need. Now execute this procedure as shown below by passing database directory object name and the file name:

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

You can check your file location (CSV_FILE_DIR) for the multiple files starting with numbers like 1_text_file.csv, 2_text_file.csv and so on, as shown in below image: