CODEWITHSUNDEEP
oracle-databaseif-statementstored-proceduresplsql
user3481395
user3481395

Reputation: 9

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,

Upvotes: 0

Views: 337

Answers (1)

Pablo Santa Cruz
Pablo Santa Cruz

Reputation: 181280

You are using TransactSQL syntax on a PL/SQL code. That won't work. If you need to add that check, adding using proper PL/SQL syntax.

Something like this:

create or replace PROCEDURE "USP_DISABLELEFTEMPLOYEES"(p_AbortJob varchar2(200))
   cnt as number;
AS
    select count(1) into cnt
      FROM SHOP_EMPLOYEE_IMPORT where varname = p_AbortJob;
    IF cnt > 5000 then
    BEGIN
        UPDATE SHOP_EMPLOYEE
        SET DATE_STARTED = '01 JAN 2099',
            PROCESSED      = 1
        WHERE PROCESSED <> 1;
        COMMIT;
    END;
END;

Also, do not include the commit clause on your procedure.

Upvotes: 1

Related Questions