[SQL] Success message for an end user inside an SQL procedure

I am using Oracle APEX and have a SQL procedure that makes a reservation for a customer and then adds the required number of tables to that customer.

The issue I am facing is that when I submit the form and the procedure starts, there isn't a success message, so the user can't see if their reservation was successful. I can't just use a redirect after the submit button is pressed because it won't show my application errors if the procedure fails.

As you can see, I have tried to implement a solution to display a success message, but it isn't working. Please refer to the last IF statement in my code for what I am trying to accomplish.

create or replace PROCEDURE AssignTable2(
p_KlantNaam VARCHAR2,
p_KlantEmail VARCHAR2,
p_KlantTelefoon NUMBER,
p_ReservatieDatum DATE,
p_BeginTijd TIMESTAMP,
p_EindTijd TIMESTAMP,
p_NumKlanten NUMBER
) AS
v_RequiredTafels NUMBER;
v_AssignedTafels NUMBER := 0;
v_AvailableTafels NUMBER;
v_TafelID NUMBER;
v_ReservatieID NUMBER;
v_KlantID NUMBER;
BEGIN
v_RequiredTafels := CEIL(p_NumKlanten / 4.0);
DBMS_OUTPUT.PUT_LINE('After selecting table: v_RequiredTafels = ' || TO_CHAR(v_RequiredTafels));
-- Check for available tables
-- Check for available tables
SELECT COUNT(t.id) INTO v_AvailableTafels
FROM TAFEL t
WHERE t.id NOT IN (
SELECT rt.FK_TAFELID
FROM RESERVATIE r
INNER JOIN RESERVATIETAFEL rt ON r.id = rt.FK_RESERVATIEID
WHERE r.datum = p_ReservatieDatum and t.FK_STATUS = 1
AND (
-- Check for overlap with existing reservations
(p_BeginTijd < r.eind_tijd + INTERVAL '1' HOUR AND p_BeginTijd >= r.begin_tijd)
OR (p_EindTijd > r.begin_tijd AND p_EindTijd <= r.eind_tijd)
OR (r.begin_tijd >= p_BeginTijd AND r.eind_tijd <= p_EindTijd)
-- Check for overlap with existing reservations that start before the new time slot
OR (r.begin_tijd < p_BeginTijd AND r.eind_tijd > p_BeginTijd)
-- Check for overlap with existing reservations that end after the new time slot
OR (r.begin_tijd < p_EindTijd AND r.eind_tijd > p_EindTijd)
)

);
DBMS_OUTPUT.PUT_LINE('After selecting table: v_AvailableTafels = ' || TO_CHAR(v_AvailableTafels));
DBMS_OUTPUT.PUT_LINE('Time slot: ' || TO_CHAR(p_BeginTijd) || ' - ' || TO_CHAR(p_EindTijd));
IF v_AvailableTafels < v_RequiredTafels THEN
RAISE_APPLICATION_ERROR(-20001, 'Sorry maar er zijn niet genoeg tafels op het gekozen moment met uw aantal gasten, probeer een andere tijdstip');
ELSE
IF p_KlantNaam IS NOT NULL THEN
INSERT INTO EX_KLANT (naam, email, telefoon)
VALUES (p_KlantNaam, p_KlantEmail, p_KlantTelefoon)
RETURNING id INTO v_KlantID;
ELSE
RAISE_APPLICATION_ERROR(-20002, 'KlantNaam mag niet leeg zijn');
END IF;
IF p_ReservatieDatum IS NOT NULL THEN
if p_ReservatieDatum > TRUNC(SYSDATE) THEN
-- Insert a new reservation
INSERT INTO RESERVATIE (FK_EX_KLANTID, num_klanten, datum, begin_tijd, eind_tijd)
VALUES (v_KlantID, p_NumKlanten, p_ReservatieDatum, p_BeginTijd, p_EindTijd)
RETURNING id INTO v_ReservatieID;
ELSE
RAISE_APPLICATION_ERROR(-20004, 'Reservatiedatum mag niet de dag voor vandaag zijn');
END IF;
ELSE
RAISE_APPLICATION_ERROR(-20003, 'Reservatie datum mag niet leeg zijn');
END IF;
-- Loop until we have assigned enough tables
WHILE v_AssignedTafels < v_RequiredTafels LOOP
SELECT t.id INTO v_TafelID
FROM (
SELECT t.id, t.code
FROM TAFEL t
LEFT JOIN RESERVATIETAFEL rt ON t.id = rt.FK_TAFELID
LEFT JOIN RESERVATIE r ON rt.FK_RESERVATIEID = r.id
WHERE t.FK_STATUS = 1 AND (r.id IS NULL OR r.datum <> p_ReservatieDatum OR r.eind_tijd < p_BeginTijd OR r.begin_tijd > p_EindTijd)
AND NOT EXISTS (
SELECT 1
FROM RESERVATIETAFEL rt
WHERE rt.FK_TAFELID = t.id
AND rt.FK_RESERVATIEID IN (
SELECT r.id
FROM RESERVATIE r
WHERE (r.datum = p_ReservatieDatum)
AND (r.begin_tijd <= p_EindTijd)
AND (r.eind_tijd >= p_BeginTijd)
)
)
ORDER BY t.code
) t
WHERE ROWNUM = 1;
-- Insert into RESERVATIETAFEL table
INSERT INTO RESERVATIETAFEL (FK_RESERVATIEID, FK_TAFELID)
VALUES (v_ReservatieID, v_TafelID);
v_AssignedTafels := v_AssignedTafels + 1;
END LOOP;
END IF;
IF v_AssignedTafels = v_RequiredTafels THEN
-- RAISE_APPLICATION_ERROR(-20005, 'procedure succesfull!');
APEX_APPLICATION.G_NOTIFICATION := 'Procedure successful! All ' || v_RequiredTafels || ' tafels have been assigned.';
END IF;
END;


I could use an application error as I did with other errors, but that doesn't feel right because it clearly looks like an error message instead of a successful one.

Is there a way to redirect the page only when the procedure is successful? I don't think this is possible with SQL alone.

Continue reading...