We help IT Professionals succeed at work.
Get Started

Error Execute Package.Procedure in PL/SQL

crazy4s
crazy4s asked
on
602 Views
Last Modified: 2012-05-11
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
Comment
Watch Question
Top Expert 2011
Commented:
This problem has been solved!
Unlock 3 Answers and 82 Comments.
See Answers
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE