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

# 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
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.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
END;
BEGIN
END;