can someone rectify the code please

[mysticmoonlight]

Create or Replace Procedure Cust_Reservation(p_ID IN VARCHAR2) AS
2 CURSOR c_viewreservation IS
3 SELECT Consumer.FirstName,Consumer.LastName,Restaurant.Name,Restaurant.Type,Reservation.ReservationDateTime ,Consumer.ConsumerID FROM(Consumer INNER JOIN Reservation ON Consumer.ConsumerID = Reservation.ConsumerID)INNER JOIN Restaurant ON Reservation.RestaurantID = Restaurant.RestaurantID WHERE ConsumerID = p_ID;
4 v_viewreservation c_viewreservation%ROWTYPE;
5 BEGIN
6 OPEN c_viewreservation;
7 FETCH c_viewreservation INTO v_viewreservation;
8 WHILE c_viewreservation%FOUND LOOP
9 DBMA_OUTPUT.PUT_LINE(v_viewreservation.FirstName||' '||v_viewreservation.LastName||' '||v_viewreservation.Name||' '||v_viewreservation.Type||' '||v_viewreservation.ReservationDateTime);
10 FETCH c_viewreservation INTO v_viewreservation;
11 END LOOP;
12 CLOSE c_viewreservation;
13 END Cust_Reservation;
14 /

Warning: Procedure created with compilation errors.

SQL> show error
Errors for PROCEDURE CUST_RESERVATION:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/8 PLS-00341: declaration of cursor 'C_VIEWRESERVATION' is
incomplete or malformed

3/2 PL/SQL: SQL Statement ignored
3/318 PL/SQL: ORA-00918: column ambiguously defined
4/20 PL/SQL: Item ignored
7/2 PL/SQL: SQL Statement ignored
7/31 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

9/3 PL/SQL: Statement ignored

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/24 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

10/1 PL/SQL: SQL Statement ignored
10/30 PLS-00320: the declaration of the type of this expression is
incomplete or malformed

[tamilselvan]

 SELECT 
   consumer.FirstName,Consumer.LastName,
  Restaurant.Name,Restaurant.Type,
Reservation.ReservationDateTime ,
Consumer.ConsumerID 
FROM(Consumer INNER JOIN Reservation ON 
Consumer.ConsumerID = Reservation.ConsumerID)
INNER JOIN Restaurant ON 
Reservation.RestaurantID = Restaurant.RestaurantID
 WHERE ConsumerID = p_ID;

Is this working?

Tamil

[mysticmoonlight]

the query is working..
i tested it in access!


it has soemthing to do with cursor and declaration..

[WilliamR]

> I tested it in access

Test it in Oracle.

[mysticmoonlight]

that part worksss!!!! in ORACLE too

[gamyers]

Even though you tell Oracle that the ConsumerId on Reservation and Consumer have to be the same...
"(Consumer INNER JOIN Reservation ON Consumer.ConsumerID = Reservation.ConsumerID)"

You still have to tell it which one you want equal to the parameter.
WHERE ??????.ConsumerID = p_ID;