Encapsulating email, network exchanges, and other “immediate” actions into an Oracle transaction

Oracle, like most databases, supports atomic transactions. This prevents DML changes from being seen by other users until you commit your transaction. It also provides the ability to rollback a transaction should an exception occur and you do not want the changes committed to the database. However, Oracle offers several packages that support more “immediate” operations that cannot, by their very nature, be made part of a transaction. One example of such an operation is a network exchange, such as sending an email or connecting to a web server. Let’s say, as part of a transaction, you would like to make a post to a web site using UTL_HTTP. You’d like to only post to this webpage if you succeed and would like to “rollback” this post with the rest of the transaction should an exception occur. An oversimplified example of a posting function might look like this:

SQL> CREATE OR REPLACE PROCEDURE web_post
  2  IS
  3    req UTL_HTTP.REQ;
  4    resp UTL_HTTP.RESP;
  5  BEGIN
  6
  7    req := UTL_HTTP.BEGIN_REQUEST( url => 'http://127.0.0.1', method => 'POST' );
  8    UTL_HTTP.SET_HEADER ( req, 'Content-Type', 'application/x-www-form-urlencoded' );
  9    UTL_HTTP.SET_HEADER( req, 'Content-Length', '12' );
 10    UTL_HTTP.WRITE_TEXT( req, 'posting data' );
 11    resp := UTL_HTTP.GET_RESPONSE( r => req );
 12
 13  EXCEPTION
 14    WHEN OTHERS THEN
 15      NULL;
 16  END;
 17  /

Procedure created.

However, if you call this procedure mid-transaction, the post will go out immediately and cannot be reversed. To avoid the need to rollback the post, all the information can be saved until the very end of the transaction. However, this can be cumbersome to code. It also does not tie directly to the transaction and relies on you to perform the post operation at the very end of the transaction, once you are certain a rollback is not needed. A trick to couple this type of operation to a database transaction is to use a job. When a job is submitted in Oracle, it is not actually scheduled for execution until the transaction is committed. If a rollback occurs, the job will never be scheduled in the queue. The following code shows such an example:

SQL> SELECT TO_CHAR( sysdate, 'DD-MON-YYYY HH24:MI:SS' ) curr_date
  2  FROM dual;

CURR_DATE
-----------------------------
19-SEP-2014 23:03:06

SQL>
SQL> DECLARE
  2    jobid NUMBER;
  3  BEGIN
  4    DBMS_OUTPUT.PUT_LINE( 'Do stuff here...' );
  5    DBMS_JOB.SUBMIT ( jobid, 'web_post;', SYSDATE+1/24/60/60 );
  6    DBMS_OUTPUT.PUT_LINE( 'Do more stuff here...' );
  7  END;
  8  /
Do stuff here...
Do more stuff here...

PL/SQL procedure successfully completed.

SQL> SELECT
  2    job,
  3    SUBSTR( what, 1, 10 ) what,
  4    TO_CHAR( next_date, 'DD-MON-YYYY HH24:MI:SS' ) next_date
  5  FROM user_jobs;

       JOB WHAT                 NEXT_DATE
---------- -------------------- -----------------------
        16 web_post;            19-SEP-2014 23:03:07

SQL> COMMIT;

Commit complete.

SQL> SELECT
  2    job,
  3    SUBSTR( what, 1, 10 ) what,
  4    TO_CHAR( next_date, 'DD-MON-YYYY HH24:MI:SS' ) next_date
  5  FROM user_jobs;

no rows selected

Notice how the job is scheduled one second after the time of submission. This means the job will execute almost immediately after committing the transaction. Before the commit, the scheduled job is available in the user_jobs view. After the commit, the job is no longer visible in user_jobs, indicating it has executed and completed.
Notice that I added an exception block to the web_post procedure that hides all errors. DBA_JOBs will try to run repeatedly if they fail and the error is thrown out to the job handler. This example assumes the post to the web page is not really important and a failure does not need to logged. Most likely, this will not be the case in a real application, but you need to be aware that jobs will retry if an exception is thrown to the job handler.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s