Tricky Oracle date type

Some of our sql scripts were inserting varchar2 into date type but it works only on some of the machines, here is why it works only sometime, it's because of nls_date_format

Show session date format

SELECT *
FROM nls_session_parameters
WHERE parameter = 'NLS_DATE_FORMAT';

Alter session data format

If the nls_date_format of the current session matches the date in string, oracle can make the convertion, example below

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

DECLARE
    testdate DATE;
    testdate_str varchar2(8);
BEGIN
    testdate_str := '2023-03-11';
    SELECT testdate_str 
      INTO testdate
      FROM DUAL;
    dbms_output.put_line(testdate);
END;

But if the format is different it will not work.

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR';

DECLARE
    testdate DATE;
    testdate_str varchar2(10);
BEGIN
    testdate_str := '2023-03-11';
    SELECT testdate_str 
      INTO testdate
      FROM DUAL;
    dbms_output.put_line(testdate);
END;