Skip to content

Instantly share code, notes, and snippets.

@jrichardsz
Forked from SteveRuben/copyfile.plsql
Created April 24, 2021 23:41
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save jrichardsz/1dc825e843035ad67213dd50ebb2e0b3 to your computer and use it in GitHub Desktop.
Save jrichardsz/1dc825e843035ad67213dd50ebb2e0b3 to your computer and use it in GitHub Desktop.
copy file in oracle, utl_file.fcopy doesn't work for some type of file and the only way to copy a file is to do it as raw file. So here is an example.
CREATE OR REPLACE PROCEDURE copyfile (in_director in varchar2, in_filename IN VARCHAR2, out_directory in varchar2, out_filename IN VARCHAR2)
IS
in_file UTL_FILE.file_type;
out_file UTL_FILE.file_type;
buffer_size CONSTANT INTEGER := 32767; -- Max Buffer Size = 32767
buffer RAW (32767);
buffer_length INTEGER;
BEGIN
-- Open a handle to the location where you are going to read the Text or Binary file from
-- NOTE: The 'rb' parameter means "read in byte mode" and is only available
-- in the UTL_FILE package with Oracle 10g or later
in_file := UTL_FILE.fopen ('IN_FILE_LOC', in_filename, 'rb', buffer_size);
-- Open a handle to the location where you are going to write the Text or Binary file to
-- NOTE: The 'wb' parameter means "write in byte mode" and is only available
-- in the UTL_FILE package with Oracle 10g or later
out_file := UTL_FILE.fopen ('OUT_FILE_LOC', out_filename, 'wb', buffer_size);
-- Attempt to read the first chunk of the in_file
UTL_FILE.get_raw (in_file, buffer, buffer_size);
-- Determine the size of the first chunk read
buffer_length := UTL_RAW.LENGTH (buffer);
-- Only write the chunk to the out_file if data exists
WHILE buffer_length > 0
LOOP
-- Write one chunk of data
UTL_FILE.put_raw (out_file, buffer, TRUE);
-- Read the next chunk of data
IF buffer_length = buffer_size
THEN
-- Buffer was full on last read, read another chunk
UTL_FILE.get_raw (in_file, buffer, buffer_size);
-- Determine the size of the current chunk
buffer_length := UTL_RAW.LENGTH (buffer);
ELSE
buffer_length := 0;
END IF;
END LOOP;
-- Close the file handles
UTL_FILE.fclose (in_file);
UTL_FILE.fclose (out_file);
EXCEPTION
-- Raised when the size of the file is a multiple of the buffer_size
WHEN NO_DATA_FOUND THEN
-- Close the file handles
UTL_FILE.fclose (in_file);
UTL_FILE.fclose (out_file);
END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment