databaseoracleoracle11gutl-file

Can Oracle UTL_FILE have multiple files open at the same time?

Is it possible to use Oracle UTL_FILE to have multiple files open at the same time?

Currently, I'm not able to test this myself as I don't have permissions, and unable to GRANT them to myself, to be able to open and write to files with UTL_FILE.

Solution

  • Yes, it is possible. Each call to utl_file.fopen() and utl_file.fopen_nchar() returns a different file descriptor record. Store each result to a different PL/SQL variable and you're safe.

    declare
    l_file_1                utl_file.file_type;
    l_file_2                utl_file.file_type;
begin
    l_file_1 := utl_file.fopen(
        location => 'MY_INPUT_DIRECTORY',
        filename => 'my_input_file.txt',
        open_mode => 'rb'
    );
    l_file_2 := utl_file.fopen(
        location => 'MY_OUTPUT_DIRECTORY',
        filename => 'my_output_file.txt',
        open_mode => 'wb'
    );
    /*
    your multi-file handling logic comes here...
    */
    utl_file.fclose_all();
exception
    when others then
        utl_file.fclose_all();
        raise;
end;
/