CODEWITHSUNDEEP
oracle-databaseplsqlprocedure
Drazen
Drazen

Reputation: 107

Oracle error while running created procedure

I have Oracle database based on Hotel room stay overs. I have created a procedure Room_taken and I can't make it work.

This is my SQL procedure code:

CREATE
PROCEDURE Room_taken
(
v_guestID IN NUMBER DEFAULT NULL ,
v_stayfrom IN DATE DEFAULT NULL ,
v_stayto IN DATE DEFAULT NULL ,
v_roomID IN NUMBER DEFAULT NULL ,
v_paymentID IN NUMBER DEFAULT NULL ,
v_totalprice IN NUMBER DEFAULT NULL 
)
AS
v_nofrom DATE;
v_noto DATE;
v_idroom NUMBER(10,0);

BEGIN

BEGIN
SELECT Stay_from, Stay_to     
INTO v_nofrom, v_noto
FROM Stayover
WHERE ID_room = v_roomID;
IF NOT ( v_nofrom >= v_stayfrom
AND v_noto <= v_stayto ) THEN

BEGIN
INSERT INTO Stayover
( Stay_from, Stay_to, Total_price, ID_Room, ID_Guest, ID_Payment )
VALUES ( v_stayfrom, v_stayto, v_totalprice, v_roomID, v_guestID, v_paymentID );
END;
ELSE

BEGIN
raise_application_error( -20002, 'Room is taken!' );
END;
END IF;
END;
END;

When I run procedure after entering following parameters:

DECLARE
v_guestID NUMBER;
v_stayfrom DATE;
v_stayto DATE;
v_roomID NUMBER;
v_paymentID NUMBER;
v_totalprice NUMBER;
BEGIN
v_guestID := 1;
v_stayfrom := '17.01.2012';
v_stayto := '19.01.2012';
v_roomID := 1;
v_paymentID := 1;
v_totalprice := 300;

Room_taken(
v_guestID => v_guestID,
v_stayfrom => v_stayfrom,
v_stayto => v_stayto,
v_roomid => v_roomid,
v_paymentID => v_paymentID,
v_totalprice => v_totalprice
);
END;

I got an error:

Connecting to the database HOTEL.
ORA-01403: no data found
ORA-06512: at "HOTEL_ROOMS.ROOM_TAKEN", line 18
ORA-06512: at line 16
Process exited.
Disconnecting from the database HOTEL.

What am I doing wrong?

Upvotes: 2

Views: 8137

Answers (2)

Vincent Malgrat
Vincent Malgrat

Reputation: 67802

You are getting an error because a SELECT INTO construct must return exactly 1 row in PL/SQL. Your first select returns no row, so the NO_DATA_FOUND error is raised.

If there were 2 rows (for example a reservation in january, another in february), a TOO_MANY_ROWS error would be raised instead.

What you want to do instead is to make sure that if you insert, there will be no overlap. For example the following query will return all reservations during a period:

SELECT * 
  FROM Stayover 
 WHERE ID_room = v_roomID
   AND stay_from <= v_stayto 
   AND stay_to >= v_stayfrom

Notice that I compared stay_from to v_stayto, this is not a typo.

You could include the above query into your code with something like this:

CREATE OR REPLACE PROCEDURE Room_taken(v_guestID    IN NUMBER,
                                       v_stayfrom   IN DATE,
                                       v_stayto     IN DATE,
                                       v_roomID     IN NUMBER,
                                       v_paymentID  IN NUMBER DEFAULT NULL,
                                       v_totalprice IN NUMBER DEFAULT NULL) AS
   l_nb_reservation NUMBER;
BEGIN
   SELECT COUNT(*)
     INTO l_nb_reservation
     FROM Stayover
    WHERE ID_room = v_roomID
      AND stay_from <= v_stayto
      AND stay_to >= v_stayfrom;
   IF l_nb_reservation > 0 THEN
      raise_application_error(-20002, 'Room is taken!');
   END IF;

   INSERT INTO Stayover
      (Stay_from, Stay_to, Total_price, ID_Room, ID_Guest, ID_Payment)
   VALUES
      (v_stayfrom, v_stayto, v_totalprice, v_roomID, v_guestID, v_paymentID);
END;

Now you will be able to reserve a room only once per period:

SQL> EXEC Room_taken(1, trunc(SYSDATE), trunc(SYSDATE), 1);

PL/SQL procedure successfully completed

SQL> EXEC Room_taken(1, trunc(SYSDATE), trunc(SYSDATE), 1);

begin Room_taken(1, trunc(SYSDATE), trunc(SYSDATE), 1); end;

ORA-20002: Room is taken!
ORA-06512: à "APPS.ROOM_TAKEN", ligne 16
ORA-06512: à ligne 2

Upvotes: 4

Ahmed Nabil
Ahmed Nabil

Reputation: 19046

Error Message
ORA-01403: no data found

Cause of Error

You tried one of the following:

  1. You executed a SELECT INTO statement and no rows were returned.
  2. You referenced an uninitialized row in a table.
  3. You read past the end of file with the UTL_FILE package.

To show a suggested solution
Check this link
http://www.techonthenet.com/oracle/errors/ora01403.php

Upvotes: 1

Related Questions