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.

Answer 2

UTL_FILE.FCLOSE (or UTL_FILE.FFLUSH) literally write to disk. If you don't want to write to disk you must not write to disk - don't close or flush a file handler until after all the data has been written to each individual buffer.

Depending on how big n is you could have a lot of open file handlers with a lot of data buffered. This won't be pretty.

It would be better to create another procedure to call UTL_FILE.FREMOVE, which removes a named file (assuming sufficient privileges).

I would be doing this in the Oracle scheduler, given each procedure being a separate step in a chain you can define a rule using the scheduler chain condition syntax to call the procedure to remove the files on an error in te chain.

Answer 3

Michael,

You can probably use the --> utl_file.fremove(DIRECTORY_PATH,FILENAME); in the exception in order to delete the file.

Example code is given below.

Procedure 1 :

CREATE OR REPLACE PROCEDURE SHAREFLE IS
v_MyFileHandle UTL_FILE.FILE_TYPE;
BEGIN
v_MyFileHandle := UTL_FILE.FOPEN('TEST_DIR','HELLO.TXT','a');
UTL_FILE.PUT_LINE(v_MyFileHandle, 'Hello Again for the Second Time! ' ||      
TO_CHAR(SYSDATE,'MM-DD-YY HH:MI:SS AM'));
UTL_FILE.FCLOSE(v_MyFileHandle);
SHAREFLE1;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE
('ERROR ' || TO_CHAR(SQLCODE) || SQLERRM);
NULL; 
END; 

Procedure 2:

 CREATE OR REPLACE PROCEDURE SHAREFLE1 IS
 v_MyFileHandle UTL_FILE.FILE_TYPE;
 BEGIN
 v_MyFileHandle := UTL_FILE.FOPEN('TEST_DIR','HELLO.TXT','a');
   UTL_FILE.PUT_LINE(v_MyFileHandle, 'Hello Again for the Third Time! ' ||    TO_CHAR(SYSDATE,'MM-DD-YY HH:MI:SS AM'));
   UTL_FILE.FCLOSE(v_MyFileHandle);
   EXCEPTION
   WHEN OTHERS THEN
   DBMS_OUTPUT.PUT_LINE
   ('ERROR ' || TO_CHAR(SQLCODE) || SQLERRM);
   utl_file.fremove('TEST_DIR','HELLO.TXT');
   NULL; 
   END; 

Plsql block to call the first procedure.

set serveroutput on;
begin
sharefle;
end;

This code is a pretty simple example of what you have asked. If there is any exception you can check the procedure 2 that in the exception block the file 'HELLO.TXT' is removed(both Procedure 1 and procedure 2 has same file). I have personally checked it and the same is working. Try to create your own exception and check for yourself. In case of any doubt please do comment.

Note: This is NEVER THE BEST WAY TO DO IT. I HAVE SHOWED YOU THAT WE CAN DO IT THIS WAY. Thanks :)