I would like to input a date and store it into a variable using PL/SQL but recieve PL-00201 error-CodePudding

I'm trying to input a date i.e. 18-Sep-09 and store it into v_today. But I receive

PLS-00201: identifier 'SEP' must be declared.

SET  SERVEROUTPUT  ON

DECLARE

v_today DATE :=TO_DATE(&today,'YY-MMM-DD');
v_tomorrow v_today%TYPE :=today 1;

BEGIN
DMBS_OUTPUT.PUT_LINE('Today is: '|| v_today);
DMBS_OUTPUT.PUT_LINE('Tomorrow will be: '|| v_tomorrow);
END;

CodePudding user response：

To whoever this helps I've sorted it out

I needed to wrap my input string in single quotes and then use the TO_CHAR function on the output.

SET  SERVEROUTPUT  ON

DECLARE

v_today DATE :='&today';
v_tomorrow v_today%TYPE :=v_today 1;

BEGIN
DBMS_OUTPUT.PUT_LINE('Today is: '|| TO_CHAR(v_today, 'DAY'));
DBMS_OUTPUT.PUT_LINE('Tomorrow will be: '|| TO_CHAR(v_tomorrow,'DAY'));
END;

CodePudding user response：

Although you did it, I'd suggest you not to rely on implicit datatype conversion. '&today' is a string, not a date. Consider doing it as follows, applying to_date (and to_char, as you already did) with appropriate format mask:

SQL> DECLARE
  2    v_today    DATE         := to_date('&today', 'dd.mm.yyyy');
  3    v_tomorrow v_today%TYPE := v_today   1;
  4  BEGIN
  5    DBMS_OUTPUT.PUT_LINE('Today is        : '|| TO_CHAR(v_today, 'DAY'));
  6    DBMS_OUTPUT.PUT_LINE('Tomorrow will be: '|| TO_CHAR(v_tomorrow,'DAY'));
  7  END;
  8  /
Enter value for today: 24.09.2021
Today is        : FRIDAY
Tomorrow will be: SATURDAY

PL/SQL procedure successfully completed.

SQL>