Search code examples
sqlplsqloracle-apex

Oracle APEX, PL/SQL validation: How to validate colon separated values from shuttle list in Form against column values stored in table for date range

I am trying to validate shuttle list values on a form with colon separated values that are stored in a table column along with date range (From and To date) columns in Oracle APEX. All the tables, form and interactive report are created in Oracle APEX but still providing detailed overview in the post as per the rules. Sharing Oracle APEX workspace details at end of this post.

Have below table for month calendar: Table Name: ACCT_CAL

+----+-------------+-------------+--------+
| ID | FROM_DATE   | TO_DATE     | STATUS |
+----+-------------+-------------+--------+
| 1  | 01-Jan-2023 | 31-Jan-2023 | C      |
+----+-------------+-------------+--------+
| 2  | 01-Feb-2023 | 28-Feb-2023 | C      |
+----+-------------+-------------+--------+
| 3  | 01-Mar-2023 | 31-Mar-2023 | C      |
+----+-------------+-------------+--------+
| 4  | 01-Apr-2023 | 30-Apr-2023 | O      |
+----+-------------+-------------+--------+
| 5  | 01-May-2023 | 31-May-2023 | F      |
+----+-------------+-------------+--------+
| 6  | 01-Jun-2023 | 30-Jun-2023 | F      |
+----+-------------+-------------+--------+
| 7  | 01-Jul-2023 | 31-Jul-2023 | F      |
+----+-------------+-------------+--------+
| 8  | 01-Aug-2023 | 31-Aug-2023 | F      |
+----+-------------+-------------+--------+
| 9  | 01-Sep-2023 | 30-Sep-2023 | F      |
+----+-------------+-------------+--------+
| 10 | 01-Oct-2023 | 31-Oct-2023 | F      |
+----+-------------+-------------+--------+
| 11 | 01-Nov-2023 | 30-Nov-2023 | F      |
+----+-------------+-------------+--------+
| 12 | 01-Dec-2023 | 31-Dec-2023 | F      |
+----+-------------+-------------+--------+

Assuming we are in the month of April 2023, then only that month status will be Open (O). Past months will have status Closed (C) and future months with status (F).

Shuttle List values: Table Name: TKT_DETAILS

+---------+
| TKT_NUM |
+---------+
|    10   |
+---------+
|    20   |
+---------+
|    30   |
+---------+
|    40   |
+---------+
|    50   |
+---------+
|    60   |
+---------+
|    70   |
+---------+
|    80   |
+---------+
|    90   |
+---------+
|   100   |
+---------+

Source SQL query for Shuttle List:

select TKT_NUM d, TKT_NUM r from TKT_DETAILS
order by TKT_NUM

Below is the table where assignment of tickets takes place for any period Table Name: TKT_ASSIGN

+----+----------+-------------+-------------+-------------+
| ID | TKT_NUM  | ASSIGNED_TO | FROM_DATE   | TO_DATE     |
+----+----------+-------------+-------------+-------------+
|  1 | 10:20:30 | USER1       | 01-Apr-2023 | 30-Jun-2023 |
+----+----------+-------------+-------------+-------------+
|  2 | 10:60    | USER2       | 01-May-2023 | 30-Sep-2023 |
+----+----------+-------------+-------------+-------------+
|  3 | 50       | USER1       | 01-Aug-2023 | 31-Aug-2023 |
+----+----------+-------------+-------------+-------------+
|  4 | 60:70    | USER1       | 01-Sep-2023 | 30-Nov-2023 |
+----+----------+-------------+-------------+-------------+

Assuming records are inserted in the order of ID column.
From the above table, tkt_num column is the shuttle list values. If ID=1 is the first record inserted, then ticket number 10 should not be assigned for ID=2 since From date of ID=2 overlap with date range of ID=1 From Date and To Date.
ID=3 is valid
ID=4 is invalid since ticket number 60 is assigned for ID=2 and the date range ID=4 overlap with date range of ID=2.
Also TO_DATE field can be null.

Below is the validation - Function Body (returning Error Text):

declare
v_cnt   number;
begin

SELECT count(*) into v_cnt
FROM TKT_ASSIGN
where (exists (select * From apex_string.split(:P2_TKT_NUM,':') 
where instr(tkt_num, column_value ) > 0 ) or :P2_TKT_NUM is null)
and ((:P2_FROM_DATE BETWEEN FROM_DATE AND TO_DATE)
OR (:P2_TO_DATE BETWEEN FROM_DATE AND TO_DATE)
or nvl(:P2_ID,0) <> ID);

    if v_cnt <> 0 then
        return 'Ticket already assigned to User for the date range';
    end if;
        return null;
end;

Below are the issues I'm having with validation.

  1. If I edit ID=4 and remove tkt_num 60 from shuttle list, I'm still getting validation error fired.
  2. For ID=3, if tkt_num 20 or 70 is added it should allow, since there is no overlapping date range for the rows ID=1 or ID=4 but still I get validation error.

Hence seeking experts help and advice.

Please find Oracle APEX details below:
Workspace: RICHADEMO
Username: DEMO
Password: dem01234

APP_ID: 83310

Thanks,
Richa

Solution

  • Thanks for the very complete sample case. The issue is the where clause:

    Change

    and ((:P2_FROM_DATE BETWEEN FROM_DATE AND TO_DATE)
OR (:P2_TO_DATE BETWEEN FROM_DATE AND TO_DATE)
or nvl(:P2_ID,0) <> ID);

    to

    and ((:P2_FROM_DATE BETWEEN FROM_DATE AND TO_DATE)
OR (:P2_TO_DATE BETWEEN FROM_DATE AND TO_DATE))
AND nvl(:P2_ID,0) <> ID;

    The current row can never be included so that needs to be an AND separated from all other predicates.

    Steps to debug this:

    1. Copied the select statement
    2. Added debug messages in the validation pl/sql to get the values of all page items on page submit (without having to search for them :) ).
    apex_debug.info(
    p_message => q'#kl debug: P2_TKT_NUM: %0#',
    p0        => :P2_TKT_NUM);
apex_debug.info(
    p_message => q'#kl debug: P2_FROM_DATE: %0#',
    p0        => :P2_FROM_DATE);
apex_debug.info(
    p_message => q'#kl debug: P2_TO_DATE: %0#',
    p0        => :P2_TO_DATE);
apex_debug.info(
    p_message => q'#kl debug: P2_ID: %0#',
    p0        => :P2_ID);

    1. Run page in debug, look for those debug messages, replace the values that are there in the sql statement

    2. Run the sql statement in SQL Command and debug.

    A couple of notes:

    1. The data structure - storing a comma separated list in a column isn't a good practice. You cannot join to that column. It's advised to store one row per assigned ticket. This will make your code a lot easier to maintain, your sql a lot simpler and save you a lot of headaches. This validation would be a lot simpler if it was stored this way.
    2. The validation sql. The statement instr(tkt_num, column_value ) > 0 ) is a bug. If you have ticket number 2 and 22 it will give a false positive because INSTR('20:30','2') > 0 yields true but is not what you want.
    3. Format your code properly.

    Here is how I would do it.

    Type: No Rows returned Source:

    SELECT 1
FROM TKT_ASSIGN
WHERE
   (EXISTS (
      SELECT 1 FROM apex_string.split(:P2_TKT_NUM,':') t
       WHERE t.column_value IN 
               (SELECT column_value 
                  FROM apex_string.split(tkt_num,':')) 
             OR :P2_TKT_NUM is null)
   )
   AND ((TO_DATE(:P2_FROM_DATE,'DD-MON-YYYY') BETWEEN FROM_DATE AND TO_DATE)
        OR (TO_DATE(:P2_TO_DATE,'DD-MON-YYYY') BETWEEN FROM_DATE AND TO_DATE)
       )
   AND NVL(:P2_ID,0) <> ID