Is it possible to call a record type inside a cursor?

Question

I am trying to call a record type inside my cursor and although it doesn't give me syntax errors, it still can't compile. My task is to create a cursor that allows retrieving data and handling multiple combinations of donor type and pledge amount as a collection input. The output result should be the donor name and the pledge amount for each pledge that matches the donor type and is greater than the pledge amount indicated. Here are the tables:

DD_DONOR

Name      Null?    Type         
--------- -------- ------------ 
IDDONOR   NOT NULL NUMBER(4)    
FIRSTNAME          VARCHAR2(15) 
LASTNAME           VARCHAR2(30) 
TYPECODE           CHAR(1)      
STREET             VARCHAR2(40) 
CITY               VARCHAR2(20) 
STATE              CHAR(2)      
ZIP                VARCHAR2(9)  
PHONE              VARCHAR2(10) 
FAX                VARCHAR2(10) 
EMAIL              VARCHAR2(25) 
NEWS               CHAR(1)      
DTENTERED          DATE     

DD_PLEDGE

Name        Null?    Type        
----------- -------- ----------- 
IDPLEDGE    NOT NULL NUMBER(5)   
IDDONOR              NUMBER(4)   
PLEDGEDATE           DATE        
PLEDGEAMT            NUMBER(8,2) 
IDPROJ               NUMBER(5)   
IDSTATUS             NUMBER(2)   
WRITEOFF             NUMBER(8,2) 
PAYMONTHS            NUMBER(3)   
CAMPAIGN             NUMBER(4)   
FIRSTPLEDGE          CHAR(1)     

My code:

DECLARE
   CURSOR donnor_pledge (dtype_amount RECORD) IS
     SELECT d.FIRSTNAME, d.LASTNAME, p.PLEDGEAMT
       FROM DD_DONOR d INNER JOIN DD_PLEDGE p
         USING (IDDONOR)
       WHERE d.TYPECODE = dtype_amount.type AND 
       p.PLEDGEAMT > dtype_amount.amount;
       
   TYPE dtype_amount IS RECORD (
     type DD_DONOR.TYPECODE%TYPE := 'I',
     amount DD_PLEDGE.PLEDGEAMT%TYPE) := 250);
     
BEGIN
   OPEN donnor_pledge;
   DBMS_OUTPUT.PUT_LINE('Donor name: ' || d.FIRSTNAME || ' ' || d.LASTNAME);
   DBMS_OUTPUT.PUT_LINE('Pledge amount: ' || p.PLEDGEAMT);
   CLOSE donnor_pledge;    
END;

The error message:

Error report -
ORA-06550: line 2, column 39:
PLS-00201: identifier 'RECORD' must be declared
ORA-06550: line 2, column 4:
PL/SQL: Item ignored
ORA-06550: line 14, column 9:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 14, column 4:
PL/SQL: SQL Statement ignored
ORA-06550: line 15, column 45:
PLS-00201: identifier 'D.FIRSTNAME' must be declared
ORA-06550: line 15, column 4:
PL/SQL: Statement ignored
ORA-06550: line 16, column 50:
PLS-00225: subprogram or cursor 'P' reference is out of scope
ORA-06550: line 16, column 4:
PL/SQL: Statement ignored
ORA-06550: line 17, column 10:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 17, column 4:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

Answer 1

    DECLARE
   TYPE dtype_amount IS RECORD(
     firstname DD_DONOR.FIRSTNAME%TYPE,
     lastname DD_DONOR.LASTNAME%TYPE,
     PLEDGEAMT DD_PLEDGE.PLEDGEAMT%TYPE,    
     atype DD_DONOR.TYPECODE%TYPE  
     
     );
   CURSOR donnor_pledge (adtype_amount dtype_amount ) IS
     SELECT d.FIRSTNAME, d.LASTNAME, 250, 'I' 
       FROM DD_DONOR d INNER JOIN DD_PLEDGE p
         USING (IDDONOR)
       WHERE d.TYPECODE = adtype_amount.atype AND 
       p.PLEDGEAMT > adtype_amount.PLEDGEAMT;    
       
     dtype_amount_rec_1  dtype_amount;   
     dtype_amount_rec_2  dtype_amount;    
             
BEGIN
   null;

   OPEN donnor_pledge(dtype_amount_rec_1);
      FETCH donnor_pledge INTO dtype_amount_rec_2 ;
      DBMS_OUTPUT.PUT_LINE('Donor name: ' || dtype_amount_rec_2.FIRSTNAME || ' ' || dtype_amount_rec_2.LASTNAME);
      DBMS_OUTPUT.PUT_LINE('Pledge amount: ' || dtype_amount_rec_2.PLEDGEAMT);            
   CLOSE donnor_pledge;   

END;
/
show errors

Answer 2

You need to declare the record type before using it in the cursor and then declare a variable for the record to pass into the cursor and then loop through the cursor.

DECLARE
  TYPE dtype_amount IS RECORD (
    typecode DD_DONOR.TYPECODE%TYPE,
    amount   DD_PLEDGE.PLEDGEAMT%TYPE
  );

  CURSOR donnor_pledge (p_typeamount dtype_amount ) IS
    SELECT d.FIRSTNAME,
           d.LASTNAME,
           p.PLEDGEAMT
    FROM   DD_DONOR d
           INNER JOIN DD_PLEDGE p
           USING (IDDONOR)
    WHERE d.TYPECODE  = p_typeamount.typecode
    AND   p.PLEDGEAMT > p_typeamount.amount;

  p_typeamount dtype_amount;
  p_name_amount donnor_pledge%ROWTYPE;
BEGIN
  p_typeamount.typecode := 'I';
  p_typeamount.amount   := 250;
  OPEN donnor_pledge(p_typeamount);
  LOOP
    FETCH donnor_pledge INTO p_name_amount;
    EXIT WHEN donnor_pledge%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Donor name: ' || p_name_amount.FIRSTNAME || ' ' || p_name_amount.LASTNAME);
    DBMS_OUTPUT.PUT_LINE('Pledge amount: ' || p_name_amount.PLEDGEAMT);
  END LOOP;
  CLOSE donnor_pledge;    
END;
/

or, you can do it without the record using two parameters for the cursor:

DECLARE
  CURSOR donnor_pledge (p_typecode CHAR, p_amount NUMBER ) IS
    SELECT d.FIRSTNAME,
           d.LASTNAME,
           p.PLEDGEAMT
    FROM   DD_DONOR d
           INNER JOIN DD_PLEDGE p
           USING (IDDONOR)
    WHERE d.TYPECODE  = p_typecode
    AND   p.PLEDGEAMT > p_amount;
  
  p_name_amount donnor_pledge%ROWTYPE;
BEGIN
  OPEN donnor_pledge('I', 250);
  LOOP
    FETCH donnor_pledge INTO p_name_amount;
    EXIT WHEN donnor_pledge%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Donor name: ' || p_name_amount.FIRSTNAME || ' ' || p_name_amount.LASTNAME);
    DBMS_OUTPUT.PUT_LINE('Pledge amount: ' || p_name_amount.PLEDGEAMT);
  END LOOP;
  CLOSE donnor_pledge;    
END;
/

db<>fiddle here