Use select inside ‘filename’ property of utl_file.fopen

I have a procedure that extracts data from a view and stores it on disc in CSV format. I’d like a name of the file to contain the most recent timestamp from table MY_TIMESTAMPS. For now it looks like this:

create or replace procedure write_file is
    file_handle UTL_FILE.file_type;
begin
    file_handle := utl_file.fopen('CSV_DIR', 'EXPORT_ABC_'|| (select to_char(max(EVENT_TS)) from MY_TIMESTAMPS) ||'.csv', 'w', 32767);
    for x in (select * from V_MY_VIEW
    loop utl_file.put_line (file_handle, COL1|| ',' ||
                                        COL2|| ',' ||
                                        COL3|| ',' ||
                                        COL4);
    end loop;
    utl_file.fclose(file_handle);
end write_file;

The part with (select to_char(max(EVENT_TS)) from MY_TIMESTAMPS) throws an error. How can I set this dynamic filename property? Maybe write it to some variable at beginning of the procedure? I tried to use DEFINE, but this errors as well.

Answer

Yes, you just might declare a variable in order to use within your select statement with an INTO clause added such as

CREATE OR REPLACE PROCDURE write_file IS
  file_handle utl_file.file_type;
  v_event_ts  varchar2(100);
BEGIN
  SELECT to_char(max(EVENT_TS)) INTO v_event_ts FROM my_timestamps;
  file_handle := utl_file.fopen('CSV_DIR', 'EXPORT_ABC_'||v_event_ts||'.csv', 'w', 32767);
    ......

where an exception handling is not needed for that SELECT statement.