遭遇ORA-01858错误求助：Oracle表插入数据异常问题

Hey there, let's tackle that ORA-01858 error you're stuck on! This error pops up when Oracle tries to parse a date value but runs into a non-numeric character where it expects a number—almost always because the date string you're inserting doesn't match the default date format your database uses.

Why This Happens #

Your EMPLEADOSMM table has two DATE columns: FECHA_NAC (date of birth) and FECHA_ING (hire date). When you insert a date as a plain string (like '01/05/1990'), Oracle relies on its default NLS_DATE_FORMAT setting to interpret it. If your string's format doesn't line up with that default, you get the ORA-01858 error.

Solutions to Fix It #

1. Use TO_DATE() to Explicitly Define the Date Format (Recommended)

The most reliable fix is wrapping your date strings with the TO_DATE() function, which tells Oracle exactly how to parse the date. Here's a corrected version of your insert statement:

INSERT INTO EMPLEADOSMM (
    ID_EMPLEADO,
    NOMBREEM,
    AP_PATEM,
    AP_MATEM,
    FECHA_NAC,
    FECHA_ING,
    ID_CARGO
) VALUES (
    '100006',
    'OSCAR',
    'MARIN',
    'PER',
    TO_DATE('15-03-1985', 'DD-MM-YYYY'), -- Adjust format to match your actual date string
    TO_DATE('01-06-2020', 'DD-MM-YYYY'), -- Match this to your hire date's format
    'C001' -- Example cargo ID
);

Tweak the format model (the second parameter in TO_DATE()) to fit your date string:

• For '1985/03/15', use 'YYYY/MM/DD'
• For 'Mar 15, 1985', use 'MON DD, YYYY'
• For '03-15-1985', use 'MM-DD-YYYY'

2. Temporarily Adjust the Session's Date Format

If you prefer using plain date strings, you can change the NLS_DATE_FORMAT for your current SQL Developer session first:

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-YYYY'; -- Replace with your desired format

Note: This only works for your current session—if you close and reopen SQL Developer, you'll need to run this again. That's why the first solution is better for long-term reliability.

Pro Tip #

Always list column names in your INSERT statements (like in the first example). This avoids errors if the table's column order ever changes, and makes your code way easier to read.

内容的提问来源于stack exchange，提问作者Fernando Wong