Simon Leung
asked on
Error ORA-0001
any one ideas how to slove the error?
execution code:
BEGIN
purchaseAdmin.CheckBelowMinStock;
END;
/
procedure:
procedure CheckBelowMinStock
as
begin
declare
addAmount number;
cursor c is
select a.*, b.supplierID, b.unitCost
from stock a, itemCost b
where a.QtyOnHand< a.MinStockLevel
and a.itemNo=b.itemNo;
begin
for e in c loop
addAmount:= e.MaxStockLevel-e.QtyOnHand;
insert into ToBePurchased values(e.itemNo, e.warehouseID, addAmount, e.supplierID, e.unitCost);
end loop;
end;
end CheckBelowMinStock;
execution code:
BEGIN
purchaseAdmin.CheckBelowMinStock;
END;
/
procedure:
procedure CheckBelowMinStock
as
begin
declare
addAmount number;
cursor c is
select a.*, b.supplierID, b.unitCost
from stock a, itemCost b
where a.QtyOnHand< a.MinStockLevel
and a.itemNo=b.itemNo;
begin
for e in c loop
addAmount:= e.MaxStockLevel-e.QtyOnHand;
insert into ToBePurchased values(e.itemNo, e.warehouseID, addAmount, e.supplierID, e.unitCost);
end loop;
end;
end CheckBelowMinStock;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You apparently have a primary key constraint on TOBEPURCHASED (most likely on ItemNo). If it's possible for an item to be supplied by more than one supplier or from a different warehouse or at a different unit cost, you probably don't want a primary key at all. You may, however, want to be sure the ItemNo to is not null.
wrap your insert like this...
begin
insert into ToBePurchased values(e.itemNo, e.warehouseID, addAmount, e.supplierID, e.unitCost);
exception
when others then null;
end;
this is bandage...
you should know, why you are trying to insert duplicate row into this table...
begin
insert into ToBePurchased values(e.itemNo, e.warehouseID, addAmount, e.supplierID, e.unitCost);
exception
when others then null;
end;
this is bandage...
you should know, why you are trying to insert duplicate row into this table...
This error means that you are trying to insert data that is already in the table according to the unique key PK_TOBEPURCHASED.
You should include some exception handling in your procedure to handle this error and others as well.
https://docs.oracle.com/cd/E11882_01/timesten.112/e21639/exceptions.htm#TTPLS194
https://www.tutorialspoint.com/plsql/plsql_exceptions.htm
Then you need to decide if you want to update the existing row or leave it as is.
That could be done in 1) the exception handling, 2) by having some IF else logic and check if data exists or not, or 3) you change the insert statement into a MERGE statement where you can handle this situation, where the data is either in the table or not, in one single statement.
https://oracle-base.com/articles/9i/merge-statement
https://www.oracletutorial.com/oracle-basics/oracle-merge/
Regards,
Tomas Helgi