Missing Right parenthesis error when using variables-CodePudding

I am new to working with oracle and I am trying to use variables to view the current month from the SYSDATE. But no matter how many combinations I tried I get an error stating that there is a missing right parenthesis.

Would really appreciate your help here, thanks!

DECLARE
    sys_month NUMBER := 0;
BEGIN   
    sys_month := TO_NUMBER(TO_CHAR(SYSDATE,'MON'));
    dbms_output.put_line('Current Month: ' || sys_month);
END;
/

CodePudding user response：

But no matter how many combinations I tried I get an error stating that there is a missing right parenthesis.

Your code is syntactically valid and does not generate a "missing right parenthesis" error. If you are getting that error then it would be from some other code (before or after) as your code:

DECLARE
    sys_month NUMBER := 0;
BEGIN   
    sys_month := TO_NUMBER(TO_CHAR(SYSDATE,'MON'));
    dbms_output.put_line('Current Month: ' || sys_month);
END;
/

Fails with a run-time exception (rather than a compile-time error):

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at line 4

Because you are converting the current date to a string like DEC for the MONth and then trying to convert that string to a number; which fails as it contains alphabetic characters and no digits.

To fix that error, as pointed out by @mathguy, you can use EXTRACT to do it in a single function call:

DECLARE
    sys_month NUMBER := EXTRACT(MONTH FROM SYSDATE);
BEGIN   
    dbms_output.put_line('Current Month: ' || sys_month);
END;
/

Or, if you really did want to convert to a string and then to a number, you can use the MM format model:

DECLARE
    sys_month NUMBER := TO_NUMBER(TO_CHAR(SYSDATE, 'MM'));
BEGIN   
    dbms_output.put_line('Current Month: ' || sys_month);
END;
/

Which both output:

Current Month: 11

db<>fiddle here

CodePudding user response：

I believe you are looking for something like so.

set serveroutput on;
DECLARE
    sys_month varchar2(60) ;
BEGIN   
    sys_month := (TO_CHAR(SYSDATE,'MON'));
    dbms_output.put_line('Current Month: ' || sys_month);
END;
/

This will return Nov.