Oracle UTL_FILE package - Oracle to Aurora PostgreSQL Migration Playbook
Oracle usagePostgreSQL usage
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Oracle UTL_FILE package

With Amazon DMS, you can access data and read/write files on the server’s file system using the Oracle UTL_FILE package. The UTL_FILE package provides APIs to operate on server files, allowing applications to read and write operating system files.

Feature compatibility Amazon SCT / Amazon DMS automation level Amazon SCT action code index Key differences

No compatibility

No automation

N/A

PostgreSQL doesn’t have the UTL_FILE equivalent.

Oracle usage

Oracle UTL_FILE PL/SQL package enables you to access files stored outside of the database such as files stored on the operating system, the database server, or a connected storage volume. UTL_FILE.FOPEN, UTL_FILE.GET_LINE, and UTL_FILE.PUT_LINE are procedures within the UTL_FILE package used to open, read, and write files.

Examples

Run an anonymous PL/SQL block that reads a single line from file1 and writes it to file2.

  • Use UTL_FILE.FILE_TYPE to create a handle for the file.

  • Use UTL_FILE.FOPEN to open streamable access to the file and specify:

    • The logical Oracle directory object pointing to the O/S folder where the file resides.

    • The file name.

    • The file access mode: 'A'=append mode, 'W'=write mode

  • Use UTL_FILE.GET_LINE to read a line from the input file into a variable.

  • Use UTL_FILE.PUT_LINE to write a single line to the output file.

DECLARE
strString1 VARCHAR2(32767);
fileFile1 UTL_FILE.FILE_TYPE;
BEGIN
fileFile1 := UTL_FILE.FOPEN('FILES_DIR','File1.tmp','R');
UTL_FILE.GET_LINE(fileFile1,strString1);
UTL_FILE.FCLOSE(fileFile1);
fileFile1 := UTL_FILE.FOPEN('FILES_DIR','File2.tmp','A');
utl_file.PUT_LINE(fileFile1,strString1);
utl_file.fclose(fileFile1);
END;
/

For more information, see UTL_FILE in the Oracle documentation.

PostgreSQL usage

Amazon Aurora PostgreSQL doesn’t currently provides a directly comparable alternative for Oracle UTL_FILE package.