what is the meaning of using alter session set nls_date_format in plsql developer?

Question

I often see this code in the company , in a procedure

begin
 execute immediate 'alter session set nls_date_format = ''yyyy-mm-dd''' ;
 .......

What is the point of that, or under what conditions do we need to use this statement? because in PLSQL we have already set the time format, so this confuses me , i think it's useless！

Answer 1

In Oracle, the NLS_DATE_FORMAT is used as the format model to implicitly cast from date-to-string or string-to-dates.

If you do:

SELECT TO_DATE('27-03-2023') FROM DUAL;

Then that is effectively the same as:

SELECT TO_DATE(
         '27-03-2023',
         (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT')
       )
FROM   DUAL;

and if the NLS_DATE_FORMAT is DD-MM-YYYY (or an equivalent) then the implicit conversion will work but it the NLS_DATE_FORMAT is not equivalent then the query will fail (or give an erroneous result).

Similarly, using:

SELECT TO_CHAR(DATE '2023-03-27') FROM DUAL;

Then that is effectively the same as:

SELECT TO_CHAR(
         DATE '2023-03-27',
         (SELECT value FROM NLS_SESSION_PARAMETERS WHERE parameter = 'NLS_DATE_FORMAT')
       )
FROM   DUAL;

And it will be formatted according to the current value of the NLS_DATE_FORMAT session parameter.

---

What is the point of that, or under what conditions do we need to use this statement? because in PLSQL we have already set the time format

If you are always use:

TO_DATE(string_value, format_model)
TO_CHAR(date_value, format_model)

And explicitly specify the format model in the second parameter then it is pointless.

If you do not always specify the format model and rely on implicit conversions then it is ensuring that your implicit conversions always use the correct format model. However, implicit conversions are considered to be bad practice and it would be better to always specify the format model and remove the dynamic calls to set the NLS_DATE_FORMAT when you next come to update those procedures and functions.