A local variable is declared but not recognized inside the PL/SQL block

Question

I am trying to display pledge ID, pledge amount, number of monthly payments, payment date, payment amount, and first payment made for each pledge, sorted by pledge ID and then by payment date.

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)

Here is my code:

DECLARE
    lv_pledge_ID DD_PLEDGE.IDPLEDGE%TYPE;
    lv_pledge_amount DD_PLEDGE.PLEDGEAMT%TYPE;
    lv_num_month_pay DD_PLEDGE.PAYMONTHS%TYPE;
    lv_pay_date DATE;
    lv_pay_amount NUMBER(6);
    lv_frst_pledge DD_PLEDGE.FIRSTPLEDGE%TYPE;
BEGIN
    FOR rec IN (SELECT DISTINCT IDDONOR FROM DD_PLEDGE)
    LOOP
        SELECT IDPLEDGE, PLEDGEAMT, PAYMONTHS, PLEDGEDATE, PLEDGEAMT*PAYMONTHS, FIRSTPLEDGE
        INTO lv_pledge_ID, lv_pledge_amount, lv_num_month_pay, lv_pay_date, lv_pay_amount, lv_frst_pledge
        FROM DD_PLEDGE      
        ORDER BY IDPLEDGE, PLEDGEDATE;
        LOOP
            DBMS_OUTPUT.PUT_LINE('pledge ID: ' || rec.lv_pledge_ID);
            DBMS_OUTPUT.PUT_LINE('pledge amount: ' || rec.lv_pledge_amount);
            DBMS_OUTPUT.PUT_LINE('number of monthly payments: ' || rec.lv_num_month_pay);
            DBMS_OUTPUT.PUT_LINE('payment date: ' || rec.lv_pay_date);
            DBMS_OUTPUT.PUT_LINE('payment amount: ' || rec.lv_pay_amount);
            DBMS_OUTPUT.PUT_LINE('first payment: ' || rec.lv_frst_pledge);
        END LOOP;
    END LOOP;
END;

I get an error:

Error report -
ORA-06550: line 16, column 46:
PLS-00302: component 'LV_PLEDGE_ID' must be declared
ORA-06550: line 16, column 4:
PL/SQL: Statement ignored
ORA-06550: line 17, column 50:
PLS-00302: component 'LV_PLEDGE_AMOUNT' must be declared
ORA-06550: line 17, column 4:
PL/SQL: Statement ignored
ORA-06550: line 18, column 63:
PLS-00302: component 'LV_NUM_MONTH_PAY' must be declared
ORA-06550: line 18, column 4:
PL/SQL: Statement ignored
ORA-06550: line 19, column 49:
PLS-00302: component 'LV_PAY_DATE' must be declared
ORA-06550: line 19, column 4:
PL/SQL: Statement ignored
ORA-06550: line 20, column 51:
PLS-00302: component 'LV_PAY_AMOUNT' must be declared
ORA-06550: line 20, column 4:
PL/SQL: Statement ignored
ORA-06550: line 21, column 50:
PLS-00302: component 'LV_FRST_PLEDGE' must be declared
ORA-06550: line 21, column 4:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.

The error message doesn't make any sense to me. Why doesn't it recognize my variables?

Updated:

BEGIN
    FOR rec IN (SELECT DISTINCT IDDONOR FROM DD_PLEDGE)
    LOOP
        SELECT IDPLEDGE, PLEDGEAMT, PAYMONTHS, PLEDGEDATE, PLEDGEAMT*PAYMONTHS AS PAYAMONT, FIRSTPLEDGE
        FROM DD_PLEDGE
        ORDER BY IDPLEDGE, PLEDGEDATE;
        LOOP
            DBMS_OUTPUT.PUT_LINE('pledge ID: ' || rec.IDPLEDGE);
            DBMS_OUTPUT.PUT_LINE('pledge amount: ' || rec.PLEDGEAMT);
            DBMS_OUTPUT.PUT_LINE('number of monthly payments: ' || rec.PAYMONTHS);
            DBMS_OUTPUT.PUT_LINE('payment date: ' || rec.PLEDGEDATE);
            DBMS_OUTPUT.PUT_LINE('payment amount: ' || rec.PAYAMONT);
            DBMS_OUTPUT.PUT_LINE('first payment: ' || rec.FIRSTPLEDGE);
        END LOOP;
    END LOOP;
END;

The error:

Error report -
ORA-06550: line 16, column 3:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:

   ; <an identifier> <a double-quoted delimited-identifier>
The symbol ";" was substituted for "end-of-file" to continue.
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Update: Deleted the second LOOP.

BEGIN
    FOR rec IN (SELECT DISTINCT IDDONOR FROM DD_PLEDGE)
    LOOP
        SELECT IDPLEDGE, PLEDGEAMT, PAYMONTHS, PLEDGEDATE, PLEDGEAMT*PAYMONTHS AS PAYAMONT, FIRSTPLEDGE
        FROM DD_PLEDGE
        ORDER BY IDPLEDGE, PLEDGEDATE;      
            DBMS_OUTPUT.PUT_LINE('pledge ID: ' || rec.IDPLEDGE);
            DBMS_OUTPUT.PUT_LINE('pledge amount: ' || rec.PLEDGEAMT);
            DBMS_OUTPUT.PUT_LINE('number of monthly payments: ' || rec.PAYMONTHS);
            DBMS_OUTPUT.PUT_LINE('payment date: ' || rec.PLEDGEDATE);
            DBMS_OUTPUT.PUT_LINE('payment amount: ' || rec.PAYAMONT);
            DBMS_OUTPUT.PUT_LINE('first payment: ' || rec.FIRSTPLEDGE);     
    END LOOP;
END;

The error:

Error report -
ORA-06550: line 4, column 3:
PLS-00428: an INTO clause is expected in this SELECT statement
ORA-06550: line 7, column 48:
PLS-00302: component 'IDPLEDGE' must be declared
ORA-06550: line 7, column 4:
PL/SQL: Statement ignored
ORA-06550: line 8, column 52:
PLS-00302: component 'PLEDGEAMT' must be declared
ORA-06550: line 8, column 4:
PL/SQL: Statement ignored
ORA-06550: line 9, column 65:
PLS-00302: component 'PAYMONTHS' must be declared
ORA-06550: line 9, column 4:
PL/SQL: Statement ignored
ORA-06550: line 10, column 51:
PLS-00302: component 'PLEDGEDATE' must be declared
ORA-06550: line 10, column 4:
PL/SQL: Statement ignored
ORA-06550: line 11, column 53:
PLS-00302: component 'PAYAMONT' must be declared
ORA-06550: line 11, column 4:
PL/SQL: Statement ignored
ORA-06550: line 12, column 52:
PLS-00302: component 'FIRSTPLEDGE' must be declared
ORA-06550: line 12, column 4:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

With a specific donor it works fine, just doesn't print anything.

DECLARE
    lv_pledge_ID DD_PLEDGE.IDPLEDGE%TYPE;
    lv_pledge_amount DD_PLEDGE.PLEDGEAMT%TYPE;
    lv_num_month_pay DD_PLEDGE.PAYMONTHS%TYPE;
    lv_pay_date DATE;
    lv_pay_amount NUMBER(6);
    lv_frst_pledge DD_PLEDGE.FIRSTPLEDGE%TYPE;
BEGIN
    SELECT IDPLEDGE, PLEDGEAMT, PAYMONTHS, PLEDGEDATE, PLEDGEAMT*PAYMONTHS, FIRSTPLEDGE
    INTO lv_pledge_ID, lv_pledge_amount, lv_num_month_pay, lv_pay_date, lv_pay_amount, lv_frst_pledge
    FROM DD_PLEDGE
    WHERE IDDONOR = 302
    ORDER BY IDPLEDGE, PLEDGEDATE;

    DBMS_OUTPUT.PUT_LINE('pledge ID: ' || lv_pledge_ID);
    DBMS_OUTPUT.PUT_LINE('pledge amount: ' || lv_pledge_amount);
    DBMS_OUTPUT.PUT_LINE('number of monthly payments: ' || lv_num_month_pay);
    DBMS_OUTPUT.PUT_LINE('payment date: ' || lv_pay_date);
    DBMS_OUTPUT.PUT_LINE('payment amount: ' || lv_pay_amount);
    DBMS_OUTPUT.PUT_LINE('first payment: ' || lv_frst_pledge);

END;

PL/SQL procedure successfully completed.

rec.lv_pledge_ID ? rec has only IDDONOR, Did you mean to use lv_pledge_ID? — Ori Marko
– Ori Marko, Commented Oct 29, 2020 at 13:50
Well, I deleted all the local variables and got the same error, but now with the real column names instead. I think I require "rec" to reference the loop. Am I not? — Alan Shore
– Alan Shore, Commented Oct 29, 2020 at 13:57

VN'sCorner · Accepted Answer · 2020-10-29 16:29:14Z

2

For loop cursor(implicit Cursor) and open..fetch(explicit cursor) cursor works differently. When using implicit cursor opening and closing of cursor is automatically taken care of.From implementation stand point it is easier to implement when compared to explicit cursor.From performance standpoint they are faster as well.

Bonus - you can use the fields in the select statement as cursor variables and there isn't any need to declare variables to fetch the records to.

Not sure of the purpose of multiple loops, i have removed redundant code and simplified.This should work.

SET SERVEROUTPUT ON SIZE 100000
DECLARE
  
BEGIN
    FOR rec IN (SELECT IDPLEDGE, PLEDGEAMT, PAYMONTHS, PLEDGEDATE, PLEDGEAMT*PAYMONTHS TOTPLEDGEAMT, FIRSTPLEDGE
                FROM DD_PLEDGE      
                ORDER BY IDPLEDGE, PLEDGEDATE)
                
    LOOP
            DBMS_OUTPUT.PUT_LINE('pledge ID: ' || rec.IDPLEDGE);
            DBMS_OUTPUT.PUT_LINE('pledge amount: ' || rec.PLEDGEAMT);
            DBMS_OUTPUT.PUT_LINE('number of monthly payments: ' || rec.PAYMONTHS);
            DBMS_OUTPUT.PUT_LINE('payment date: ' || rec.PLEDGEDATE);
            DBMS_OUTPUT.PUT_LINE('payment amount: ' || rec.TOTPLEDGEAMT);
            DBMS_OUTPUT.PUT_LINE('first payment: ' || rec.FIRSTPLEDGE);
            
    END LOOP;
END;

edited Oct 29, 2020 at 16:29

answered Oct 29, 2020 at 16:11

VN'sCorner

1,5521 gold badge9 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Alan Shore Over a year ago

Thanks for the simplification. It compiles, but doesn't print anything.

VN'sCorner Over a year ago

It depends on the setting on the client you use. SET SERVEROUTPUT ON is required if you intent to display output as SQL*Plus does't display the output by default from PL/SQL. oreilly.com/library/view/oracle-sqlplus-the/0596007469/…

Alan Shore Over a year ago

I am using the SQL Developer and it worked there too, after you added that code on the top.

Collectives™ on Stack Overflow

A local variable is declared but not recognized inside the PL/SQL block

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related