Rollback procedure creating file

Question

I have several PL/SQL procedures that export tables in a file using UTL_FILE.

Here's a snap:

PROCEDURE export_t1
  AS
    l_file      UTL_FILE.FILE_TYPE;
    record       VARCHAR2(4096);
  BEGIN

    l_file := UTL_FILE.FOPEN(DIRECTORY_PATH, FILENAME, 'A');

    FOR j IN
    (SELECT * FROM PRODUCTS WHERE HANDLE = '0')
    LOOP
     l_record := j.id || ',' || j.code || ',' || j.desc ....... [others fields];
     UTL_FILE.PUT_LINE(l_file,l_record);
    END LOOP;

    UTL_FILE.FCLOSE(l_file);
    UPDATE PRODUCTS SET HANDLE = '1' WHERE HANDLE = '0';

  EXCEPTION
  WHEN OTHERS THEN
    -- log
   RAISE;
  END export_t1;

So I have export_t1, export_t2, export_tn procedures. In addition I call these in a 'main' procedure sequentially..

My question is..if I have an exception in export_t2, which is the second procedure, how can I block the first one (export_t1) to create the file

The idea is..create files just when those ALL procedures are gone OK, no exception

Answer 1

Unless you could get your file system to participate in a two-phase commit (which to my knowledge isn't possible right now), coordinating file output with your database transactions is going to be difficult because your file operations lie outside the scope of your database transaction.

I.e., there is always a theoretical scenario where something happens at exactly the wrong time and your database and file system are out of sync. (Sort of makes you appreciate everything COMMIT does for us).

Anyway, a possible strategy is to design things so the window for something going wrong is as short as possible. E.g.,

begin
  delete_real_files;  -- delete leftovers.
  write_temp_file_n1;
  write_temp_file_n2;
  write_temp_file_n3;
  ...
  write_temp_file_nx;

  rename_temp_files_to_real;

  commit;

  -- don't do anything else with the files after this point

exception
  when others then
    remove_real_files;
    remove_temp_files;
    rollback;
end;

The idea here is that you write all the files to temp files. If there is a failure, you clean them up. No process could ever see the "real" files, because you never created them. Only at the end do you make the temporary files real, by renaming them.

Your risk here is that your first few temp files get renamed successfully, but the subsequent temp files cannot get renamed AND either (A) a process jumps in and sees them before your exception handler can remove them or (B) the exception handler cannot remove them for some reason.

I like this approach because it ties all the risk to renaming files, which is a pretty safe operation (since it does not require extra disk space). It's not very likely that some of the renames will succeed and some will fail.

Lots of variations on this approach are possible. But the thing to remember is that you're not implementing a rock-solid solution here. There's always a chance that something goes wrong, so implement whatever checks (elsewhere in your system) are required, depending on how much fault tolerance you have.