sqloracle-apex

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.


Solution

  • 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.

    enter image description here

    result on the UI

    enter image description here

    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.