help! - batch job to run sp 1

[kam3t]

I need to create a batch job to run an Oracle stored proc every Monday at 4am.

Our oracle instance is not a management server, it is stand alone and therefore I do not have access to schedule jobs via the EMC. (I do not try to pretend to be a DBA, but that is the answer that I received when I asked about it).

What is the syntax to kick off the stored proc in a batch file (*.bat)? So that I can schedule it using the AT command.

TIA.

 

[SantaMufasa]

Kam,

First, I recommend your testing your command you wish to schedule with the following command from your DOS prompt:

<path_prompt> sqlplus <username>/<password>@<host_string> @<path>/<script.sql>

Here is an example of an actual command I just ran:

D:\oracle\ora92\bin>sqlplus system/passw@dhunt @d:\dhunt\sqldba\sharedpool

SQL*Plus: Release 9.2.0.4.0 - Production on Wed Dec 3 17:23:03 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production


                 Sharable     Persistent        Runtime
  COUNT(*)         Memory         Memory         Memory
---------- -------------- -------------- --------------
        63        740,659         59,552        249,440

Flushing Shared_Pool...

System altered.


                 Sharable     Persistent        Runtime
  COUNT(*)         Memory         Memory         Memory
---------- -------------- -------------- --------------
        25        289,600         25,528         90,104

SQL> exit

Be sure to include the "exit" statement in your script if you want your scheduled job to exit back out to the system prompt.

Once you are satisfied with the behavior of your command line, implement the command line in your scheduler.

Let us know if this works for you,

Mufasa
(aka Dave of Sandy, Utah, USA @ 00:29 (04Dec03) GMT, 17:29 (03Dec03) Mountain Time)

 

[sem]

I suppose that you don't need EMC. You may schedule your job using DBMS_JOB package right from sql*plus. The syntax is:

var jobno number
exec DBMS_JOB.SUBMITjobno,'begin your_proc; end;', to_date('8.12.03 4:00', 'dd.mm.rr hh24:mi'), 'trunc(sysdate,'W')+1/6')

The last parameter - just a formula to calculate the date of next start, i.e. "first day of the week plus 1/6 of a day (4 hours)" If your week starts on Monday, you may use this value. In other cases you should add your specific number of days between week start and Monday, e.g. for US is should be 'trunc(sysdate,'W')+1+1/6'

Then you may issue

print jobno

to get a job handler, that may be used to control this job in future (read about other procedures in that package)

Don't forget preconditions of using Oracle jobs: values of JOB_QUEUE_PROCESSES and JOB_QUEUE_INTERVAL



Regards, Dima

http://seminihin.narod.ru