Example of copying/renaming/deleting files on your OS using PL/SQL


DECLARE

ORIG_FILE_NM VARCHAR2(4000) := 'foo.tst';
TMP_FILE_NM VARCHAR2(4000) := 'foo.tmp';
COPIED_FILE_RENM VARCHAR2(4000) := 'foo.txt2';
SRC_DIR VARCHAR2(4000) := 'TEST_DBMS_FILE_TRNSFR';
DEST_DIR VARCHAR2(4000) := 'TEST_DBMS_FILE_TRNSFR2';
FILE_DOES_NOT_EXIST EXCEPTION;
PRAGMA EXCEPTION_INIT(FILE_DOES_NOT_EXIST, -29283);

BEGIN
-- Clean up prev tmp file in case its still there.
BEGIN
UTL_FILE.FREMOVE(LOCATION => DEST_DIR, FILENAME => TMP_FILE_NM);
EXCEPTION
WHEN FILE_DOES_NOT_EXIST THEN
NULL;
END;

-- Copy the sample file (must be a size = multiple of 512B)
DBMS_FILE_TRANSFER.COPY_FILE(SOURCE_DIRECTORY_OBJECT => SRC_DIR
,SOURCE_FILE_NAME => ORIG_FILE_NM
,DESTINATION_DIRECTORY_OBJECT => DEST_DIR
,DESTINATION_FILE_NAME => TMP_FILE_NM);

-- rename the file @ the dest
UTL_FILE.FRENAME(SRC_LOCATION => DEST_DIR
,SRC_FILENAME => TMP_FILE_NM
,DEST_LOCATION => DEST_DIR ,DEST_FILENAME => COPIED_FILE_RENM);
END;