Re:Auto-run batch processes in Oracle

From: B Siva Shankar <bsshankar_at_chennai.tcs.co.in>
Date: Wed, 21 Jun 2000 17:06:49 +0530
Message-Id: <10535.109964@fatcity.com>

Hello Guru,

Thanks a lot. I have submitted one job to be executed at exactly a minute later than current time. The exact command is

exec dbms_job.submit(:x,'batch_test;',sysdate+(1/1540),'sysdate+(1/1540)');

where batch_test is a procedure which inserts a record in a table and commits. Then I checked for the
next_date in dba_jobs for this job. It showed the expected time(i.e. one minute later than current time).
But, when even after 5 minutes, I find no new records in the table. I checked the alert log. But, found nothing
abnormal.

Can you please explain this?

Shiva.

dgoulet_at_vicr.com on 06/16/2000 07:56:02 PM

To: B Siva Shankar/MLs/TCSCHENNAI_at_TCSCHENNAI, Multiple recipients of list

      ORACLE-L <ORACLE-L_at_fatcity.com>
cc:

Subject: Re:Auto-run batch processes in Oracle

Shiva,

    Once you have the procedure written (functions I do not believe can be used directly) then you can use the DBMS_JOB package to schedule the job. Use the "submit" procedure whose's definition is:

dbms_job.submit(job#, what_do_I_do, when_should_I_start, what_interval,

            defer_parsing?);

Where:

    job# is a number that will be returned.     what_do_I_do is the procedure you want run (I think this can be a PL/SQL block as well

    when_should_I_start is a date field, can be sysdate.     What_interval is a character field that would evaluate to a date     defer_parsing is a boolean that defaults to false. It tells the database whether to parse teh <what_do_I_do> field now or when first executed.

Example that I have:

    declare

        job number;
    begin

        dbms_job.submit(job,


                      'data_archive.process_batch',

                            trunc(sysdate)+(18/24),


                      'trunc(sysdate+1)+(18/24)',

                    FALSE);
        dbms_output.put_line('Job Number is '||job);

    end;
    /

____________________Reply Separator____________________

Subject: Auto-run batch processes in Oracle Author: "B Siva Shankar" <bsshankar_at_chennai.tcs.co.in> Date: 6/16/00 4:27 AM

Hi List,

Can any one tell me how to make a batch process run at a predetermined time in ORACLE? This batch process can be a procedure or a function and should run at regular intervals.

Thanks,
Shiva.

--
Author: B Siva Shankar
  INET: bsshankar_at_chennai.tcs.co.in

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Wed Jun 21 2000 - 06:36:49 CDT