Re: Automating SQL calls

From: Jens Baumeister <invalid_at_spam.no>
Date: Fri, 11 May 2001 20:22:30 +0200
Message-ID: <9dhajk$9f9$1@news.netcologne.de>

Tim Mavers <webview_at_hotmail.com> schrieb in im Newsbeitrag: 3afc28ca$0$16467$45beb828_at_newscene.com...
> I am wondering what is the best way to automate SQL calls in Oracle 8i? I
> need to run a bunch of statements automatically several times a week.
>
> Is the best way to use sqlplus and specify a .SQL file (with the GET
> command)?
>
> I am coming from a MS SQL Server environment (and am used to DTS), so I am
> not sure about Oracle basics.

I hope I didn't misunderstand your question; if I did, just ignore the post. ;-)

It depends on what your SQL need to do. The usual way to automate SQL is by using jobs.

See this document for info about that:

http://otn.oracle.com/docs/products/oracle8i/doc_library/817_doc/server.817/ a76956/jobq.htm#8312#8312

(Note that your init-file needs to specifiy the line "job_queue_processes = x" with x being a number greater 0, otherwise your jobs won't get executed.)

Hint:
You might want to run a job at a certain time of day. Since this isn't easy to figure out for the Oracle newbie, here's an example:

Let's say you want to ru a job every day at 3 a.m. Just insert it like this:

VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,

      'your_job_name',
      TRUNC(SYSDATE)+3/24, 'TRUNC(SYSDATE) + 1 + 3/24');

COMMIT;
END;
/

This sets the date of the last execution to 3.am today [TRUNC(SYSDATE) gets you midnight today as a result, 1/24 of a day is 1 hour so TRUNC(SYSDATE)+3/24 is midnight plus 3 hours] and the date of the next execution is midnight today + 1day + 3 hours, which is exactly what we want.

This is all very brief, but should make more sense after reading the above link.

Jens Received on Fri May 11 2001 - 13:22:30 CDT