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.