lundi 8 mai 2017

Update Oracle Stored Procedure to Introduce a Check

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