Ora 01861 ошибка

I am trying to insert data into an existing table and keep receiving an error.

INSERT INTO Patient  
(
  PatientNo,
  PatientFirstName,
  PatientLastName,
  PatientStreetAddress,
  PatientTown,
  PatientCounty,
  PatientPostcode,
  DOB,
  Gender,
  PatientHomeTelephoneNumber,
  PatientMobileTelephoneNumber
)
VALUES 
(
  121, 
  'Miles', 
  'Malone', 
  '64 Zoo Lane', 
  'Clapham', 
  'United Kingdom',
  'SW4 9LP',
  '1989-12-09',
  'M',
  02086950291,
  07498635200
);

Error:

Error starting at line : 1 in command -
INSERT INTO Patient (PatientNo,PatientFirstName,PatientLastName,PatientStreetAddress,PatientTown,PatientCounty,PatientPostcode,DOB,Gender,PatientHomeTelephoneNumber,PatientMobileTelephoneNumber)
VALUES (121, 'Miles', 'Malone', '64 Zoo Lane', 'Clapham', 'United Kingdom','SW4 9LP','1989-12-09','M',02086950291,07498635200)
Error report -
SQL Error: ORA-01861: literal does not match format string
01861. 00000 -  "literal does not match format string"
*Cause:    Literals in the input must be the same length as literals in
           the format string (with the exception of leading whitespace).  If the
           "FX" modifier has been toggled on, the literal must match exactly,
           with no extra whitespace.
*Action:   Correct the format string to match the literal.

Just not sure why this keeps happening I am learning SQL at the moment, any help will be greatly appreciated!

I am trying to insert data into an existing table and keep receiving an error.

INSERT INTO Patient  
(
  PatientNo,
  PatientFirstName,
  PatientLastName,
  PatientStreetAddress,
  PatientTown,
  PatientCounty,
  PatientPostcode,
  DOB,
  Gender,
  PatientHomeTelephoneNumber,
  PatientMobileTelephoneNumber
)
VALUES 
(
  121, 
  'Miles', 
  'Malone', 
  '64 Zoo Lane', 
  'Clapham', 
  'United Kingdom',
  'SW4 9LP',
  '1989-12-09',
  'M',
  02086950291,
  07498635200
);

Error:

Error starting at line : 1 in command -
INSERT INTO Patient (PatientNo,PatientFirstName,PatientLastName,PatientStreetAddress,PatientTown,PatientCounty,PatientPostcode,DOB,Gender,PatientHomeTelephoneNumber,PatientMobileTelephoneNumber)
VALUES (121, 'Miles', 'Malone', '64 Zoo Lane', 'Clapham', 'United Kingdom','SW4 9LP','1989-12-09','M',02086950291,07498635200)
Error report -
SQL Error: ORA-01861: literal does not match format string
01861. 00000 -  "literal does not match format string"
*Cause:    Literals in the input must be the same length as literals in
           the format string (with the exception of leading whitespace).  If the
           "FX" modifier has been toggled on, the literal must match exactly,
           with no extra whitespace.
*Action:   Correct the format string to match the literal.

Just not sure why this keeps happening I am learning SQL at the moment, any help will be greatly appreciated!

I keep getting this error in Oracle when i try to run this statement. I am not sure where the formatting error is coming from. maybe someone with fresh eyes can assist me with this problem.

INSERT INTO Faculty
(FacNo, FacFirstName, FacLastName, FacCity, FacState,
 FacDept, FacRank, FacSalary, FacSupervisor, FacHireDate, FacZipCode)
 VALUES ('543-21-0987','VICTORIA','EMMANUEL','BOTHELL','WA','MS','PROF',120000.0,'','2001-04-15','98011-2242');

Here is the error message i keep getting:

Error starting at line : 1 in command — Error report —
SQL Error: ORA-01861: literal does not match format string
01861. 00000 — «literal does not match format string»
*Cause: Literals in the input must be the same length as literals in
the format string (with the exception of leading whitespace). If the
«FX» modifier has been toggled on, the literal must match exactly,
with no extra whitespace.
*Action: Correct the format string to match the literal.

Here are the specs on the table i am trying to INSERT this data into:

FACNO CHAR(11 BYTE)
FACFIRSTNAME VARCHAR2(30 BYTE)
FACLASTNAME VARCHAR2(30 BYTE)
FACCITY VARCHAR2(30 BYTE)
FACSTATE CHAR(2 BYTE)
FACZIPCODE CHAR(10 BYTE)
FACRANK CHAR(4 BYTE)
FACHIREDATE DATE
FACSALARY NUMBER(10,2)
FACSUPERVISOR CHAR(11 BYTE)
FACDEPT CHAR(6 BYTE)

ORA-01861 means that the date format between two operands cannot be comparable. Usually, it happens when comparing DATE with CHAR. Therefore, we should make them match with each other in order to avoid ORA-01861.

In this post, you will see some error patterns of ORA-01861 and their respective solutions.

The following statement looks like no problem.

SQL> conn hr/hr
Connected.
SQL> set heading off;
SQL> select count(*) || ' Persons' from employees where hire_date > '2008-03-01';
select count(*) || ' Persons' from employees where hire_date > '2008-03-01'
                                                               *
ERROR at line 1:
ORA-01861: literal does not match format string

But it threw ORA-01861 eventually. Let’s try to add TO_DATE function to convert the string into DATE value.

SQL> select count(*) || ' Persons' from employees where hire_date > to_date('2008-03-01');
select count(*) || ' Persons' from employees where hire_date > to_date('2008-03-01')
                                                                       *
ERROR at line 1:
ORA-01861: literal does not match format string

Even though they should have been comparable, their different formats stopped doing this. That is, the root cause is datetime format mismatch, not data type mismatch.

Here we talked two error patterns of ORA-01861 in this post:

1. Date format mismatch issues.
2. JDBC driver’s problem specific for ORA-01861.

Date Format Mismatch ORA-01861

Converting the date string into a DATE is not working. There still have format mismatching problem. Now, let’s see what date format does the database accept?

SQL> select value from v$nls_parameters where parameter = 'NLS_DATE_LANGUAGE';

AMERICAN

SQL> select value from v$nls_parameters where parameter = 'NLS_DATE_FORMAT';

DD-MON-RR

There’re 5 ways that can solve ORA-01861 and make formats between date and string match each other.

1. Conform to NLS_DATE_FORMAT
2. Use TO_DATE
3. Use TO_CHAR
4. Change NLS_DATE_FORMAT at Session-Time
5. Set NLS_LANG

Conform to NLS_DATE_FORMAT

As we can see, our date string ‘2008-03-01’ does not match the current date format ‘DD-MON-RR’. Let’s conform to the current date format by converting the date string from ‘2008-03-01′ into ’01-MAR-08’.

SQL> select count(*) || ' Persons' from employees where hire_date > '01-MAR-08';

4 Persons

Please note that, you don’t have to use TO_DATE function to convert the string into a date value, an implicit conversion will be processed.

Use TO_DATE

The statement now is working, but sometimes you may still want to use the original date string. You can format the date string by TO_DATE function.

SQL> select count(*) || ' Persons' from employees where hire_date > to_date('2008-03-01', 'YYYY-MM-DD');

4 Persons

Use TO_CHAR

On the other hand, you can also convert DATE into string by TO_CHAR in order to compare the date string.

SQL> select count(*) || ' Persons' from employees where to_char(hire_date, 'YYYY-MM-DD') > '2008-03-01';

4 Persons

You might have some performance issue by applying this solution if the table is really big. A function-based index might be required for the computed values. In this case, it’s TO_CHAR(HIRE_DATE, ‘YYYY-MM-DD’).

Change NLS_DATE_FORMAT at Session-Time

If you don’t want to modify your statement, not even a tiny bit, you can set NLS_DATE_FORMAT at session-level to align with your date string format.

SQL> alter session set nls_date_format = 'YYYY-MM-DD';

Session altered.

SQL> select count(*) || ' Persons' from employees where hire_date > '2008-03-01';

4 Persons

SQL> exit

Set NLS_LANG

Here comes a more advanced topic about NLS_DATE_FORMAT. Sometimes, you can not change the application that you’re using in your environment, not even the SQL statements inside.

So every time you run it, there’re many ORA-01861 complained about the date format. This is because the format of date strings used by the application does not match NLS settings in your environment.

In such moment, the only thing you can do is set environment variable NLS_LANG or NLS_DATE_FORMAT at OS level to make date format of every session running on the platform comply with the application so as to prevent ORA-01861.

Use Only NLS_LANG

In our case, the format of our date string ‘2008-03-01’ is ‘YYYY-MM-DD’, so what should we set in NLS_LANG? According to NLS_TERRITORY to NLS_DATE_FORMAT Mapping Table, there’s only few territories use ‘YYYY-MM-DD’ (or ‘RRRR-MM-DD’), one of which is SWEDEN.

In the following setting, we set NLS_LANG as SWEDISH language which subsequently changed NLS_TERRITORY into SWEDEN.

[oracle@test ~]$ export NLS_LANG=Swedish

Please note that, NLS_DATE_FORMAT is literally derived from NLS_TERRITORY, not from NLS_DATE_LANGUAGE.

[oracle@test ~]$ sqlplus /nolog
...
SQL> conn hr/hr
Connected.

It connected without ORA-12705 which means that Oracle database accepted the value of NLS_LANG.

SQL> set heading off;
SQL> select value from v$nls_parameters where parameter = 'NLS_TERRITORY';

SWEDEN

SQL> select value from v$nls_parameters where parameter = 'NLS_DATE_LANGUAGE';

SWEDISH

SQL> select value from v$nls_parameters where parameter = 'NLS_DATE_FORMAT';

YYYY-MM-DD

SQL> select count(*) || ' Persons' from employees where hire_date > '2008-03-01';

4 Persons

As you can see, NLS settings of the session follows NLS_LANG. The best thing is that we don’t need to modify the statement, we just use NLS_LANG to align with its format string.

For windows platform, you can set NLS_LANG like the followings, it also works.

C:\Users\Administrator>set NLS_LANG=Swedish

C:\Users\Administrator>echo %NLS_LANG%
Swedish

Although NLS_LANG has a fixed format including language, territory and character set that I have talked about it in another post, it can accept only languages or territory.

Use Both NLS_LANG and NLS_DATE_FORMAT

If you also set NLS_DATE_FORMAT in OS shell, it overrides the datetime format derived from NLS_LANG. You may follow the link to know it.

JDBC Driver Problem ORA-01861

Here comes the most advanced topic about ORA-01861 in this post. By default, Java uses the locale of OS as NLS settings, therefore NLS_LANG environment variable does not affect Oracle JDBC drivers in connecting to Oracle databases.

Changing the locale of OS maybe a solution to ORA-01861, but seriously, it would be a big issue to other applications or other users. A lower-cost solution is to add another environment variable called JAVA_TOOL_OPTIONS.

There’re 2 ways to set JAVA_TOOL_OPTIONS so as to solve ORA-01861 problem from JDBC.

1. Run-time Setting
2. Permanent Setting

Run-time Setting

C:\Users\Administrator>set JAVA_TOOL_OPTIONS=-Duser.language=en -Duser.country=US

C:\Users\Administrator>echo %JAVA_TOOL_OPTIONS%
-Duser.language=en -Duser.country=US

Permanent Setting

Oracle JDBC driver will pick up JAVA_TOOL_OPTIONS Environment Variable and follow the instructions in it. That’s how we solve ORA-01861 for Oracle JDBC driver.

Further reading: How to Set NLS_DATE_FORMAT in RMAN

Ora 01861 literal does not match format string error is an issue you may face when working with Oracle database. This error occurs when a string literal in a SQL statement is used with a date or number format, but the format of the literal does not match the format string specified in the statement.

In this article, you will explore the various causes of this error and practical solutions for resolving it. Whether you’re a seasoned Oracle database administrator or just starting, this article will provide valuable insights and tips for avoiding and solving this error in your database operations.

Why Am I Encountering the Ora 01861 Literal Error?

You are encountering the ora 01861 literal does not match format string error because you are using a string literal in a SQL statement with a date or number format, but the format of the literal does not match the format string specified in the statement.

Simply put, the error arises from a mismatch between the date format specified in the SQL statement and the format of the date literal being used. In Oracle databases, dates must be specified in a specific format, such as dd-mon-yy or yyyy-mm-dd. If the format of the date literal does not match the format specified in the SQL statement, this error will be generated.

For example, consider the following SQL statement:

SELECT * FROM orders WHERE order_date = ’06-FEB-23′;

In this case, the date literal ’06-FEB-23′ is being used with the format “dd-mon-yy”. However, if the actual format of the date in the database is “yyyy-mm-dd”, it will trigger this error. It means converting the date string into a DATE is not working.

– Scenarios That Can Trigger This Error in Oracle Database

As you use Oracle databases, you can trigger this error in various scenarios. Below is a quick look at some of them:

• ora-01861 literal does not match format string in java: This error occurs when working with Java and an Oracle database. The error arises when a string literal, typically representing a date or timestamp value, does not match the format expected by the Oracle database.
• ora-01861: literal does not match format string sql loader: The error arises during an SQLLoader operation. It is triggered when the data in the input file being loaded into the database does not match the format specified in the control file for SQLLoader.
• impdp ora-01861: literal does not match format string: This error occurs when using the Data Pump Import (IMPDP) utility to import data into an Oracle database. The error occurs specifically during an import operation.
• ora-01861 literal does not match format string to_date: This error occurs when a string literal being used in the TO_DATE function does not match the format specified in the second argument of the TO_DATE function.

You can solve the ora 01861 literal does not match format string error by conforming to the NLS_DATE_FORMAT. NLS_DATE_FORMAT is an Oracle system variable that defines the default date format for the database. This ensures that the format of the date literal matches the expected format.



Thereby avoiding the ora-01861 error in oracle. To use NLS_DATE_FORMAT, you can add the following line of code at the beginning of your SQL statement:

ALTER SESSION SET NLS_DATE_FORMAT = ‘dd-mon-yy’;

This will set the date format for the current session to “dd-mon-yy”. You can then use the date literal in your SQL statement without encountering the ora 01861 error, as long as the format of the date literal matches the format specified by NLS_DATE_FORMAT.

Note that NLS_DATE_FORMAT only affects the current session. If you want to make a permanent change to the default date format for your database, you will need to set the NLS_DATE_FORMAT in the initialization file for your database.

By conforming to NLS_DATE_FORMAT, you can simplify your SQL statements and ensure consistency in your date formatting, which can help avoid the ora 01861 error. Here is a complete example of how how to resolve ora-01861 literal does not match format string error using this approach:

– Avoiding the “Ora 01861” Error With the TO_DATE Function

You can also solve the error by using the TO_DATE function in Oracle databases. The TO_DATE function allows you to convert a string literal into a date value with the format specified in the second argument.

By using this function, you ensure that the format of the date literal matches the expected format, thereby avoiding the ora 01861 error.

Here’s an example of how you can make use of the TO_DATE function to solve the ora-01861 date format error:

SELECT * FROM orders WHERE order_date = TO_DATE(’06-FEB-23′, ‘dd-mon-yy’);

In this example, the date literal ’06-FEB-23′ is being converted to a date value using the TO_DATE function, with the format “dd-mon-yy” specified in the second argument.

This ensures that the format of the date literal matches the expected format, even if the format of the date in the database is different.

It is important to note that the TO_DATE function must be used consistently throughout the SQL statement and the database to avoid the ora 01861 error or other similar errors. By using the TO_DATE function, you can ensure that the format of the date literal matches the expected format, making it easier to avoid the “ora 01861” error.

– Using the TO_CHAR Function for Error-free Date Formatting

Another way you can solve this error is by using the TO_CHAR function in Oracle databases. The TO_CHAR function allows you to convert a date value into a string literal, with the format specified in the second argument.

By using this function, you can ensure that the format of the date value matches the expected format, thereby avoiding the ora 01861 error.

Here’s an example of using the TO_CHAR function to solve the ora 01861 error:

SELECT * FROM orders WHERE order_date = TO_CHAR(purchase_date, ‘dd-mon-yy’);

In this example, the date value is converted to a string literal using the TO_CHAR function, with the format specified in the second argument. This ensures that the format of the date value matches the expected format, even if the format of the date in the database is different.

However, using this approach can result in performance issues, especially when working with a very big table. You may require a function-based index for the output. In this example, it is TO_CHAR(purchase_date, ‘dd-mon-yy’).

– Bid Farewell to the “Ora 01861” Error With NLS_LANG

You can also solve the error by setting NLS_LANG. This is an advanced use of the NLS_DATE_FORMAT. In some cases, you are unable to alter the application that you are using. This applies even to the SQL statements.

As such, you may trigger this error several times because the date strings format you use in your application do not match the NLS configuration in your location.

Thus, to prevent the error, the only need you need to do is to set the environment variable NLS_LANG to ensure the date format of each session on the platform complies with the application.

Suppose you are using the date string that looks like this ‘2020-08-21’ which of the format ‘YYYY-MM-DD’. The question is, what should you set in NLS_LANG?

Going by the NLS_TERRITORY to NLS_DATE_FORMAT Mapping Table, only a handful of territories rely on the ‘YYYY-MM-DD’ (or ‘RRRR-MM-DD’). A good example is Sweden.

To change the NLS_LANG in Sweden, you need to set it to SWEDISH language, which will subsequently change the NLS_TERRITORY to SWEDEN. Here is how you can solve this ora-01861: literal does not match format string oracle error using NLS_LANG:

It is worth noting that the NLS_DATE_FORMAT stems from NLS_TERRITORY. The NLS_Territory value within the NLS_LANG environment variable determines the default date format, currency symbol, and other regional settings for the database.

By changing the NLS_Territory value to match the expected format of the date literals in your SQL statements, you can resolve the ora 01861 error. The good thing about using NLS_LANG is that you do not have to change the statement.

Here is an example:

SELECT * FROM orders WHERE order_date > ‘2022-04-22’;

Conclusion

In this post, we shared everything about the triggers of this error and how you can solve them. Here is a quick summary of what you have learned that will help you fix the error effortlessly:

• This error in the Oracle database arises from a date format mismatch.
• To resolve this error, one of the solutions entails conforming to the NLS_DATE_FORMAT.
• Using the TO_DATE or TO_CHAR functions in SQL statements is another way to solve this error.
• Also, setting the NLS_LANG environment variable to change the NLS_TERRITORY setting can solve this error.

By understanding the causes and solutions to this error, you can tackle it confidently and get back to what you do best.