Stored Procedure (rookie programmer), What’s wrong with my code?

I'm trying to create a stored procedure that will update a related row in the BugIt table given an expense report number ErepNo and expense category number EXNo.

The procedure must contain 3 input parameters EXNo ,ErepNo and the 'rUpAm' and with one boolean output parameter aRes.

If the ErepStatus is equal to 'Approved' then the stored value rUpAm is added to BIAct,the output parameter is set to TRUE and the row is updated.

Else the ErepStatus is equal to 'Denied' or 'Pending' an application error is raised and the output parameter is set to FALSE.

Here is what I have tried so far

    CREATE OR REPLACE PROCEDURE StoredProcedure1
(tEXNo IN BugIt.EXNo%TYPE,
 tErepNo IN ER.ErepNo%TYPE,
 rUpAm IN INTEGER, 
 aRes OUT BOOLEAN ) IS
FOUND BOOLEAN := False; 
tErepStatus ER.ErepStatus%TYPE;
tBIAct BugIt.BIAct%TYPE;


CURSOR ERCur(TmpErepNo ExpI.ErepNo%TYPE)is
 SELECT ExpI.EXNo, ER.ErepStatus
    FROM ER, BugIt, ExpI
    WHERE ER.ErepNo = tErepNo
      AND BugIt.EXNo = ExpI.EXNo
      AND ER.ErepNo = ExpI.ErepNo
    ORDER BY ErepStatus DESC;
    
BEGIN 
OPEN ERCur(tErepNo);
LOOP
    FETCH ERCur INTO  tErepStatus, tBIAct;
    EXIT WHEN ERCur%NOTFOUND;
    IF tErepStatus = 'APPROVED' THEN
        FOUND := TRUE;
    END IF;
    IF tErepStatus = 'PENDING' OR tErepStatus = 'DENIED' THEN
        FOUND := False;
        EXIT;
    END IF;
END LOOP;

CLOSE ERCur;
IF FOUND THEN
    tBIAct := tBIAct + rUpAm;
    aRes := TRUE;
    UPDATE BugIt 
    Set BIAct = tBIAct
    WHere EXNo = tEXNo;
          
ElSE 
    tBIAct := tBIAct;
    aRes := False;
END IF;
    
    
    
    
EXCEPTION
WHEN OTHERS THEN aRes := False;
  raise_application_error(-20001, 'Database error');
END;    
/

Here is my test case to implement the code that I have, which keeps coming up with errors(below)

---test--
SET Serveroutput on;

SELECT ExpI.EXNo, ER.ErepStatus
    FROM ER, BugIt, ExpI
    WHERE ER.ErepNo = 21
      AND BugIt.EXNo = ExpI.EXNo
      AND BugIt.ErepNo = ExpI.ErepNo
    ORDER BY ErepStatus DESC;
    
-- Test script
DECLARE
Result BOOLEAN;
aBIAct BugIt.BIAct%TYPE;
BEGIN

StoredProcedure1(2, 2, 3, Result);
IF Result then
    dbms_output.put_line('updated element to the BugIt table');
ELSE
    dbms_output.put_line('Row not updated to the  BugIt table');
END IF;
END;
/


ERROR at line 1:
ORA-20001: Database error
ORA-06512: at "SYSTEM.SPROLLUPEXPITEM", line 51
ORA-06512: at line 6


Read more here: https://stackoverflow.com/questions/64898600/stored-procedure-rookie-programmer-whats-wrong-with-my-code

Content Attribution

This content was originally published by ZenZuluZeeee at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: