asked on

Error Execute Package.Procedure in PL/SQL

Hi,
I'm getting tons of errors when doing this execute which call the procedures to insert this new data into the specified tables....
this is linked to the previous post which is the package and package body with the procedure Do1Order have been created....

some of these errors were repeated so i just post once...

the tables:
CREATE TABLE OrderLog (
Order# INTEGER
check (Order# > 1000 and Order# < 10000)
CONSTRAINT Order#_pk_OrderLog PRIMARY KEY,
OrderDateTime DATE,
Cust# INTEGER
check (Cust# > 100 and Cust# < 1000)
CONSTRAINT Customer_fk_OrderLog
REFERENCES Customer(CustomerID),
OrderTotalPrice INTEGER
);

CREATE TABLE ErrorLog (
Order# INTEGER
check (Order# > 1000 and Order# < 10000)
CONSTRAINT Order#_pk PRIMARY KEY,
OrderDateTime DATE,
Cust# INTEGER
check (Cust# > 100 and Cust# < 1000)
CONSTRAINT Customer_fk_ErrorLog
REFERENCES Customer(CustomerID),
Prod# INTEGER
check (Prod# > 10000 and Prod# < 100000)
CONSTRAINT Product_fk_ErrorLog
REFERENCES Product(ProductID),
Quan INTEGER,
ErrorMessage VARCHAR2(50)
);

CREATE TABLE CustInvoice (
Invoice# INTEGER
CONSTRAINT Invoice_pk PRIMARY KEY,
Cust# INTEGER
check (Cust# > 100 and Cust# < 1000)
REFERENCES Customer(CustomerID),
CustName VARCHAR2(25),
Street VARCHAR2(25),
Zip INTEGER
check (Zip > 0 and Zip < 100000)
CONSTRAINT Zip_fk_CustInvoice
REFERENCES Zipcode(Zip)
);

the 3 triggers:
--start "C:\Users\jessica99327\AppData\Roaming\SQL Developer\StartSequences.sql"

CREATE OR REPLACE TRIGGER CheckQuan
AFTER UPDATE OF QuanInStock
ON Product
FOR EACH ROW
BEGIN
IF :old.QuanInStock <= :old.ReorderPoint THEN
INSERT INTO
ReOrder (Prod#, RequestDateTime, NormalQuan, ReorderQuan)
VALUES
(:old.ProductID, SYSDATE, 'T', :old.ReorderQuan);

END IF;

END;
/

show errors

CREATE OR REPLACE TRIGGER DoCustInvoice
AFTER INSERT OR UPDATE ON ProductOrder
FOR EACH ROW
DECLARE
v_action VARCHAR(50);
BEGIN
IF UPDATING THEN
v_action := 'A row has been updated in the ProductOrder table';
END IF;
IF INSERTING THEN
v_action := 'A row has been inserted in the ProductOrder table';
END IF;
INSERT INTO
CustInvoice (Invoice#, Cust#, CustName, Street, Zip)
select InvoiceSEQ.nextval, :new.CustomerID, C.FirstName || C.LastName, C.Street, C.Zip from Customer C where C.CustomerID = :new.CustomerID;
END;
/

show errors

CREATE OR REPLACE TRIGGER DoPo
AFTER INSERT OR UPDATE ON ReOrder
FOR EACH ROW
DECLARE
v_action VARCHAR2(50);
BEGIN
IF UPDATING THEN
v_action := 'A row has been updated in the ReOrder table';
END IF;
IF INSERTING THEN
v_action := 'A row has been inserted in the ReOrder table';
END IF;
INSERT INTO
PurchaseOrder (PONo, ProductID, PODate)
VALUES
(PONoSEQ.nextval, :new.Prod#, :new.RequestDateTime);
END;
/

show errors

SQL> execute OrderProcessingDef.Do1Order(101, 10010, 100, 10014, 1,'CC');
BEGIN OrderProcessingDef.Do1Order(101, 10010, 100, 10014, 1,'CC'); END;

*
ERROR at line 1:
ORA-00001: unique constraint (JESSICAFOO9.INVOICE_PK) violated
ORA-06512: at "JESSICAFOO9.DOCUSTINVOICE", line 10
ORA-04088: error during execution of trigger 'JESSICAFOO9.DOCUSTINVOICE'
ORA-06512: at "JESSICAFOO9.ORDERPROCESSINGDEF", line 11
ORA-06512: at line

SQL> execute OrderProcessingDef.Do1Order(102, 99999, 1, 10014, 1,'FOOD');
BEGIN OrderProcessingDef.Do1Order(102, 99999, 1, 10014, 1,'FOOD'); END;

*
ERROR at line 1:
ORA-02291: integrity constraint (JESSICAFOO9.PRODUCT_FK_ORDERDETAILS) violated
- parent key not found
ORA-06512: at "JESSICAFOO9.ORDERPROCESSINGDEF", line 13
ORA-06512: at line 1

ASKER CERTIFIED SOLUTION