How to search for a month that is input by the user

I am working on some homework and have been stuck on this for a week. I have tried using TO_CHAR, MONTH(search), and EXTRACT(MONTH from…) and they all end up with either identifier ‘JAN'(the month I am searching for) is not declared, or expression is of the wrong type. This assignment is to display all the rows for pledges made in a specified month. The column PLEDGEDATE is of type Date in the format ‘dd-mmm-yy’. Any ideas how to make this work?

Declare
Pledges UNIT_2_ASSIGNMENT%ROWTYPE;
SEARCH DATE;

Begin
SEARCH := &M0NTH;
FOR PLEDGES IN
(SELECT IDPLEDGE, IDDONOR, PLEDGEAMT,
CASE 
WHEN PAYMONTHS = 0 THEN 'LUMP SUM'
ELSE'MONHTLY - '||PAYMONTHS
END AS MONTHLY_PAYMENT
FROM UNIT_2_ASSIGNMENT
WHERE TO_CHAR(PLEDGEDATE,'MMM') = 'SEARCH'
ORDER BY PAYMONTHS)

LOOP 
DBMS_OUTPUT.PUT_LINE('Pledge ID: '||UNIT_2_ASSIGNMENT.IDPLEDGE||
' Donor ID: '||UNIT_2_ASSIGNMENT.IDDONOR||
' Pledge Amount: '||TO_CHAR(UNIT_2_ASSIGNMENT.PLEDGEAMT)||
' Lump Sum: '||MONTHLY_PAYMENT);
END LOOP;

END;

Answer

You can use (comments on changes are inline):

DECLARE
  Pledges UNIT_2_ASSIGNMENT%ROWTYPE;
  SEARCH VARCHAR2(3); -- Use VARCHAR2 not DATE data type.
BEGIN
  SEARCH := 'JAN'; -- Replace with your substitution variable.


  FOR PLEDGES IN (
    SELECT IDPLEDGE,
           IDDONOR,
           PLEDGEAMT,
           CASE 
           WHEN PAYMONTHS = 0
           THEN 'LUMP SUM'
           ELSE 'MONHTLY - '||PAYMONTHS
           END AS MONTHLY_PAYMENT
    FROM   UNIT_2_ASSIGNMENT
    WHERE  TO_CHAR(PLEDGEDATE,'MON') = SEARCH -- Unquote variable and use MON not MMM
    ORDER BY PAYMONTHS
  )
  LOOP 
    DBMS_OUTPUT.PUT_LINE(
      'Pledge ID: '||Pledges.IDPLEDGE|| -- Use rowtype variable name not table name.
      ' Donor ID: '||Pledges.IDDONOR||
      ' Pledge Amount: '||TO_CHAR(Pledges.PLEDGEAMT)||
      ' Lump Sum: '||Pledges.MONTHLY_PAYMENT
    );
  END LOOP;
END;
/

Which, for the sample data:

CREATE TABLE unit_2_assignment( idpledge, iddonor, pledgeamt, pledgedate, paymonths ) AS
SELECT LEVEL,
       'Donor' || LEVEL,
       LEVEL * 1000,
       ADD_MONTHS( DATE '2020-01-01', LEVEL - 1 ),
       LEVEL
FROM   DUAL
CONNECT BY LEVEL <= 12;

Outputs:

Pledge ID: 1 Donor ID: Donor1 Pledge Amount: 1000 Lump Sum: MONHTLY - 1