Home » SQL & PL/SQL » SQL & PL/SQL » Send a mail with automatic
Send a mail with automatic [message #262501] Mon, 27 August 2007 05:24 Go to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

i am working on HTMLDB.
I created one package to send the Birthday wish mail.
This should be run automatic every midnight. Could you please let me know how to correct it.
The code is :

create or replace package BDAY_PKG is
procedure bday_proc;
end;


create or replace package body "BDAY_PKG" is
procedure BDAY_PROC
is
l_body CLOB :=' ';
l_body_html CLOB;
BEGIN
FOR c1 IN (SELECT name,mailid

FROM SSI_EMP_BDAYS

WHERE to_char(birthday,'dd-mon')=to_char(sysdate,'dd-mon'))

LOOP

IF c1.mailid IS NOT NULL THEN

HTMLDB_MAIL.SEND(

P_FROM => c1.mailid,
P_TO => c1.mailid,
P_BODY => 'Wish you a very Happy Birthday',
P_SUBJ => 'Time to wish a Birthday !!!');

END IF;
END LOOP;
HTMLDB_MAIL.PUSH_QUEUE('mail.XXX.com',25);
end BDAY_PROC;
end BDAY_PKG;

[Updated on: Mon, 27 August 2007 05:25]

Report message to a moderator

Re: Send a mail with automatic [message #262519 is a reply to message #262501] Mon, 27 August 2007 06:19 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
What problems do you have with it?

MHE
Re: Send a mail with automatic [message #262534 is a reply to message #262519] Mon, 27 August 2007 07:31 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

I am using HTMLDB. Created one package. No error. complied successfully. I want to send this package details automatically every midnight using dbms_job. But I don't know how to do that and please let me know any set up required for that.


Regards,
Nayana
Re: Send a mail with automatic [message #262539 is a reply to message #262534] Mon, 27 August 2007 07:46 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
You'd use DBMS_SCHEDULER. Look at this page for some examples.

MHE
Re: Send a mail with automatic [message #262541 is a reply to message #262534] Mon, 27 August 2007 07:51 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Created one procedure called BadyWish and in that mentioned the following code:

declare
jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT
(job => jobno
,what => 'begin BDAY_PKG.bday_proc; end;'
,next_date => SYSDATE
,interval => 'SYSDATE + 10/1440');
COMMIT;
end;

Procedure successfully compiled. But i didn't get automated mail to my id. Please let me know why? please asap..

Thanks a lot in advance.

Regards,
Nayana
Re: Send a mail with automatic [message #262622 is a reply to message #262541] Mon, 27 August 2007 22:46 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Please do the needful..its very urgent.

My procedure is :


create or replace procedure "BDAY_WISH"
is
jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT
(job => jobno
,what => 'begin BDAY_PKG.bday_proc; end;'
,next_date => SYSDATE
,interval => 'SYSDATE + 10/1440');
COMMIT;
END;

HTMLDB application is loaded to my local machine not server. Is that problem? Please let me know asap.

Regards,
Nayana
Re: Send a mail with automatic [message #262696 is a reply to message #262622] Tue, 28 August 2007 01:36 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
nayana wrote on Tue, 28 August 2007 05:46
Please do the needful..its very urgent.
No it's not urgent. Not to me.

nayana wrote on Tue, 28 August 2007 05:46
My procedure is :
....
And what is the problem with it?


nayana wrote on Tue, 28 August 2007 05:46
HTMLDB application is loaded to my local machine not server. Is that problem?
You tell me. How can I know whether that is a problem?

MHE
Re: Send a mail with automatic [message #266186 is a reply to message #262696] Mon, 10 September 2007 02:18 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

I changed DBMS_job.submit procedure.

DECLARE
jobno number;
BEGIN
DBMS_JOB.SUBMIT(job => jobno

,what => 'BDAY_PKG.BDAY_PROC;'

,next_date => SYSDATE

,interval => 'SYSDATE + 10/1440');

DBMS_JOB.RUN(job => jobno);
COMMIT;
END;


Its working now but only once. Could you please let me know the reason for the same.

Thanks in advance.

Regards,
Nayana
Re: Send a mail with automatic [message #266191 is a reply to message #266186] Mon, 10 September 2007 02:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
job_queue_processes > 0?

Regards
Michel
Re: Send a mail with automatic [message #266214 is a reply to message #266191] Mon, 10 September 2007 04:01 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

Yes!

ALTER SYSTEM SET job_queue_processes =2;
But still i got only one mail. Please advice.

Thanks,
Nayana
Re: Send a mail with automatic [message #266236 is a reply to message #266214] Mon, 10 September 2007 04:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post columns from dba_jobs for the job.
But before:
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Regards
Michel
Re: Send a mail with automatic [message #266264 is a reply to message #266214] Mon, 10 September 2007 06:03 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

Used following statement :

select * from DBA_JOBS;

and result for the above statement attached. Please advice me how to correct this..

Regards,
Nayana
  • Attachment: report.xls
    (Size: 27.00KB, Downloaded 394 times)
Re: Send a mail with automatic [message #266268 is a reply to message #266264] Mon, 10 September 2007 06:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't want and can't download xls files.
Just post the output for the relevant job.
Read the guide and format your post.

Regards
Michel
Re: Send a mail with automatic [message #266280 is a reply to message #266264] Mon, 10 September 2007 06:23 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

When I run the dba_job i got the following output:


JOB LOG_USER PRIV_USER SCHEMA_USER LAST_DATE LAST_SEC
 THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC TOTAL_TIME BROKEN
 INTERVAL FAILURES WHAT NLS_ENV MISC_ENV INSTANCE 

3605 ANONYMOUS NAYANA NAYANA 10-SEP-07 14:26:15 -  -  
10-SEP-07 16:51:05 4 N sysdate+10/1440 15 BDAY_PKG.BDAY_PROC; 
NLS_LANGUAGE='AMERICAN' NLS_TERRITORY='AMERICA' 
NLS_CURRENCY='$' NLS_ISO_CURRENCY='AMERICA' NLS_NUMERIC_CHARACTERS='.,' NLS_DATE_FORMAT='DD-MON-RR' 
NLS_DATE_LANGUAGE='AMERICAN' NLS_SORT='BINARY' 0102000200000000 1 



Please let me know how to correct this job.

Regards,
Nayana

[Updated on: Tue, 11 September 2007 04:06] by Moderator

Report message to a moderator

Re: Send a mail with automatic [message #266286 is a reply to message #266280] Mon, 10 September 2007 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What don't youo understand in "Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button."?

Regards
Michel
Re: Send a mail with automatic [message #266291 is a reply to message #266280] Mon, 10 September 2007 06:57 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
15 failures.. Looks like something goes wrong.
Either that, or you should copy-paste what you get from sqlplus, so the headers are placed correctly over the data..
Re: Send a mail with automatic [message #266609 is a reply to message #266291] Tue, 11 September 2007 04:04 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

select *from dbs_job;

result is:

last_DATE LAST_SEC NEXT_DATE NEXT_SEC TOTAL_TIME BROKEN  FAILURES 
11-SEP-07 13:48:36 11-SEP-07 14:32:55      4       N            5 
 



Please advice.

Regards,
Nayana
Re: Send a mail with automatic [message #266613 is a reply to message #266609] Tue, 11 September 2007 04:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Check alert.log and udump/bdump the reason of failures.

Regards
Michel
Re: Send a mail with automatic [message #266649 is a reply to message #266613] Tue, 11 September 2007 05:27 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

alert.log file shows like this:

 Tue Sep 11 15:33:14 2007
Errors in file c:\oraclexe\app\oracle\admin\xe\bdump\xe_j000_3376.trc:
ORA-12012: error on auto execute of job 3646
ORA-20001: This procedure must be invoked from within an application session.
ORA-06512: at "FLOWS_020100.WWV_FLOW_MAIL", line 170
ORA-06512: at "NAYANA.BDAY_PKG", line 25
ORA-06512: at line 1


Not able to understand what exactly the mistake. Could you please let me know.

Regards,
Nayana
Re: Send a mail with automatic [message #266650 is a reply to message #266649] Tue, 11 September 2007 05:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
ORA-20001: This procedure must be invoked from within an application session

This is an error YOU put in your code.
It seems you do not want to do what you are currently doing when you coded the application.
Check it and correct it as you want.

Regards
Michel

Re: Send a mail with automatic [message #266677 is a reply to message #266649] Tue, 11 September 2007 06:58 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

Still its failed!

I used the following code:

create or replace package BDAY_PKG is
procedure bday_proc;
end;

create or replace package body "BDAY_PKG" is
procedure BDAY_PROC is
 
l_body CLOB :=' ';
l_body_html CLOB;
  
BEGIN
 FOR c1 IN (SELECT name,mailid
 FROM   SSI_EMP_BDAYS
 WHERE  to_char(birthday,'dd-mon')=to_char(sysdate,'dd-mon'))

LOOP
 IF c1.mailid IS NOT NULL  
THEN
 
l_body_html := '<html>
  
                <body>'||chr(10);

l_body_html := l_body_html||'Dear '||c1.name||''; 
 
l_body_html := l_body_html||'<br>Birthday Mail'||'';
       

HTMLDB_MAIL.SEND(

                 P_FROM        =>  'nayana.patikkal@oracle.com',
                 P_TO          =>  c1.mailid,
                 P_BODY        =>  l_body,
                 P_BODY_HTML   =>  l_body_html, 
                 P_SUBJ =>  'TestMail !!!');                    
   END IF; 
END LOOP;
HTMLDB_MAIL.PUSH_QUEUE('XXX.XXX.com',25);   
  
end BDAY_PROC; 
end BDAY_PKG;



And In SQL Command prompt I used dbms_job.submit procedure.

Could you please let me know is this package and procedure is correct or not.

Regards,
Nayana
Re: Send a mail with automatic [message #266679 is a reply to message #266677] Tue, 11 September 2007 07:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is NOT the procedure that raises the error.
Please read the alert.log lines.

Regards
Michel
Re: Send a mail with automatic [message #268403 is a reply to message #266679] Tue, 18 September 2007 07:57 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

I used the following code within my application.

Its working!!

wwv_flow_api.set_security_group_id;


Thanks a Lot!


One more help please..

How to send a single mail to different users at a time? Please advice.

Regards,
Nayana

Re: Send a mail with automatic [message #270466 is a reply to message #268403] Thu, 27 September 2007 01:29 Go to previous messageGo to next message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

My application is working now. But still one problem!

My table and value is:
name birthday mailid
 AAA  27-Sep    xx.xx
 BBB  27-Sep    xxx.x 


If I used my codes(earlier mentioned)it will send two separate mail for each person. I want only one single mail along with their names something like this..
 Dear AAA,BBB,
    Wish U Happy Birthday 


Could you please advice.

Thanks in advance.
Regards,
Nayana
Re: Send a mail with automatic [message #270526 is a reply to message #270466] Thu, 27 September 2007 06:04 Go to previous message
nayana
Messages: 33
Registered: August 2007
Location: Bangalore
Member
Hi,

Thanks a Lot!
In my application used Array and cursor .

Its working!!!!



Regards,
Nayana
Previous Topic: Exception Handling
Next Topic: From Sybase to Oracle
Goto Forum:
  


Current Time: Mon Dec 05 23:46:23 CST 2016

Total time taken to generate the page: 0.19678 seconds