?
Solved

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

Posted on 2009-04-10
12
Medium Priority
?
430 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
12 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24115009
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 48

Expert Comment

by:schwertner
ID: 24115060
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
ID: 24115066
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Author Comment

by:mrong
ID: 24115222
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 48

Expert Comment

by:schwertner
ID: 24115261
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
ID: 24115293
So Just call the first code once? thanks.
0
 
LVL 48

Expert Comment

by:schwertner
ID: 24115302
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
ID: 24157167
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
ID: 24158328
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
ID: 24158346
I guess here is the last question-> How do I populate data into the message body? thanks.
0
 
LVL 48

Accepted Solution

by:
schwertner earned 400 total points
ID: 24158610
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
ID: 24158715
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
Suggested Courses

777 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