PL/SQL Script - DECLARE statement - component must be declared error-CodePudding

So I have this script that I am trying to fix. I am supposed to declare variables and use them to print the final results. I looked at other examples and tried to write my script from what I've read.

here is what i have:

SET serveroutput ON

DECLARE
    cust_name customer.cust_fname%type;
    loc locations.location_name%type;
    book_date booking.booking_date%type;

BEGIN
    FOR rec IN (
        SELECT 
        (customer.cust_fname || ', ' || customer.cust_sname) AS name,
        locations.location_name AS location,
        booking.booking_date
        INTO cust_name, loc, book_date
        FROM booking
        INNER JOIN customer ON customer.cust_id = booking.cust_id
        INNER JOIN locations ON locations.location_id = booking.location_id
        WHERE booking.booking_date = '20 June 2017'
    ) 
    LOOP
        dbms_output.put_line('CUSTOMER: ' || rec.cust_name);
        dbms_output.put_line('LOCATION: ' || rec.loc);
        dbms_output.put_line('PARTICIPANTS: ' || rec.book_date);
        dbms_output.put_line('------------------------');
    END LOOP;
END;

here is the errors I get:

Error report -
ORA-06550: line 19, column 50:
PLS-00302: component 'CUST_NAME' must be declared
ORA-06550: line 19, column 9:
PL/SQL: Statement ignored
ORA-06550: line 20, column 50:
PLS-00302: component 'LOC' must be declared
ORA-06550: line 20, column 9:
PL/SQL: Statement ignored
ORA-06550: line 21, column 54:
PLS-00302: component 'BOOK_DATE' must be declared
ORA-06550: line 21, column 9:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

This is the results I want from the script:

anonymous block completed
CUSTOMER: Philip, Jackson
LOCATION: Durban
PARTICIPANTS: 20 June 2017
--------------------------------------------------
CUSTOMER: Sarah, Jones
LOCATION: Cape Town
PARTICIPANTS: 20 June 2017
--------------------------------------------------

The script works well if i simply don't use the declarations, but due to the circumstances I have to. I am completely lost as to why this doesn't work with the declarations. Any help will be appreciated!

CodePudding user response：

In a loop you can't select .. into, only select is possible. And using loop makes variable not needed at all. But since you're somehow forced to use variables, you may use them within loop.

SET serveroutput ON

DECLARE
    cust_name customer.cust_fname%type;
    loc locations.location_name%type;
    book_date booking.booking_date%type;

BEGIN
    FOR rec IN (
        SELECT 
        (customer.cust_fname || ', ' || customer.cust_sname) AS cust_name,
        locations.location_name,
        booking.booking_date
        FROM booking
        INNER JOIN customer ON customer.cust_id = booking.cust_id
        INNER JOIN locations ON locations.location_id = booking.location_id
        WHERE booking.booking_date = '20 June 2017'
    ) 
    LOOP
        cust_name := rec.cust_mame;
        loc := rec.location_name;
        book_date := rec.booking_date;
        dbms_output.put_line('CUSTOMER: ' || cust_name);
        dbms_output.put_line('LOCATION: ' || loc);
        dbms_output.put_line('PARTICIPANTS: ' || book_date);
        dbms_output.put_line('------------------------');
    END LOOP;
END;

Can it be that you've been told to use collections, not variables?