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;
Best Answer
DELIVERY_DATE
isVARCHAR2(15)
. Your procedure includes:... which will cause the
delivery_date
values in the table to be implicitly converted todate
type, based on the NLS_DATE_FORMAT for the session. If you have a 'date' in there which is not in that format then you will get this error. For example if your NLS_DATE_FORMAT ismm/dd/yyyy
as you said in the previous question and you've previous stored a date in the table asdd/mm/yyyy
, e.g. '13/05/2013', then it will get this error from the implicit conversion.It might be somewhat intermittent; if there is no data in
placed_order
for the customer then there will be no dates to (attempt to) convert.So it's nothing to do with the trigger. The trigger seems to be pointless anyway; why aren't you just inserting into
previous_customer
in this procedure? You can just do this:... and drop and forget about the trigger.
You should not store dates in text fields, for exactly this reason. If you can't change the
placed_order
field to aDATE
type - and you really should - you have to assume that the data in their is in a standard format, and it just doesn't match yours in that case you can (as Bob said) force the conversion on your own terms:... where `dd/mm/yyyy' is the date format that's been used to populate the text field. If it has a time component, and I guess it might from the length, then include that in the mask too:
... although obviously that's now too long for a
varchar2(15)
field, so who knows what you have in there.Do you really mean
> sysdate
though? I it populated when at item is actually delivered, or does it show expected delivery date? The error is odd and odesn't make it clear if you only can't delete if there are not-yet-delivered orders, or there have been any delivered orders, in which casedelivery_date is not null
might suffice anyway.