Hi Team,
I am facing issue with below procedure:
CREATE OR REPLACE PROCEDURE CALCULATE_BILL AS
v_customer customer%rowtype;
CURSOR C1 IS
Select * from customer;
units number;
rate number(3,2);
amount number;
surcharge number;
excise number;
net number;
BEGIN
DELETE FROM BILL;
open C1;
Loop
fetch C1 into v_customer;
Select DECODE(v_customer."Customer Type",'A',1,'B',1.25,'C',1.50,'D',1.30) into rate from dual;
Select DECODE(v_customer."Meter Type",'S',5,'T',10) into surcharge from dual;
units:=v_customer."Current Reading"-v_customer."Previous Reading";
amount:=rate*units;
excise:= 0.3*(amount+surcharge);
net:=amount+excise+surcharge;
IF C1%NOTFOUND then
EXIT;
END IF;
Insert into Bill values (v_customer."Meter Number",units,rate,amount,surcharge,excise,net);
END LOOP;
Close C1;
END;
This procedure runs fine but if remove the highlighted if-loop then it is throwing error while execution.
Error report -
ORA-00001: unique constraint (SYS.SYS_C007119) violated
ORA-06512: at "SYS.CALCULATE_BILL", line 28
ORA-06512: at line 1
00001. 00000 - "unique constraint (%s.%s) violated"
*Cause: An UPDATE or INSERT statement attempted to insert a duplicate key.
For Trusted Oracle configured in DBMS MAC mode, you may see
this message if a duplicate entry exists at a different level.
*Action: Either remove the unique restriction or do not insert the key.
Message was edited by: 4669e293-d945-4c2e-8aa2-99d3566752e7