asked on

procedure to run in cron job oracle

hi i need this two query to run as cron job i what a single procedure
update noc_item_encoded_reply set enc_encoded_reply=replace(enc_encoded_reply,'*','#');

update noc_t_mrc
set enc_encoded_reply = rpad(nvl(rtrim(enc_encoded_reply, '*'), '#'), length(enc_encoded_reply), '#')
where enc_encoded_reply like '%*'*

Sean Stuber

something like this?

I recommend not using cron for something like this. Just use the database built in scheduler

CREATE OR REPLACE PROCEDURE update_noc_tables
IS
BEGIN
    UPDATE noc_item_encoded_reply
       SET enc_encoded_reply = REPLACE(enc_encoded_reply, '*', '#');

    UPDATE noc_t_mrc
       SET enc_encoded_reply = RPAD(NVL(RTRIM(enc_encoded_reply, '*'), '#'), LENGTH(enc_encoded_reply), '#')
     WHERE enc_encoded_reply LIKE '%*';

    COMMIT;
END;

Open in new window

chalie001

ASKER

how to use database built in schedular will this replace * with # for every record at the end only at the end

Sean Stuber

Here's an example of a job that will run at 8am every Monday morning.
You can change the schedule to be whatever you want.

BEGIN
    DBMS_SCHEDULER.create_job(job_name          => 'UPDATE_NOC_TABLES_ON_MONDAY',
                              job_type          => 'STORED_PROCEDURE',
                              job_action        => 'UPDATE_NOC_TABLES',
                              start_date        => TRUNC(NEXT_DAY(SYSDATE, 'Mon')) + 8 / 24,
                              repeat_interval   => 'FREQ=weekly;BYDAY=MON;BYHOUR=8;BYMINUTE=0;BYSECOND=0',
                              enabled           => TRUE);
END;
/

Open in new window

The procedure will do whatever your statements do. I didn't change your SQL.
If you want them to do something different, simply change the sql inside the procedure.

chalie001

ASKER

I must run this under sys user

Sean Stuber

you should not install anything into the SYS schema

There is no good reason for you to do so.

Alex [***Alex140181***]

Apart from what Sean already mentioned (which is more than 100% correct), I just want to add following:
If you have to deal with time zones (time shifting, DST), you should use "to_timestamp_tz" in conjunction with the "start_date" parameter ;-)

chalie001

ASKER

how can i chnage this to run every morning around 7 and in ther everning around 18:15
start_date => TRUNC(NEXT_DAY(SYSDATE, 'Mon')) + 8 / 24 no time zone issue

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial