How to schedule a job in pl/sql

oracle10gplsql

I have created one stored procedure with name

traffic_details_temp_send_mail;

How to make this procedure to run everyday at 10AM?

Please help with block of code.

Thanks in advance.

Best Answer

you can create a scheduler job:

begin
    dbms_scheduler.create_job(job_name        => 'TRAFFIC_DETAILS_JOB',
                              job_type        => 'STORED_PROCEDURE',
                              job_action      => 'traffic_details_temp_send_mail',
                              start_date      => systimestamp,
                              end_date        => null,
                              repeat_interval => 'freq=daily; byhour=10; byminute=0; bysecond=0;',
                              enabled         => true,
                              auto_drop       => false,
                              comments        => 'your description here.');
end;
/

then you can see the details in the scheduler job views (user_scheduler_jobs etc). see here for information on scehduler jobs.

Related Solutions

Oracle – BEGIN – END block atomic transactions in PL/SQL

Firstly, BEGIN..END are merely syntactic elements, and have nothing to do with transactions.

Secondly, in Oracle all individual DML statements are atomic (i.e. they either succeed in full, or rollback any intermediate changes on the first failure) (unless you use the EXCEPTIONS INTO option, which I won't go into here).

If you wish a group of statements to be treated as a single atomic transaction, you'd do something like this:

BEGIN
  SAVEPOINT start_tran;
  INSERT INTO .... ; -- first DML
  UPDATE .... ; -- second DML
  BEGIN ... END; -- some other work
  UPDATE .... ; -- final DML
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO start_tran;
    RAISE;
END;

That way, any exception will cause the statements in this block to be rolled back, but any statements that were run prior to this block will not be rolled back.

Note that I don't include a COMMIT - usually I prefer the calling process to issue the commit.

It is true that a BEGIN..END block with no exception handler will automatically handle this for you:

BEGIN
  INSERT INTO .... ; -- first DML
  UPDATE .... ; -- second DML
  BEGIN ... END; -- some other work
  UPDATE .... ; -- final DML
END;

If an exception is raised, all the inserts and updates will be rolled back; but as soon as you want to add an exception handler, it won't rollback. So I prefer the explicit method using savepoints.

Apache setenvif doesn’t set env variable if checked against HTTP Header

Format is:

SetEnvIf <headername> <regex> <environment variable name and optionally a value>

This worked for me:

SetEnvIf ACTUAL_CLIENT_IP "^172\.111\.0\.27" dontlog

where ACTUAL_CLIENT_IP is the name of the HTTP header which stores the actual client IP (as opposed to the Remote_Addr field which gets overwritten with our proxy server's IP).

Best Answer

Related Solutions

Oracle – BEGIN – END block atomic transactions in PL/SQL

Apache setenvif doesn’t set env variable if checked against HTTP Header

Related Topic