I've asked this question in the previous post (https://stackoverflow.com/questions/16520216/ora-01843-not-a-valid-month) but its changed since then and i thought it would be better to make it clearer from the start.
I am receiving this error when running a procedure to delete a customer from a customer table.
BEGIN customers.remove_customer('17023'); END;
However when i delete a customer from the customers table a trigger fires which is where i think the problem lies, here is the code for the trigger:
create or replace TRIGGER CUSTOMER_AD BEFORE DELETE ON CUSTOMER REFERENCING OLD AS OLD FOR EACH ROW DECLARE pragma autonomous_transaction; nPlaced_order_count NUMBER; BEGIN SELECT COUNT(*) INTO nPlaced_order_count FROM PLACED_ORDER p WHERE p.FK1_CUSTOMER_ID = :OLD.CUSTOMER_ID; IF nPlaced_order_count > 0 THEN INSERT into previous_customer (customer_id, first_name, last_name, address) VALUES (:old.customer_id, :old.first_name, :old.last_name, :old.address); END IF; END CUSTOMER_AD;
Now when i delete a customer who has not got a record in the placed_order table the record is deleted fine, the error message occurs when trying to delete a customer who has a record in the placed_order table. Thus i think the error occurs in the trigger as the trigger is only inserting values into the previous_customer table if they have had a record in the placed_order table. But i still don't understand why i get the error message i do?
The customer table has no other triggers on it.
The placed order table has no other triggers on it and neither does the previous_employee
The structure for customers table is :
"CUSTOMER_ID" VARCHAR2(40) NOT NULL ENABLE, "FIRST_NAME" VARCHAR2(30), "LAST_NAME" VARCHAR2(30), "ADDRESS" VARCHAR2(30) CONSTRAINT "PK_CUSTOMER" PRIMARY KEY ("CUSTOMER_ID") ENABLE
The structure for placed_order is:
"ORDER_ID" NUMBER(*,0) NOT NULL ENABLE, "ORDER_DATE" VARCHAR2(15), "DELIVERY_DATE" VARCHAR2(15), "FK1_CUSTOMER_ID" VARCHAR2(40) NOT NULL ENABLE, CONSTRAINT "PK_PLACED_ORDER" PRIMARY KEY ("ORDER_ID") ENABLE ALTER TABLE "PLACED_ORDER" ADD CONSTRAINT "FK1_PLACED_ORDER_TO_CUSTOMER" FOREIGN KEY ("FK1_CUSTOMER_ID") REFERENCES "CUSTOMER" ("CUSTOMER_ID") ON DELETE CASCADE ENABLE
and the structure for previous_customer is:
"CUSTOMER_ID" VARCHAR2(40), "FIRST_NAME" VARCHAR2(30), "LAST_NAME" VARCHAR2(30), "ADDRESS" VARCHAR2(30)
Anyone got any idea why I'm getting the error message ORA-01843???
The procedure code is:
PROCEDURE remove_customer (customer_id VARCHAR2) IS ordersCount pls_integer; BEGIN select count(*) into ordersCount from placed_order where fk1_customer_id = remove_customer.customer_id and delivery_date > sysdate; IF ordersCount > 0 THEN DBMS_OUTPUT.PUT_LINE ('Customer currently has a order been delivered and cant be deleted'); ELSE DELETE FROM customer WHERE customer.customer_id = remove_customer.customer_id; total_customers := total_customers - 1; END IF; END;