Link to home
Start Free TrialLog in
Avatar of TECH_NET
TECH_NET

asked on

How to schedule a oracle job to update a column using information provided in a view.

I have a table with hold information of employees who left 6 months ago. Now i wish to update the leave dates of these employees with information provided in another table.

ie the SOURCE table is EMPLOYEE_STAGING ( which hold the employee who left 6 months back)
EMPLOYEE_CREDENTIALS table( hold the current info)
here is what i need to perform

UPDATE EMPLOYEE_STAGING  ES
SET  LEAVE_DATE= (SELECT LEAVE_DATE FROM EMPLOYEE_CREDENTIALS  EC WHERE EC.EMP_ID=ES.EMP_ID)
WHERE EC.EMP_ID=ES.EMP_ID

I hope i am able to convey what i am trying to accomplish using the above code although it might be totaling wrong.

I also want to have this job of updating schedueld on a weekly basis.

How can i do so?


Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image

What is ur DB - Oracle or SQL Server? You posted this question in both zones, so m just confirming, though to me it seems to be for Oracle.

UPDATE EMPLOYEE_STAGING  ES
SET  LEAVE_DATE= (SELECT LEAVE_DATE FROM EMPLOYEE_CREDENTIALS  EC WHERE EC.EMP_ID=ES.EMP_ID);

You can put this in a stored procedure and setup a DBMS_JOB to run it periodically.
Avatar of TECH_NET
TECH_NET

ASKER

It is for ORACLE v8.

Also i want to ensure that only records that need to be updated are those found in EMPLOYEE_CREDENTIALS  table. I guess the query would address it.
ie if the EMPLOYEE_STAGING has 20 records and EMPLOYEE_CREDENTIALS has only 2 records, only those 2 employee information would be updated in the EMPLOYEE_STAGING

I am right.
Ohh i see. In that case, use this : -

UPDATE EMPLOYEE_STAGING  ES
SET LEAVE_DATE = (SELECT LEAVE_DATE FROM EMPLOYEE_CREDENTIALS EC WHERE EC.EMP_ID = ES.EMP_ID)
WHERE ES.EMP_ID IN (SELECT EMP_ID FROM EMPLOYEE_CREDENTIALS);
Avatar of sonicefu

create or replace procedure update_proc
is 
begin
   UPDATE EMPLOYEE_STAGING  ES
             SET LEAVE_DATE = (SELECT LEAVE_DATE FROM EMPLOYEE_CREDENTIALS EC 
                       WHERE EC.EMP_ID = ES.EMP_ID)
             WHERE ES.EMP_ID IN (SELECT EMP_ID FROM EMPLOYEE_CREDENTIALS);
end;
/
-----Weekly scheduler
begin 
  dbms_scheduler.create_job('update_job',  job_type=>'plsql_block',   job_action=>'update_proc();',  repeat_interval=>'FREQ=WEEKLY;BYHOUR=0;BYMINUTE=0',  enabled=>true);
end;
/

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The END; qualier was repeated twice above. Removed and re-pasted the code as below : -
CREATE OR REPLACE PROCEDURE Update_ES AS
BEGIN
UPDATE EMPLOYEE_STAGING ES
SET LEAVE_DATE = (SELECT LEAVE_DATE
                  FROM   EMPLOYEE_CREDENTIALS EC
                  WHERE  EC.EMP_ID = ES.EMP_ID)
WHERE ES.EMP_ID IN (SELECT EMP_ID FROM EMPLOYEE_CREDENTIALS);
END Update_ES;
/
 
DECLARE
jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
job => jobno,
what => 'Update_ES();',
next_date => TRUNC(SYSDATE),
interval => 'TRUNC(SYSDATE + 7)'
);
COMMIT;
END;
/

Open in new window

i suggest the attached modified code.

Thanks


CREATE OR REPLACE PROCEDURE Update_ES AS
BEGIN
UPDATE EMPLOYEE_STAGING ES
SET LEAVE_DATE = (SELECT LEAVE_DATE
                  FROM   EMPLOYEE_CREDENTIALS EC
                  WHERE  EC.EMP_ID = ES.EMP_ID)
WHERE ES.EMP_ID IN (SELECT EMP_ID FROM EMPLOYEE_CREDENTIALS);
-- either you should have commit here or from where this is being called otherwise records updated records
-- are locked and will lead to deadlocks.
END Update_ES;
/
 
 
DECLARE
jobno NUMBER;
BEGIN
DBMS_JOB.SUBMIT(
job => jobno,
what => 'begin Update_ES(); commit; end;',  --- this is better to call the proc from a block with commit
next_date => TRUNC(SYSDATE),
interval => 'TRUNC(SYSDATE + 7)'
);
COMMIT;
END;
/

Open in new window