Re: Auto-run batch processes in Oracle

From: Bert De Ridder <b.deridder_at_nissan.be>
Date: Thu, 22 Jun 2000 08:32:34 +0200
Message-Id: <10535.110105@fatcity.com>

I don't know about other platforms, but I have to issue a commit after a = dbms_job.submit
(7.3 on Netware)

Bert De Ridder
Nissan Belgium

Disclaimer : The opinions expressed in this message are my own,

                   and are not necessarily those of NV Nissan Belgium.

>>> "Libal, Ivo" <ivo.libal_at_knapp-systems.com> 21/06/2000 15:51:32 >>> How many background job processes do you have? see init.ora params:
JOB_QUEUE_PROCESSES
JOB_QUEUE_INTERVAL
by default #of processes=3D0
Ivo Libal

> -----Urspr=FCngliche Nachricht-----
> Von: B Siva Shankar [SMTP:bsshankar_at_chennai.tcs.co.in]=20
> Gesendet am: Mittwoch, 21. Juni 2000 14:57
> An: Multiple recipients of list ORACLE-L
> Betreff: Re:Auto-run batch processes in Oracle

>=20
>=20
>=20

> Hello Guru,

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

>=20

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

>=20

> 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.

>=20

> Can you please explain this?

>=20

> Shiva.

>=20
>=20
>=20
>=20

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

>=20

> To: B Siva Shankar/MLs/TCSCHENNAI_at_TCSCHENNAI, Multiple recipients of
> list
> ORACLE-L <ORACLE-L_at_fatcity.com>
> cc:

>=20
>=20
>=20
>=20
>=20

> Shiva,

>=20
> 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:

>=20


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

>             defer_parsing?);

>=20

> Where:

>=20


>     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.

>=20

> Example that I have:

>=20


>     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;

>     /

>=20
>=20


> ____________________Reply Separator____________________

> Author: "B Siva Shankar" <bsshankar_at_chennai.tcs.co.in>

> Date:       6/16/00 4:27 AM

>=20
>=20
>=20

> Hi List,

>=20

> 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.

>=20

> Thanks,
> Shiva.

>=20
>=20

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

>=20


> 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
> also send the HELP command for other information (like subscribing).

>=20
>=20
>=20
>=20

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

>=20


> 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
> also send the HELP command for other information (like subscribing).

--
Author: Libal, Ivo
  INET: ivo.libal_at_knapp-systems.com=20

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

Received on Thu Jun 22 2000 - 01:32:34 CDT