Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE pp_acc(end_date DATE);
CREATE PROCEDURE message(v_msg VARCHAR2)
  IS BEGIN DBMS_OUTPUT.PUT_LINE(v_msg); END;
/
CREATE PROCEDURE pause
  IS BEGIN NULL; END;
/
DECLARE
  p_enquiry_date pp_acc.end_date%type;

  FUNCTION name_in(v_col VARCHAR2) RETURN DATE
    IS BEGIN RETURN SYSDATE; END;
BEGIN
  DBMS_OUTPUT.ENABLE();

  p_enquiry_date  := to_date(
                       (
                         nvl(
                           name_in('PPUN.DSP_RUN_END_DATE'),
                           to_char(sysdate, 'DD-MON-YYYY')
                         )
                       ),
                       'DD-MON-YYYY'
                     );
  message('enq: '||p_enquiry_date);
  pause;
END;
/
ORA-01861: literal does not match format string
ORA-06512: at line 9

DECLARE
  p_enquiry_date pp_acc.end_date%type;

  FUNCTION name_in(v_col VARCHAR2) RETURN VARCHAR2
    IS BEGIN RETURN TO_CHAR(SYSDATE, 'DD-MON-YYYY'); END;
BEGIN
  DBMS_OUTPUT.ENABLE();

  p_enquiry_date  := to_date(
                       (
                         nvl(
                           name_in('PPUN.DSP_RUN_END_DATE'),
                           to_char(sysdate, 'DD-MON-YYYY')
                         )
                       ),
                       'DD-MON-YYYY'
                     );
  message('enq: '||p_enquiry_date);
  pause;
END;
/
1 rows affected

dbms_output:
enq: 2023-10-17 00:00:00

DECLARE
  p_enquiry_date VARCHAR2(50);

  FUNCTION name_in(v_col VARCHAR2) RETURN VARCHAR2
    IS BEGIN RETURN SYSDATE; END;
BEGIN
  DBMS_OUTPUT.ENABLE();

  p_enquiry_date  := nvl(name_in('PPUN.DSP_RUN_END_DATE'), to_char(sysdate));
  message('enq: '||p_enquiry_date);
  pause;
END;
/
1 rows affected

dbms_output:
enq: 2023-10-17 09:38:16

DECLARE
  p_enquiry_date pp_acc.end_date%type;

  FUNCTION name_in(v_col VARCHAR2) RETURN VARCHAR2
    IS BEGIN RETURN SYSDATE; END;
BEGIN
  DBMS_OUTPUT.ENABLE();

  p_enquiry_date  := nvl(TO_DATE(name_in('PPUN.DSP_RUN_END_DATE')), sysdate);
  message('enq: '||TO_CHAR(p_enquiry_date, 'DD-MON-YYYY'));
  pause;
END;
/
1 rows affected

dbms_output:
enq: 17-OCT-2023

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY';
DECLARE
  p_enquiry_date pp_acc.end_date%type;

  FUNCTION name_in(v_col VARCHAR2) RETURN DATE
    IS BEGIN RETURN SYSDATE; END;
BEGIN
  DBMS_OUTPUT.ENABLE();

  p_enquiry_date  := to_date(
                       (
                         nvl(
                           name_in('PPUN.DSP_RUN_END_DATE'),
                           to_char(sysdate, 'DD-MON-YYYY')
                         )
                       ),
                       'DD-MON-YYYY'
                     );
  message('enq: '||p_enquiry_date);
  pause;
END;
/
1 rows affected

dbms_output:
enq: 17-OCT-2023