Re: Scheduling job at database startup. How?

| If you are on NT, you could try modifying the databases' startup script,
| usually located in x:\orant\database (replace x with correct drive
letter.)
|
| It will be named strs<sid-name>.cmd (ex: strtorcl.cmd) and contains an NT
| level command for starting the database. On the line after the startup
| command, put a server manager command to submit a stored procedure to the
| database's job queue (providing you have setup the database to run jobs).
| Below is just an example and requires some modificiation:
|
| FILE: strtorcl.cmd.. --------------------- e:\orant\bin\oradim80.exe
-startup
| -sid ORCL -usrpwd oracle -starttype srvc,inst -pfile
| e:\orant\database\initorcl.ora set ORACLE_SID=ORCL set
ORACLE_HOME=x:\orant
| x:\orant\bin\svrmgr30.exe "connect system/manager"
| <x:\orant\database\mycmd.sql
|
| >x:\orant\database\mycmd.out
|
| Then in a file called mycmd.sql, put:
| -------------------------------------
| #
| # Start my batch job...
| #
| spool mycmd.log
| set echo on
| declare
| jobno number;
| begin
| execute dbms_job.submit(jobno, 'my_stored_procedure', sysdate+5/24/60,
NULL );
| end;
| /
| spool off
| exit
|
| The sysdate+5/24/60 will allow for 5 minutes before the proc starts and
the
| NULL parameter will remove the job once it is run one time.
|
| I believe this should give you the result you want.

An interesting suggestion. I assume a delay may be necessary after the database
start command and before connecting with SQL*Plus to let the startup complete?
I'll do some testing to see if I can make it work.

I'm a bit skeptical though. Some time back I tried putting some extra stuff in my 'strtORCL.cmd' file. When the file was run manually my extra commands were executed as expected. However, when the computer was restarted the extra commands were not executed. As far as I could tell the Oracle start service was only reading and using the first line of the 'strtORCL.cmd' file.
This was at least a year ago so maybe it works now. I give it a try.

Thanks,
Dana Stockler Received on Sun Nov 01 1998 - 04:37:16 CST