Success message for an end user inside an SQL procedure

Question

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.

Answer 1

Add an OUT parameter to the procedure "AssignTable2" and populate a page item with it. Then in the "Success Message" attribute of the page process, reference the page item.

Example:

This example only uses an OUT parameter but you should get the point.

pl/sql procedure

create or replace procedure SO_MESSAGE_TEST
(
    P_SUCCESS_MESSAGE_O OUT VARCHAR2
)
as
begin
    P_SUCCESS_MESSAGE_O := 'Success message from pl/sql !';
end SO_MESSAGE_TEST;
/

page process

P274_SUCCESS_MESSAGE is a hidden page item on the page.

result on the UI

Outside the scope of this question, but the page could use validations in addition to the RAISE_APPLICATION_ERROR calls. That is more "the apex way". For the posted code, there would be 4 validations. If any of the validations fail then the process would not be invoked. Event with the validations in place, the RAISE_APPLICATION_ERROR calls could just remain in the code for an extremely rare race condition where data changes in the milliseconds between the validation execution and the page process.