While UTL_FILE.FCOPY can be used to copy valid text files, it does not copy binary files or invalid text files. An example of an invalid text file would be one that contains a character sequence that cannot be generated by the characters on your keyboard (i.e. the sequence of two CR s in a row on a Windows platform rather than CRLF, CRLF) and hence may be considered a binary file.
NOTE: As UTL_FILE first supported the reading and writing of binary files in 10gR1, this note can only be used with Oracle Database versions 10gR1 and later.
Solution
Create two directory objects for the IN and OUT file locations.
CREATE OR REPLACE DIRECTORY in_file_loc AS 'C:\temp\in';
CREATE OR REPLACE DIRECTORY out_file_loc AS 'C:\temp\out';
NOTE: You must have the CREATE ANY DIRECTORY privilege to execute these statements.
Create the procedure CopyFile.
-- Procedure to COPY a Text or Binary file
CREATE OR REPLACE PROCEDURE copyfile (
in_filename 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;
/
Test procedure CopyFile with both Text and Binary files.
-- Test a TEXT file
EXEC CopyFile('original_textfilename.txt', 'new_textfilename.txt')
-- Test a BINARY File
EXEC CopyFile('original_binaryfilename.jpg', 'new_binaryfilename.jpg')
Check the IN_FILE_LOC and OUT_FILE_LOC and verify that the files were successfully copied. Compare the actual size in bytes to make sure they are an exact copy.
NOTE: As UTL_FILE first supported the reading and writing of binary files in 10gR1, this note can only be used with Oracle Database versions 10gR1 and later.
Solution
Create two directory objects for the IN and OUT file locations.
CREATE OR REPLACE DIRECTORY in_file_loc AS 'C:\temp\in';
CREATE OR REPLACE DIRECTORY out_file_loc AS 'C:\temp\out';
NOTE: You must have the CREATE ANY DIRECTORY privilege to execute these statements.
Create the procedure CopyFile.
-- Procedure to COPY a Text or Binary file
CREATE OR REPLACE PROCEDURE copyfile (
in_filename 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;
/
Test procedure CopyFile with both Text and Binary files.
-- Test a TEXT file
EXEC CopyFile('original_textfilename.txt', 'new_textfilename.txt')
-- Test a BINARY File
EXEC CopyFile('original_binaryfilename.jpg', 'new_binaryfilename.jpg')
Check the IN_FILE_LOC and OUT_FILE_LOC and verify that the files were successfully copied. Compare the actual size in bytes to make sure they are an exact copy.
