PL/SQL procedure with parameters

hey can anyone help me with PL/SQL statements i am having trouble with. Our assignment is a video shop database.
this is what i'm trying to do....

Write a procedure called DUE_FILMS that accepts a date (in DD/MM/YYYY format) that will determine the films that are either overdue or due back on that day. The procedure must display the film title, format, copy number, customer’s name and contact number, the date it was rented and due, the number of days it is overdue and the fine due. The output should be in date due order (most overdue film first). The data should also be written in UPPERCASE to a table called FILMS_DUE_AND_OVERDUE.

this is what i have for it....

create or replace procedure due_films(overdue_date IN DATE)
IS
cursor c1 is
select fr.date_rented, fr.date_due, c.cust_firstname, c.cust_lastname, c.cust_contact, f.film_title, f.classification, fc.format, fc.copy_nbr
from film_rental fr, customer c, film f, film_copy fc
where fr.customer_nbr = c.customer_nbr
and fr.film_id = f.film_id
and fr.film_id = fc.film_id
and date_due = overdue_date;
rented_date DATE;
due_date DATE;
cus_lastname VARCHAR(15);
cus_firstname VARCHAR(15);
contact VARCHAR(9);
title VARCHAR(20);
class VARCHAR(2);
f_mat VARCHAR(3);
copy_num NUMBER(4);
begin
open c1;
for i in 1..100 LOOP
fetch c1 INTO rented_date, due_date, cus_lastname, cus_firstname,contact, title, class, f_mat, copy_num;
exit when c1%NOTFOUND;
dbms_output.put_line(rented_date||' '||due_date||' '||cus_lastname||' '||cus_firstname||' '||contact||' '||title||' '||class||' '||f_mat||' '||copy_num);
end loop;
close c1;
end;



but the data i get is like when you dont have a join in and it creates a cartesian product...

17/OCT/03 19/OCT/03 JOHN SMITH 59736454 CONAIR G DVD 1
17/OCT/03 19/OCT/03 JOHN SMITH 59736454 CONAIR G VHS 1
17/OCT/03 19/OCT/03 JOHN SMITH 59736454 CONAIR G VHS 2
17/OCT/03 19/OCT/03 JOHN SMITH 59736454 CONAIR G VHS 3
17/OCT/03 19/OCT/03 JOHN SMITH 59736454 CONAIR G VHS 4
17/OCT/03 19/OCT/03 JOHN SMITH 59736454 CONAIR G VHS 5


i'm not sure what i'm doing wrong...........can anybody help........please

It seems that your joins are correct but might be you have missed something. I believe that you have more than one copy of some of these films that's why it returns more than one records from the film_copy table because the film_id is same. So whenever you try to join the tables it replicates all copy of those movies with same customer name and movie name. You should add one new column in film_rental table i,e copy_nbr and then join film_rental's copy_nbr with film_copy's copy_nbr column like that
and fr.copy_nbr = fc.copy_nbr

You will get the result that you wanted.

Note : Next time must send template of your table structure.

Message: Make your life easy, Make others life easier.

Regards