Solved

Store Procude to send an email message out from Oracle 10g

Posted on 2009-04-10
12
421 Views
Last Modified: 2013-12-18
Greeting,

I am trying to use store procedue to read data from Oracle 10g table and send email message out. The store procedue needs to run every 5min and it go thru the table and check the field->SendMail. if it is false/null then send the email outfor that record and update it to true,otherwise go to the next one. I can use SMTP from W2K3 server to send the messge.
I am not sure what needs to be done to make this work, so pls provide some sample if possible.
thanks.

-M
0
Comment
Question by:mrong
  • 7
  • 4
12 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
Comment Utility
We use a Java stored procedure that calls Javamail. You have to learn how to create stored procedures in your database, then we schedule an overall PL/SQL job from DBMS_SCHEDULER that iterates a table and calls the Java stored procedure with each address / message and receives the status. Sorry I cannot post code here, this is a commercial app, and I don't have time to today to write a sample for you, but just wanted to point you to our approach if it helps.

Please research PL/SQL, Javamail and Java stores procedures in Oracle.

0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
You have to schedule the procedure that is doing this:

set serveroutput on
set linesize 10000
BEGIN
   DBMS_OUTPUT.enable(100000);  
   dbms_scheduler.create_job(
      job_name => 'weekly_stats'
     ,job_type => 'STORED_PROCEDURE'
     ,job_action => 'SEND_MAIL'
     ,start_date => TO_TIMESTAMP_TZ('2009/10/22 2:10:00.000000 +08:00','yyyy/mm/dd hh24:mi:ss.ff tzh:tzm')
     ,repeat_interval => 'FREQ=WEEKLY'
     ,enabled => TRUE
     ,comments => 'Computes statistics over tables and indexes.'
     );
     DBMS_OUTPUT.PUT_LINE('Job successfully created.');
EXCEPTION
     WHEN OTHERS THEN
       DBMS_OUTPUT.PUT_LINE(substr(sqlerrm,1,200));
END;
/

The procedure should additionally read the table that is object of your attention and
do all what you mention in your posting.
PROCEDURE SEND_MAIL

(V_MAILTO IN VARCHAR2

,V_MAILFROM IN VARCHAR2

,V_SUBJECT IN VARCHAR2

,V_BODY IN LONG

,V_CC IN VARCHAR2 := null

,V_BCC IN VARCHAR2 := null

)

IS

-- PL/SQL Specification

CONN UTL_SMTP.CONNECTION;

dato varchar2(4000);
 

-- PL/SQL Block

BEGIN

DECLARE

CONN UTL_SMTP.CONNECTION;

dato VARCHAR2(4000);

nSqlCode NUMBER(8);

vSqlErrm VARCHAR2(4000);

vSrv VARCHAR2(100);
 

BEGIN
 

vSrv := 'smtp.server.com;

CONN := UTL_SMTP.OPEN_CONNECTION(vSrv);

UTL_SMTP.HELO(CONN,'Origin.server.com');

UTL_SMTP.MAIL(CONN,v_mailfrom);

UTL_SMTP.RCPT(CONN,v_mailto);

UTL_SMTP.OPEN_DATA(CONN);
 

UTL_SMTP.WRITE_DATA(CONN,'Date:'||TO_CHAR(SYSDATE,'dd mon rrrr hh24:mi:ss')||utl_tcp.CRLF);

UTL_SMTP.WRITE_DATA(CONN,'From:'||v_mailfrom||utl_tcp.CRLF);

UTL_SMTP.WRITE_DATA(CONN,'To:'||v_mailto||utl_tcp.CRLF);

UTL_SMTP.WRITE_DATA(CONN,'Subject:'||v_subject||utl_tcp.CRLF);

UTL_SMTP.WRITE_DATA(CONN,utl_tcp.CRLF);
 

-- Splits body (long) into varchar2s.
 

FOR I IN 0..CEIL(LENGTH(V_BODY)/2000)-1 LOOP
 

DATO:=SUBSTR(V_BODY,I*2000+1,2000);

UTL_SMTP.WRITE_DATA(CONN,DATO);
 

END LOOP;
 

UTL_SMTP.CLOSE_DATA(CONN);

UTL_SMTP.QUIT(CONN);
 
 

EXCEPTION

WHEN utl_smtp.transient_error THEN

nSqlCode := SQLCODE;

vSqlErrm := SQLERRM;
 

BEGIN

UTL_SMTP.CLOSE_DATA(CONN);

EXCEPTION WHEN OTHERS THEN NULL;

END;
 

BEGIN

utl_smtp.quit(conn);

EXCEPTION WHEN OTHERS THEN NULL;

END;
 

raise_application_error(-20100, vSqlErrm);
 

WHEN utl_smtp.permanent_error THEN

nSqlCode := SQLCODE;

vSqlErrm := SQLERRM;

BEGIN

UTL_SMTP.CLOSE_DATA(CONN);

EXCEPTION WHEN OTHERS THEN NULL;

END;
 

BEGIN

utl_smtp.quit(conn);

EXCEPTION WHEN OTHERS THEN NULL;

END;
 

raise_application_error(-20200, vSqlErrm);

END;

END SEND_MAIL;

Open in new window

0
 

Author Comment

by:mrong
Comment Utility
mrjoltcola:
Thank you for your post. I am just wondering if there is any easy way...just call the store procedure every time after an inert stmt. In that case, no much coding involve.

thanks.
0
 

Author Comment

by:mrong
Comment Utility
schwertner:
Thank you for your code and it helps a lot.
Questions.
1.Your first code calls the second code. How to make the first code run? just call it once and it repeat running in its FREQ defined in the code.
2. you mentioned to use Java in your first post but I didn't see it in your code. Can you explain it a little bit.
thanks!
0
 
LVL 47

Expert Comment

by:schwertner
Comment Utility
The first code SCHEDULES the second code to run weekly.
Read the docs for DBMA_SCHEDULER to change it to run every 5 minutes.
It will automatically invoke the second code.
The second code is AS IS. The package uses Java but behind the stage.
Possibly you can find more up to date PL/SQL scripts to send emails.
0
 

Author Comment

by:mrong
Comment Utility
So Just call the first code once? thanks.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 47

Expert Comment

by:schwertner
Comment Utility
But check in the views if it is really scheduled:

/* use this to see which jobs are in
** the queue
*/

 

select owner, job_name, start_date,next_run_date, schedule_type  from dba_scheduler_jobs;

select owner, job_name, schedule_type  from dba_scheduler_jobs;

select * from dba_scheduler_jobs;
0
 

Author Comment

by:mrong
Comment Utility
schwertner,

I configured the utl_mail in my oracle 10g and when I creating the following AFTER INSERT trigger from  SQL Plus worksheet got error "Warning: Trigger created with compilation errors."

Below is my trigger

CREATE OR REPLACE TRIGGER mssg_after_insert
AFTER INSERT
    ON mssg_log
    FOR EACH ROW
BEGIN
 IF (:new.dsc='PM') THEN
    INSERT INTO fmax_mssg_log_BK(ent_date,Proposal,trans_num,send_yn,phsdsc)
    VALUES
     ( :new.ent_date,
       :new.item,
       :new.trans_num,
       :new.send_yn,
       :new.dsc);
       UTL_MAIL.SEND('mymail@yahoo.com','yourmail@yahoo.com',NULL,NULL,'Testing Message','This is a test','text/plain',NULL);
END IF;
END;

Any suggestions?
thanks.
0
 

Author Comment

by:mrong
Comment Utility
ok. I need to grant the privs to the user like to following.

SQL> grant execute on utl_mail to scott;

Grant succeeded.
0
 

Author Comment

by:mrong
Comment Utility
I guess here is the last question-> How do I populate data into the message body? thanks.
0
 
LVL 47

Accepted Solution

by:
schwertner earned 100 total points
Comment Utility
PROCEDURE SEND_MAIL
....
,V_BODY IN LONG   ------> pass body as text  here <----------
....
BEGIN
.....
-- Splits body (long) into varchar2s.
 
FOR I IN 0..CEIL(LENGTH(V_BODY)/2000)-1 LOOP
 
DATO:=SUBSTR(V_BODY,I*2000+1,2000);
UTL_SMTP.WRITE_DATA(CONN,DATO);
 
END LOOP;



0
 

Author Comment

by:mrong
Comment Utility
I am not sure if I quite follow you.
Say I have field1 and field2 need to be populated into the email. what will it looks like?
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now