Toad gives syntax error on CAST operator, when I try to use the format or NLS arguments as varchar2 local parameter/bind variable.
However running the statement or anonym block is successful in Oracle. Db server version is 12.2.0.1.
Please check the following examples.
SELECT :v AS xx,
CAST ('2019-05-01' AS DATE, 'yyyy-mm-dd')
FROM DUAL;
SELECT :v AS xx,
CAST ('2019-05-01' AS DATE, :fmt_opt)
FROM DUAL;
DECLARE
v_d DATE;
BEGIN
SELECT CAST ('2019-05-01 12:03:03' AS DATE, 'yyyy.mm.dd hh24:mi:ss')
INTO v_d
FROM DUAL;
DBMS_OUTPUT.put_line (TO_CHAR (v_d, 'yyyy.mon.dd hh24:mi:ss'));
SELECT CAST ('2019-Máj. -01 12:03:03' AS DATE, 'yyyy.mon.dd hh24:mi:ss', 'NLS_DATE_LANGUAGE = Hungarian')
INTO v_d
FROM DUAL;
DBMS_OUTPUT.put_line (TO_CHAR (v_d, 'yyyy.mon.dd hh24:mi:ss'));
END;
/
DECLARE
v_fmt VARCHAR2 (30) := 'yyyy-mm-dd hh24:mi:ss';
v_fmt_mon VARCHAR2 (30) := 'yyyy-mon-dd hh24:mi:ss';
v_nls VARCHAR2 (300) := 'NLS_DATE_LANGUAGE = Hungarian';
v_d DATE;
BEGIN
SELECT CAST ('2019-Máj. -01 12:03:03' AS DATE, v_fmt_mon, v_nls) INTO v_d FROM DUAL;
DBMS_OUTPUT.put_line (TO_CHAR (v_d, 'yyyy.mm.dd hh24:mi:ss'));
END;
/