How to schedule jobs in Oracle?

[Former Member]

Hello everyone,

Can you explain me how to schedule job in oracle?

I want to execute one stored procedure weekly. How would I schedule job to execute that procedure weekly?

Regards

Swati

[Former Member]

closed

[former_member184494]

Swati,

Does this have anything to do with SAP BI.

If this is to do with the Oracle DB that your SAP Bi runs on - then you need to look at the netweaver BASIS forums.

Am redirecting the post to the BASIS forums - if this is not related to SAP at all - request you to close the post.

Arun

[Former Member]

Hi Arun

I am weekly pulling data from oracle table in BW. Data is getting populated in that table by one procedure.

My requirement is :

1.Each time when I pull data first I should execute that procedure so that data will be populated in the table.

2.Then I should delete all data from data target & pull data from that table so that data target will not have any old data, it will have only current data.

So I am using one process chain which I have scheduled in a way such that it will run weekly. It will first delete data from data target & then pull data in BW from table.

Before doing all this I need to execute procedure also.

My question is how to execute procedure weekly?

Can we do it using Process chain?

Or I have to write cron job to execute the procedure weekly?

Or can I schedule this job in oralce itself?

Regards

Swati.

[Former Member]

>

...

> Or can I schedule this job in oralce itself?

> Regards

Hi,

• before 10g:

you may check the dbms_job package in ORACLE.

simply it can execute i.e. a procedure with a scheduled time pattern:

DBMS_JOB.SUBMIT ( 
   job       OUT BINARY_INTEGER,
   what      IN  VARCHAR2,
   next_date IN  DATE DEFAULT sysdate,
   interval  IN  VARCHAR2 DEFAULT 'null' );

example (the commit is important because without it no job will be started): weekly job at 9 a.m.

BEGIN
 DECLARE
 v_JobNum   NUMBER;
 BEGIN
 DBMS_JOB.SUBMIT(v_JobNum,'your_proc;',sysdate,'trunc(sysdate)+7+9/24');
 commit;
 end;
 END;

sysdate:

your date when to execute it

trunc(sysdate)79/24 :

execute the job weekly , reset to midnight , add 9 hours. the interval has to be set that way that you avoid a sliding job

• 10g and above:

you have a additional package dbms_scheduler (a more advanced implementation)

please check the documentation