Home » SQL & PL/SQL » SQL & PL/SQL » Auto Email Using DBMS_Schedular (Oracle 10g)
Auto Email Using DBMS_Schedular [message #346853] Tue, 09 September 2008 21:12 Go to next message
sirfkashif
Messages: 70
Registered: September 2007
Location: Rawalpindi
Member
Dear All,

I am trying to run a procedure naming Auto_email on daily basis for which i have created a job using dbms_scheduler.create_job and i know that this job is running but i can't get that job running and the Auto_email procedure is running smoothly independently.

Following code is to create a job

begin
dbms_scheduler.create_job(
      job_name => 'Auto_Email_Schedule'
     ,job_type => 'STORED_PROCEDURE'
     ,job_action => 'auto_email'
     ,number_of_arguments => 0
     ,start_date => '09-Sep-2008 02:00 PM'
     ,repeat_interval => 'FREQ=DAILY'
     ,enabled => TRUE
     ,comments => 'Demo for job schedule.');
end;


the following query returns the "Auto_Email_Schedule"

SELECT * FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = upper('Auto_Email_Schedule');


Any Help will be appreciated

Regards,
Kashif Ali
Re: Auto Email Using DBMS_Schedular [message #346855 is a reply to message #346853] Tue, 09 September 2008 21:24 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above


>i know that this job is running but i can't get that job running
To me the statement above is internally contradictory.

>> i know that this job is running
Provide proof.

>> i can't get that job running
Provide proof.

This job? That job? which job? same job? different job?

>the Auto_email procedure is running smoothly independently.
What is supposed to be the significance of the statement above?
Are you bragging, complaining, or just stating a fact?

http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above
Re: Auto Email Using DBMS_Schedular [message #346857 is a reply to message #346853] Tue, 09 September 2008 21:35 Go to previous messageGo to next message
sirfkashif
Messages: 70
Registered: September 2007
Location: Rawalpindi
Member
Thanks for correction, i certainly made a confusing statement but what i was trying to say that the procedure mentioned in the dbms_schedular.create_job i.e. auto_email when i used it in the form and pl/sql developer i got expected output i.e. emial was sent.

when i tried to automate that procedure using dbms_schedular package, result of the following query was the name of the job i.e.
Auto_Email_Schedule, which i think means that the job is running but that job is not doing which it is supposed to do i.e. running that auto_email procedure mentioned in the create_job Package.

SELECT JOB_NAME FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = upper('Auto_Email_Schedule');  


---------------------------------------------------
Job_Name

AUTO_EMAIL_SCHEDULE

Regards,
Kashif Ali
Re: Auto Email Using DBMS_Schedular [message #346858 is a reply to message #346857] Tue, 09 September 2008 22:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
which i think means that the job is running

The query you posted just means your job is created not it is running or it has run.
Query LAST_START_DATE, LAST_RUN_DURATION, NEXT_RUN_DATE, RUN_COUNT, STATE, FAILURE_COUNT.

Regards
Michel
Re: Auto Email Using DBMS_Schedular [message #346861 is a reply to message #346853] Tue, 09 September 2008 23:13 Go to previous messageGo to next message
sirfkashif
Messages: 70
Registered: September 2007
Location: Rawalpindi
Member
SELECT JOB_NAME,state,t.job_priority,t.enabled,t.schedule_type,t.start_date,t.repeat_interval, t.last_start_date 
FROM DBA_SCHEDULER_JOBS t 
WHERE JOB_NAME = upper('Auto_Email_Schedule');

----------------------------------------------------------------------------------------
JOB_NAME	    STATE	JOB_PRIORITY	ENABLE    SCHEDULE_TYPE            START DATE                     LAST_SRART_DATE    
AUTO_EMAIL_SCHEDULE	SCHEDULED	3	TRUE	CALENDAR 09-SEP-20 08.02.00.000000 PM +00:00 nill



sorry for formating, i am not able to regenerate the results here

[Updated on: Tue, 09 September 2008 23:33] by Moderator

Report message to a moderator

Re: Auto Email Using DBMS_Schedular [message #346867 is a reply to message #346861] Tue, 09 September 2008 23:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*PLus, format your query and result, keep your lines in 80 characters and post ALL the columns I mentioned.

Regards
Michel
Re: Auto Email Using DBMS_Schedular [message #347510 is a reply to message #346853] Thu, 11 September 2008 23:38 Go to previous message
williamgeorge
Messages: 1
Registered: September 2008
Junior Member
As usual, Oracle introduces new features, and we read the documentation about how they work and we start using them… but what about what is not in the documentation? That we find out by practice, experience, or accident. And that is exactly what happened to me today.
-----------------------
williamgeorge

[Updated on: Fri, 12 September 2008 00:40] by Moderator

Report message to a moderator

Previous Topic: Help with query rewrite using analytic function
Next Topic: explicit/implicit inner join
Goto Forum:
  


Current Time: Sat Dec 03 20:35:56 CST 2016

Total time taken to generate the page: 0.08806 seconds