how can i resolve this pl/sql declare error?

Question

DECLARE
    COUNTING1 NUMBER(1);
BEGIN
    SELECT COUNT(VACATION_REMAINING_COUNT)
    INTO COUNTING1
    FROM VACATION
    WHERE NAME = :P0_VNAME;

    IF COUNTING1 > 0 THEN
        SELECT VACATION_REMAINING_COUNT
        FROM
        (
            SELECT ROW_NUMBER() OVER (ORDER BY CREATED DESC) ROW_ID,
                   V.VACATION_REMAINING_COUNT 
            FROM VACATION V
            WHERE NAME = :P0_VNAME
        )
        WHERE ROW_ID = 1;
    ELSE
        SELECT USER_YEAR_VACATION FROM VA_USER WHERE NAME = :P0_VNAME;
    END IF;
END;

ORA-06550: line 1, column 114: PLS-00103: Encountered the symbol "DECLARE" when expecting one of the following: ( - + case mod new not null continue avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date pipe

I wrote this sql code. but An error has occurred. please help me..

MT0 MT0 · Accepted Answer · 2020-01-08T11:30:34

You are missing the INTO clause for your second and third SELECT statements.

However, I would skip using the first COUNT statement and just try to find the latest row and catch a NO_DATA_FOUND exception if it occurs:

DECLARE
  p_vacation_remaining VACATION.VACATION_REMAINING_COUNT%TYPE;
BEGIN
  BEGIN
    SELECT vacation_count_remaining
    INTO   p_vacation_remaining
    FROM   vacation
    WHERE  name = :P0_VNAME
    ORDER BY created DESC
    FETCH FIRST 1 ROW ONLY;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      SELECT USER_YEAR_VACATION
      INTO   p_vacation_remaining
      FROM   VA_USER
      WHERE NAME = :P0_VNAME;
  END;

  -- Do something with p_vacation_remaining
  DBMS_OUTPUT.PUT_LINE( p_vacation_remaining );
END;
/

how can i resolve this pl/sql declare error?

1 Answers