ok, my problem is this:
I have to create a stored procedure in order to reserve the free rooms to the students in the period comprised between the DATE_START and DATE_END.

step1: verify if the student is in table em:
if the student is not in table em, then insert new student

step2: find available room
if find it reserve room INTO rmpct

step3: update students in em

step4: update sex of room or apartment floor

step5: if no room is available raise error

I tried this procedure:

CREATE OR REPLACE PROCEDURE reserve_rooms
-- input parameters:
(p_em_id IN em.em_id%TYPE,
p_cod_stud IN em.cod_stud%TYPE,
p_bl_id IN rm.bl_id%TYPE,
p_fl_id IN rm.fl_id%TYPE,
p_rm_id IN rm.rm_id%TYPE,
p_sex IN rm.sex%TYPE,
p_date_start IN VARCHAR2,
p_date_end IN VARCHAR2)
AS
-- local variables:
v_appo INTEGER;
v_bl_id rm.bl_id%TYPE;
v_fl_id rm.fl_id%TYPE;
v_rm_id rm.rm_id%TYPE;
v_rm_cat rm.rm_cat%TYPE;
v_sex rm.sex%TYPE;
BEGIN
-- verify if the student is in table em:
SELECT COUNT (*)
INTO v_appo
FROM em
WHERE em_id = p_em_id
AND cod_stud = p_cod_stud;
-- if the student is not in table em, then insert new student:
IF v_appo = 0 THEN
INSERT INTO em (em_id, cod_stud)
VALUES (p_em_id, p_cod_stud);
END IF;
BEGIN
-- find available room:
SELECT bl_id, fl_id, rm_id, sex, rm_cat
INTO v_bl_id, v_fl_id, v_rm_id, v_sex, v_rm_cat
FROM (SELECT rm.bl_id, rm.fl_id, rm.rm_id, rm.sex, rm.rm_cat
FROM rmpct, rm
WHERE rm.bl_id = rmpct.bl_id (+)
AND rm.fl_id = rmpct.fl_id (+)
AND rm.rm_id = rmpct.rm_id (+)
AND rm.bl_id = NVL (p_bl_id, rm.bl_id)
AND rm.fl_id = NVL (p_fl_id, rm.fl_id)
AND rm.rm_id = NVL (p_rm_id, rm.rm_id)
AND (rm.sex = p_sex OR rm.sex = 0)
AND rmpct.date_start (+) <= TO_DATE (p_date_end, 'DD-MM-YYYY')
AND rmpct.date_end (+) >= TO_DATE (p_date_start, 'DD-MM-YYYY')
GROUP BY rm.bl_id, rm.fl_id, rm.rm_id, rm.sex, rm.rm_cat, rm.rm_std
HAVING SUM (decode(rmpct.rm_id (+),null,0,1))
< DECODE (rm_std, 'S', 1, 'D', 2, 'T', 3)
ORDER BY DBMS_RANDOM.RANDOM)
WHERE ROWNUM = 1;
-- reserve room:
INSERT INTO rmpct (bl_id, fl_id, rm_id, em_id, cod_stud, date_start, date_end)
VALUES (v_bl_id, v_fl_id, v_rm_id, p_em_id, p_cod_stud,
TO_DATE (p_date_start, 'DD-MM-YYYY'),
TO_DATE (p_date_end, 'DD-MM-YYYY'));
-- update students:
UPDATE em
SET bl_id = v_bl_id, fl_id = v_fl_id, rm_id = v_rm_id
WHERE em_id = p_em_id
AND cod_stud = p_cod_stud;
-- update sex of room or apartment floor:
IF v_sex = 0 THEN
IF v_rm_cat = 'ROOM' THEN
UPDATE rm
SET sex = p_sex
WHERE bl_id = v_bl_id
AND fl_id = v_fl_id
AND rm_id = v_rm_id;
ELSIF v_rm_cat = 'APARTMENT' THEN
UPDATE rm
SET sex = p_sex
WHERE bl_id = v_bl_id
AND fl_id = v_fl_id;
END IF;
END IF;
EXCEPTION
-- if no room is available:
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20001, 'Sorry, there is no such room available.');
END;
END reserve_rooms;

Have you any idea?

Thanks in advance!