question

bobbylove avatar image
bobbylove asked

Write a pl/sql procedure for the following question. Show specific event: Given an event type (Birthday, Wedding, etc.) display all events of that type in all hotels along with the address of the hotel and the date of the event.

The tables are below

Hotel Table have

Hotel_id, hotel_name, Hotel_city, Hotel_state, Hotel_zip,Hotel_phone,

Reservation Table have

Reservation_id, Hotel_id, Room_num, Service_id, Guest_name, Reservation_start_date, Reservation_end_date, cancelation_date, Num_of_guest, event_type

Room Table have

Room_num, Hotel_id, Room_type, Room_capacity, Room_cost

service table have

service_id, Service_type, Service_cost

This is what i tried. the procedure compiled, and when i excute it it just say procedure successfully completed but i want my output to be the print statment(dbms statment). Im not even sure if my code is correct (fit the question). my code is below.

set serveroutput on; 
 create or replace  procedure change_event (Event_Name in VARCHAR2)
IS
cursor c1 is select Event_type, Hotel_name , Hotel_city,Hotel_state, Hotel_Zip ,        hotel_phone 
from reservation, Hotel
where reservation.Hotel_id = hotel.hotel_id
and event_type = event_name;
R_row c1%rowtype; 
begin
for R_row in c1
loop
Dbms_output.put_line (R_row.event_type || '  '|| R_row.Hotel_name  ||' , '|| R_row.Hotel_city || ' , '||R_row.Hotel_state||'  '|| R_row.Hotel_Zip ||' , '|| R_row.hotel_phone );
end loop;
end;

exec change_event(' Birthday');




functionsplsqlprocedure
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·

What have you tried so far? Happy to help with homework but like to see that you’ve tried something too.

0 Likes 0 ·

1 Answer

·
bobbylove avatar image
bobbylove answered

@KenJ This is what i tried. the procedure compiled, and when i excute it it just say procedure successfully completed but i want my output to be the print statment(dbms statment). Im not even sure if my code is correct (fit the question). my code is below. Thank you

set serveroutput on;  
create or replace  procedure change_event (Event_Name in VARCHAR2)
IS
cursor c1 is select Event_type, Hotel_name , Hotel_city,Hotel_state, Hotel_Zip ,hotel_phone
from reservation, Hotel
where reservation.Hotel_id = hotel.hotel_id
and event_type = event_name;
R_row c1%rowtype;
begin
for R_row in c1
loop
Dbms_output.put_line (R_row.event_type || '  '|| R_row.Hotel_name  ||' , '|| R_row.Hotel_city 
|| ' , '||R_row.Hotel_state||'  '|| R_row.Hotel_Zip ||' , '|| R_row.hotel_phone );
end loop;
end;

exec change_event(' Birthday');

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

KenJ avatar image KenJ commented ·
The query itself seems reasonable for what you are after.  Can you redo the procedure and just return the query results rather than adding the complexity of the print statement?  That might help narrow things down (also, I notice a leading space in ‘ Birthday’.  Does that actually match any data?


set serveroutput on;
create or replace  procedure change_event (Event_Name in VARCHAR2)
IS

select Event_type, Hotel_name , Hotel_city,Hotel_state, Hotel_Zip ,hotel_phone
from reservation inner join Hotel
on reservation.Hotel_id = hotel.hotel_id
Where reservation.event_type = Event_Name;

end;

exec change_event(' Birthday');
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.