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

Question

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!

ekochergin · Accepted Answer · 2022-11-17 10:42:24Z

1

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?

answered Nov 17, 2022 at 10:42

ekochergin

4,1692 gold badges14 silver badges20 bronze badges

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

1 Comment

Corné Ackerman Over a year ago

Thank you for the solution and explanation, learnt from this. I was forced to use variables as I am working through old exam papers as learning, and the mark allocation states "Correct variables created".

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related