ORA-01861: literal does not match format string?

Question

I have this trigger

CREATE OR REPLACE TRIGGER TRIGGER_MAYOR
BEFORE INSERT OR UPDATE ON VENTAS_MENSUALES
FOR EACH ROW

DECLARE 

V_PRODUCTO VARCHAR2(30);
V_FECHA DATE;
V_USUARIO VARCHAR2(50);

BEGIN
 SELECT DESCRIPCION INTO V_PRODUCTO FROM PRODUCTO;
 SELECT SYSDATE INTO V_FECHA FROM DUAL;
 SELECT USER INTO V_USUARIO FROM DUAL;
 INSERT INTO VENTAS_MENSUALES(PRODUCTO,FECHA,USUARIO) VALUES (V_PRODUCTO,' '||V_FECHA,V_USUARIO);
END;

I compile and i don't get errors but when i'm going to test by inserting values i get this

INSERT INTO VENTAS_MENSUALES(PRODUCTO,FECHA,USUARIO) VALUES ('testing','2019-05-02',user);

ERROR

ORA-01861: literal does not match format string

This is my table and columns ... I'm just trying to insert values there to check if it's works or not ! producto can be anything , fecha is supposed to be the sysdate and usuario is supposed to be the user who is doing the query (in this case PRUEBA2)

What is the default date format for your Oracle database? In other words, what is the value of NLS_DATE_FORMAT? What is displayed when you run the following query: select sysdate from dual ? — Abra
What is the point of the look-up on the producto table? If you're passing in a product value as part of your insert, why do you then need to refer to that table, and how are they linked? — Alex Poole

Alex Poole Alex Poole · Accepted Answer · 2020-07-06T17:46:08

The error doesn't have anything to do with the trigger; the insert is erroring before it gets as far as firing that.

'2019-05-02' is a string, not a date. You should use a date literal like date '2019-05-02', or to_date().

INSERT INTO VENTAS_MENSUALES(PRODUCTO,FECHA,USUARIO)
VALUES ('testing', date '2019-05-02', user);

or if you prefer:

INSERT INTO VENTAS_MENSUALES(PRODUCTO,FECHA,USUARIO)
VALUES ('testing', to_date('2019-05-02', 'YYYY-MM-DD'), user);

but unless you need a non-midnight time, that's just more typing.

Your trigger does have some issues though. Sticking with dates, in this part:

INSERT INTO VENTAS_MENSUALES(PRODUCTO,FECHA,USUARIO)
VALUES (V_PRODUCTO,' '||V_FECHA,V_USUARIO);

The ' '||V_FECHA will implicitly convert the date value to a string using the current session's NLS settings, then prepend a space, then implicitly convert it back to a date. The only reason I can imagine to want to do that is to strip off the time portion, but that relies on NLS, which is never safe; and can be much more easily achieved with trunc(V_FECHA). If that is what you want; if this is for auditing/history then you probably want the full time to be preserved.

Then, SELECT DESCRIPCION INTO V_PRODUCTO FROM PRODUCTO; will try to return all rows from the PRODUCTO table into a single string value. If the table only has one row that will work, but that seems unlikely. If the table is empty you'll get a no-data-found error. If it has more than one row you'll get too-many-rows ("ORA-01422: exact fetch returns more than requested number of rows").

Possibly you intended to look up a single product, but if so it isn't clear how you would identify it. Perhaps the table has another column you haven't shown, and you meant to use the :new pseudorecord.

Both of these queries:

SELECT SYSDATE INTO V_FECHA FROM DUAL;
SELECT USER INTO V_USUARIO FROM DUAL;

can be done with assignments:

V_FECHA := SYSDATE;
V_USUARIO := USER;

though you don't need the variables at all as you can refer to them directly in the insert:

INSERT INTO VENTAS_MENSUALES(PRODUCTO,FECHA,USUARIO)
VALUES (V_PRODUCTO, SYSDATE, USER);

But there is the biggest problem - you are trying to insert into the same table the trigger is against, which will either throw a mutating-table error or, more likely, loop until Oracle kills it.

I suspect you're actually trying to modify the row that is being updated, with the product info from a look-up (though that implies denormalised data?) and the user taking the action. Your initial, erroring, insert statement is only referring to those same rows though, so it's not clear how it should be corrected.

You probably want something more like:

CREATE OR REPLACE TRIGGER TRIGGER_MAYOR
BEFORE INSERT OR UPDATE ON VENTAS_MENSUALES
FOR EACH ROW

BEGIN

  SELECT DESCRIPCION INTO :new.PRODUCTO FROM PRODUCTO
  WHERE some_column = :new.some_column;

  :new.FECHA := SYSDATE;
  :new.USUARIO := USER;

END;

If you include the table definition (DDL) and a more realistic insert statement, it may become clearer.

ORA-01861: literal does not match format string?

1 Answers