Nope, I would use dbms_job to

a) make it appear blazingly fast. (utl_smtp uses a blocking call -- it'll take a second)
b) make it transactional (roll back that insert -- drat, got an email anyway)

if you have my book expert one on one Oracle -- i flesh this concept out fully.

see below

Ok, maybe it'll sell a book or two. Here is a lengthly extract from my book "Expert One on One Oracle" that describes this in painstaking detail:

....

The main routine you will use with DBMS_JOB is the SUBMIT routine. Its interface is as follows:

PROCEDURE SUBMIT
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
JOB BINARY_INTEGER OUT
WHAT VARCHAR2 IN
NEXT_DATE DATE IN DEFAULT
INTERVAL VARCHAR2 IN DEFAULT
NO_PARSE BOOLEAN IN DEFAULT
INSTANCE BINARY_INTEGER IN DEFAULT
FORCE BOOLEAN IN DEFAULT

Where the arguments to the SUBMIT routine have the following meanings:

o JOB: is a job identifier. It is system assigned (it is an OUT only parameter). You can use this to query the USER_JOBS or DBA_JOBS views by job id to see information about that job. Additionally some routines such as 'run' and 'remove' take the job id as their only input to uniquely identify the job to run or remove.

o WHAT: is the SQL Text of what will be run. It must be a valid PL/SQL statement or block of code. For example, to run a stored procedure P, you might pass the string P; (with the semi-colon) to this routine. Whatever you submit in the WHAT parameter will be wrapped in the following PL/SQL block:

DECLARE
job BINARY_INTEGER := :job;
next_date DATE := :mydate;
broken BOOLEAN := FALSE;
BEGIN
WHAT
:mydate := next_date;
IF broken THEN :b := 1; ELSE :b := 0; END IF;
END;

That is why you need to add the ';' to any statement - in order to just replace the WHAT with your code, it will need a semi-colon

o NEXT_DATE: Is the next (or since we are just submitting the first) time to run the job. The default is SYSDATE - run as soon as possible (after committing).

oINTERVAL: Is a string containing a date function that calculates the next time to run the job. You can consider this function to be 'selected from dual'. If you pass in the string 'sysdate+1', the database will in effect execute 'select sysdate+1 into :next_date from dual'. See below for some caveats on setting the interval of a job to prevent 'sliding'.

o NO_PARSE: Determines whether the WHAT parameter is parsed upon submission. By parsing the string - you can be reasonable sure the string is in fact executable. In general, NO_PARSE should always be left with its default of FALSE. When set to true, the WHAT parameter is accepted 'as is' with no validity checking.

o INSTANCE: Is only meaningful in Parallel Server mode, a mode Oracle can run in on a loosely coupled cluster of machines. This would specify the instance upon which this job should be executed. By default, this will have a value of ANY_INSTANCE.

o FORCE: Again, this is only meaningful in Parallel Server mode. If set to TRUE (the default) you may submit the job with any instance number - even if that instance is not available at the time of job submission. If set to FALSE, submit will fail the request if the associated instance is not available.

....

Now that we have a working knowledge of how DBMS_JOB works and what functions are available to us, we'll look at how to run a job once, how to set up a recurring job correctly, and how to monitor our jobs and find out what errors they have encountered.


Running a job once

Many of the jobs I run are 'one off' jobs. I use DBMS_JOB much as one would use the & in Unix or the start command in Windows to run a process in the background. The example I gave above with regards to sending email is a good example. I use DBMS_JOB to make the sending of email not only transactional but appear to be fast. Here is one implementation of that to demonstrate how to run a job once. We'll start with a small stored procedure to send mail using the supplied UTL_SMTP package:

tkyte@TKYTE816> create or replace
2 PROCEDURE send_mail (p_sender IN VARCHAR2,
3 p_recipient IN VARCHAR2,
4 p_message IN VARCHAR2)
5 as
6 -- NOTE that you have to use a host
7 -- that supports SMTP and that you have access to.
8 -- you do not have access to this host and must change it
9 l_mailhost VARCHAR2(255) := 'aria.us.oracle.com';
10 l_mail_conn utl_smtp.connection;
11 BEGIN
12 l_mail_conn := utl_smtp.open_connection(l_mailhost, 25);
13 utl_smtp.helo(l_mail_conn, l_mailhost);
14 utl_smtp.mail(l_mail_conn, p_sender);
15 utl_smtp.rcpt(l_mail_conn, p_recipient);
16 utl_smtp.open_data(l_mail_conn );
17 utl_smtp.write_data(l_mail_conn, p_message);
18 utl_smtp.close_data(l_mail_conn );
19 utl_smtp.quit(l_mail_conn);
20 end;
21 /

Procedure created.

Now, to time how long that takes - I'll run it two times:

tkyte@TKYTE816> set serveroutput on
tkyte@TKYTE816> declare
2 l_start number := dbms_utility.get_time;
3 begin
4 send_mail( 'asktom_us@oracle.com',
5 'asktom_us@oracle.com', 'hey there' );
6 dbms_output.put_line
7 ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
8 ' seconds' );
9 end;
10 /
.81 seconds

PL/SQL procedure successfully completed.

tkyte@TKYTE816> /
.79 seconds

PL/SQL procedure successfully completed.

It looks like that will consistently take on the order of 8/10's of a second to send a mail during the best of times. As far as I am concerned, that is far too long. We can do much better - well, we can do much 'apparently' better. We'll use jobs to give this the appearance of being much faster - and gain the benefit of a 'transactional' email as well.

We will start by creating a table to store the email and a procedure we could run against this table to send an email. This procedure will ultimately become our background job. A question is - why am I using a table to store the emails? Why not just pass parameters to the job? The reason is bind variables and the shared pool. Since all jobs are created using the WHAT parameter - and the database will simply 'execute' that string at run time, we want to make sure that the WHAT parameter we submit is something that will be in the shared pool. We could easily just submit a job such as:

dbms_job.submit( x, 'send_mail(''asktom_us@oracle.com'',
''asktom_us@oracle.com'', ''hello'' );' );

but that would have the effect of flooding our shared pool with hundreds or thousands of unique statements - killing our performance. Since we plan on sending lots of emails (anything more than 1 is lots and would mandate the use of bind variables) - we need to be able to submit something like:

dbms_job.submit( x, 'background_send_mail( constant );' );

Well, as it turns out there is an easy way to do this. We simply need to create a table that contains a field for each parameter we really wanted to send to the routine (sender, recipient, message in this case) plus an ID primary key field. For example:

tkyte@TKYTE816> create table send_mail_data( id number primary key,
2 sender varchar2(255),
3 recipient varchar2(255),
4 message varchar2(4000),
5 senton date default NULL );

Table created.

Here I added an ID column as a primary key - and in this case a sent on senton column. We'll use this table not only as a place to queue up outgoing emails but to keep a persistent log of emails sent and when they were sent (very handy, trust me, for when people say "but I didn't get the notification"). Now all we need to do is figure out a way to generate a key for this table and get it to our background process using a constant string. Fortunately DBMS_JOB already does that for us. When we schedule a job, it automatically creates a job id for it and returns that to us. Since the block of code it wraps around our WHAT parameter includes this job id, we can simply pass it to ourselves! That means, our fast_send_mail routine will look like this:

tkyte@TKYTE816> create or replace
2 PROCEDURE fast_send_mail (p_sender IN VARCHAR2,
3 p_recipient IN VARCHAR2,
4 p_message IN VARCHAR2)
5 as
6 l_job number;
7 begin
8 dbms_job.submit( l_job, 'background_send_mail( JOB );' );
9 insert into send_mail_data
10 ( id, sender, recipient, message )
11 values
12 ( l_job, p_sender, p_recipient, p_message );
13 end;
14 /

Procedure created.

This routine will submit a job background_send_mail and pass it the JOB parameter. If you refer to the WHAT parameter description above - you'll see the block of code includes 3 local variables we have access to - we are simply passing ourselves one of them. The very next thing we do in this procedure is to insert the email into our queue table - for delivery later. So, DBMS_JOB creates the primary key, then we insert the primary key with the associated data into this table. That's all we need to do. Now, we need to create the background_send_mail routine and it is simply:

tkyte@TKYTE816> create or replace
2 procedure background_send_mail( p_job in number )
3 as
4 l_rec send_mail_data%rowtype;
5 begin
6 select * into l_rec
7 from send_mail_data
8 where id = p_job;
9
10 send_mail( l_rec.sender, l_rec.recipient, l_rec.message );
11 update send_mail_data set senton = sysdate where id = p_job;
12 end;
13 /

Procedure created.

It reads out the data we saved, calls the slow send_mail routine and then updates the record to record the fact that we actually sent the mail. Now, we can run fast_send_mail and see how fast it really is:

tkyte@TKYTE816> declare
2 l_start number := dbms_utility.get_time;
3 begin
4 fast_send_mail( 'asktom_us@oracle.com',
5 'asktom_us@oracle.com', 'hey there' );
6 dbms_output.put_line
7 ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
8 ' seconds' );
9 end;
10 /
.03 seconds

PL/SQL procedure successfully completed.

tkyte@TKYTE816>
tkyte@TKYTE816> declare
2 l_start number := dbms_utility.get_time;
3 begin
4 fast_send_mail( 'asktom_us@oracle.com',
5 'asktom_us@oracle.com', 'hey there' );
6 dbms_output.put_line
7 ( round( (dbms_utility.get_time-l_start)/100, 2 ) ||
8 ' seconds' );
9 end;
10 /
.02 seconds

PL/SQL procedure successfully completed.

As far as our end users are concerned - this fast_send_mail is 26 to 40 times faster then the original send mail. It is not really, it just appears to be that much faster (and that is what really counts). The actual sending of the mail will happen in the background after they commit. That is an important note here: if you run this example, make sure you commit when using the DBMS_JOB example else the email will never get sent. The job will not be visible to the job queue processes till you do (your session can see the job in the USER_JOBS view - but the job queue processes won't see it until you commit). Don't take this as a limitation - it is actually a feature, we've just made email transactional. If you rollback, so does your send mail. When you commit, it'll be delivered.

if you do a one time job, not a recurring job, there is no record left behind -- it just runs and exits and cleans itself up.

You would log this yourself, the job would become something like:


dbms_job.submit(:n, 'declare n number; begin n := dbms_utility.get_time; myproc(job); n := dbms_utility.get_time-n; insert into your_own_special_log_table values ( ......, n ); end;'


do you have Expert One on One Oracle? I covered dbms_job in gory detail in that book in the appendix, showing you how to use NEXT_DATE, another parameter.

when you run with dbms_job, there is 'no password' available to be sent over.

if you create a private link with the username/password to be used, it will work.

i don't see a delete in there anywhere? so how can the message be removed??

i'm sorry but that update is *not* happening. you'll have to prove that, you don't need jobs or utl_mail -- just change send_mail to be raise_application_error() and show us the update happening.

I need to understand why the job is removed (from the job queue) even
when some errors appeared during the execution of the job (internally), because
these jobs disappear from the job queue and the "message" was gone....

that is NOT happening is my point -- unless your job catches and ignores the error.


You will have to provide an ENTIRE TEST CASE (remember -- utl_smtp isn't relevant here -- just make send_mail be "raise....") that shows a job that failed getting removed -- for the simple reason that it doesn't work like that.

So, I can only assume that your job is really something like:

dbms_job.submit( l_job, 'begin send_.... exception when others log_error; end;' )

that when others without a raise makes the error go "poof", the job "succeeded"

I'm guessing that is what you have done, and I have to guess cause you have a partial example here.

well, you should read up on dbms_job or dbms_scheduler.

I believe you want to understand what you are doing - not just be told what to do. Scheduling a recurring job is something you'll want to do over and over - so research those two packages (dbms_scheduler is preferred if you can use it, better logging facilities)