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 '%*'*
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 '%*'*
* Oracle12c* Oracle PL/SQLOracle Database* oracle syntax* Oracle DBA
Last Comment
Sean Stuber
ASKER
how to use database built in schedular will this replace * with # for every record at the end only at the end
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.
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.
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;
/
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.
ASKER
I must run this under sys user
you should not install anything into the SYS schema
There is no good reason for you to do so.
There is no good reason for you to do so.
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 ;-)
If you have to deal with time zones (time shifting, DST), you should use "to_timestamp_tz" in conjunction with the "start_date" parameter ;-)
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
start_date => TRUNC(NEXT_DAY(SYSDATE, 'Mon')) + 8 / 24 no time zone issue
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
I recommend not using cron for something like this. Just use the database built in scheduler
Open in new window