We help IT Professionals succeed at work.

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

1,410 Views
Last Modified: 2013-12-19
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?


Comment
Watch Question

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.

Author

Commented:
It is for ORACLE v8.

Author

Commented:
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);

Commented:

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

This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
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

Naveen KumarProduction Manager / Application Support Manager
CERTIFIED EXPERT

Commented:
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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.