We have a problem with the below stored procedure in oracle, it marks employees who have left the company as having started in 2099.
create or replace PROCEDURE "USP_DISABLELEFTEMPLOYEES"
AS
BEGIN
UPDATE SHOP_EMPLOYEE
SET DATE_STARTED = '01 JAN 2099',
PROCESSED = 1
WHERE PROCESSED <> 1;
COMMIT;
END;
Long story short, it marked all of our employees as left the company as the initial file sent from the sftp server was empty. I'm new to oracle and pl-sql so I wanted to introduce a check before the stored procedure runs.
The data gets loaded into SHOP_EMPLOYEE_IMPORT first, so what I wanted to do is run a check if this table contains less than 5000 records the stored procedure wont run.
create or replace PROCEDURE "USP_DISABLELEFTEMPLOYEES"
AS
**SELECT @var = AbortJob FROM SHOP_EMPLOYEE_IMPORT where varname = @AbortJob
IF (@@ROWCOUNT > 5000)**
BEGIN
UPDATE SHOP_EMPLOYEE
SET DATE_STARTED = '01 JAN 2099',
PROCESSED = 1
WHERE PROCESSED <> 1;
COMMIT;
END;
However as you can image this won't work. Can you help me get this working,
Aucun commentaire:
Enregistrer un commentaire