Oracle PL/SQL keyword AUTONOMOUS_TRANSACTION will cause a procedure to create another session, do a transaction, commit/rollback just that private transaction, and return flow control back to the parent.
Oh..never send email on uncommitted data.
EDIT: (due to edit of original post)
Roughly the same problem arises if you want to import files from a
directory. If you delete the file inside the transaction, then the
transaction might fail and the file was deleted but never imported. Or
you delete the file after the transaction. Then the delete of the file
might fail and the file gets imported a second time.
This type of problem is called a bug.
The solution is:
- Define each step as its own
TRANSACTION
- You'll want to create them in a way that you can rerun (or skip) the step(s) as needed
- Run each step in the appropriate order.
- do not send email prior to
COMMIT.
- do not delete a file prior to successful loading of the data
- You'll need to keep track of "where you are at" and if that step has passed/failed.
EMAIL example
You should have a procedure to sendEmail that should be called after commit.
If you want to call the procedure prior to commit, you'll need to add a row to a queue that will rollback with the main transaction. For Oracle, this will be either Advance Queuing or the package APEX_MAIL
By putting it in a separate procedure, you can sendEmail a 2nd time upon [end-user's] request.
Process File
You have an algorithm that contains a few steps where each step can fail. this is actually different than your sendEmail problem.
You need to record what you are processing, where you are at within your algorithm, and if that step have succeeded or failed.
In order to recover from an error at any step, each step of the process needs to be defined as a discrete TRANSACTION.
In Oracle, I would have these procedures (1 procedure per TRANSACTION):
create or replace
package file_processing_package
as
procedure update_file_processing_status(
p_id IN files_to_process.id%TYPE
, p_status IN process_states.id%TYPE);
function add_a_file_to_be_processed( p_filename IN files_to_process.file_name%TYPE )
return files_to_process.id%TYPE;
procedure load_data_from_file( p_id in files_to_process.id%TYPE );
procedure process_already_loaded_data( p_id in files_to_process.id%TYPE );
procedure delete_file_from_os( p_id in files_to_process.id%TYPE );
end;
/
This is based on the following tables:
CREATE TABLE PROCESS_STATES (
id int generate by default on null as identity, -- 12c+
state_desc varchar2(25) not null,
constraint process_states_pk primary key (id),
constraint process_states_uq1 unique (state_desc)
);
insert into process_states( state_desc ) values ( 'file to be processed' );
insert into process_states( state_desc ) values ( 'file loaded' );
insert into process_states( state_desc ) values ( 'processing' );
insert into process_states( state_desc ) values ( 'processing failed' );
insert into process_states( state_desc ) values ( 'processing succeeded' );
insert into process_states( state_desc ) values ( 'delete failed' );
insert into process_states( state_desc ) values ( 'OK' ); -- delete succeeded
commit;
CREATE TABLE FILES_TO_PROCESS (
id int generate by default on null as identity, -- 12c+
file_name varchar2(50) not null,
process_state_id int not null,
constraint file_to_process_pk primary key (id),
constraint file_to_process_uq1 unique (file_name),
constraint file_to_process_fk1 foreign key (process_state_id)
references (process_states.id)
);
The UNIQUE constraint on FILE_NAME prevents the same file being processed twice.
