Directory Creation File naming

Question

I have created a directory and created an anonymous block to create a file in it. Every time if I execute the block the file is overwritten. I need it to be created as a new file. What should I do to attain this.

DECLARE
  v_name    utl_file.file_type;
  v_count   NUMBER := 0;
BEGIN
  v_name := utl_file.fopen('PLSQL_DIR', 'Task1.txt', 'W');
  utl_file.put_line(
    v_name,
    TO_CHAR(SYSDATE, 'DD/MM/YY/HH/MI')||' Data Migration Starts'
  );
  FOR i IN (SELECT * FROM sue_par_det_tb) LOOP
    utl_file.put_line(
      v_name,
      i.patient_name||', '|| i.ord_date||', '|| i.mobile_number||', '|| i.refered_by
    );
    v_count := v_count + 1;
  END LOOP;
  utl_file.put_line(
    v_name,
    v_count||' Rows generated at '||TO_CHAR(SYSDATE, 'DD/MM/YY/HH24/MI')
  );
  utl_file.fclose(v_name);
END;
/

Answer 1

Split your file name into name and extension and then check if the file exists and, if it does, then append a number to the name and check if that file exists and loop until you do not find a file.

Something like:

DECLARE
  v_file     utl_file.file_type;
  v_dir      VARCHAR2(30)  := 'PLSQL_DIR';
  v_filename VARCHAR2(256) := 'Task1.txt';
  v_name     VARCHAR2(256) := SUBSTR( v_filename, 1, INSTR( v_filename, '.', -1 ) - 1 );
  v_ext      VARCHAR2(256) := SUBSTR( v_filename, INSTR( v_filename, '.', -1 ) );
  v_num      INTEGER       := NULL;
  v_count    NUMBER        := 0;
BEGIN
  LOOP
    EXIT WHEN NOT FileExists( v_dir, v_filename );
    IF v_num IS NULL THEN
      v_num := 1;
    ELSE
      v_num := v_num + 1;
    END IF;
    v_filename := v_name || '.' || v_num || v_ext;
  END LOOP;
  v_name := utl_file.fopen( v_dir, v_filename, 'W' );
  -- rest of your code
END;
/