Answer to Question #273160 in Databases | SQL | Oracle | MS Access for Ampfarisaho

Question

Your colleague, John, has been struggling with a PL/SQL query

Identify the errors and re‐write the correct code:

set serveroutput on

 c_id CUSTOMER.cust_id%Type;

 t TOWNHOUSE.house_num%Type;

 m_name MATERIALS.material_name%Type;

cursor info is

select cu.cust_id, t.house_num, m.material_name

from CUSTOMER cu, TOWNHOUSE t, MATERIALS m, PURCHASE_AGREEMENT pa

where cu.cust_id = pa.cust_id

and t.house_num = pa.house_num

and m.material_id = pa.material_id

begin

for rec in info

 c_id:=rec.cust_id;

 t:= rec.house_num;

 m_name:=rec.material_name;

 dbms_output_put_line('CUSTOMER ID:  ' || c_id || ', ' || chr(13) ||'HOUSE NUM:   ' || t ||

chr(13) || 'MATERIALS:   ' || m_name);

 dbms_output.put_line('‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐');

end loop;

Answer 1

There is a problem with the SELECT statement.
The SELECT statement can be:
select cu.cust_id, t1.house_num, m.material_name

from CUSTOMER cus, TOWNHOUSE t1, MATERIALS m, PURCHASE_AGREEMENT pa

where cus.cust_id = pa.cust_id

and t1.house_num = pa.house_num

and m.material_id = pa.material_id