Sql – Why am i getting ORA-01843: not a valid month error

databaseoracleplsqlsqltriggers

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 Solution

DELIVERY_DATE is VARCHAR2(15). Your procedure includes:

and delivery_date > sysdate;

... which will cause the delivery_date values in the table to be implicitly converted to date 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 is mm/dd/yyyy as you said in the previous question and you've previous stored a date in the table as dd/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:

ELSE
    INSERT INTO previous_customer(customer_id, first_name, last_name, address)
    SELECT customer_id, first_name, last_name, address)
    FROM customer
    WHERE customer_id = remove_customer.customer_id;

    DELETE FROM customer
    WHERE customer.customer_id = remove_customer.customer_id;

    total_customers := total_customers - 1;
END IF;

... 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 a DATE 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:

and to_date(delivery_date, 'dd/mm/yyyy') > sysdate

... 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:

and to_date(delivery_date, 'dd/mm/yyyy HH24:MI:SS') > sysdate

... 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 case delivery_date is not null might suffice anyway.

Related Question