-- удачно:
> alter session set nls_date_format = 'dd-mon-rr'
session SET altered.
> Select to_date(date'2017-06-11', 'DD.MM.YYYY') from dual
TO_DATE(DATE'2017-06-11','DD.MM.YYYY')
--------------------------------------
11-jun-17                              


-- Неудачно:
> alter session set nls_date_format = 'yyyy-mm-dd'
session SET altered.
> Select to_date(date'2017-06-11', 'DD.MM.YYYY') from dual

Error starting at line 5 in command:
Select to_date(date'2017-06-11', 'DD.MM.YYYY') from dual
Error report:
SQL Error: ORA-01861: literal does not match format string

-- Полный звиздец:
> alter session set nls_date_format = 'mm.dd.yyyy'
session SET altered.
> Select to_char(
  to_date(date'2017-06-11', 'DD.MM.YYYY') 
  , 'dd-MON-yyyy')
from dual
TO_CHAR(TO_DATE(DATE'2017-06-11','DD.MM.YYYY'),'DD-MON-YYYY')
-------------------------------------------------------------
06-NOV-2017