Interactive Grid:Process with PL/SQL only that isn't based off a table

Question

Env: Oracle APEX v5.1 with Oracle 12c Release 2

Firstly, I have created an Interactive Grid that isn't based off an underlying table as I will process this manually using PL/SQL.

I have been using the following as a guide:

https://apex.oracle.com/pls/apex/germancommunities/apexcommunity/tipp/6361/index-en.html

I basically have the following query:

select  
  level as id,  
  level as grid_row,  
  null as product,  
  null as product_item  
from dual connect by level <= 1

Concentrating on just the product and product_item columns where the product_item column will be a readonly column and only the product number can be entered, I would like to achieve the following:

Product    Product Item
---------- -------------
123456     123456-1
123456     123456-2
556677     556677-1
654321     654321-1
654321     654321-2
654321     654321-3
123456     123456-3

From the above, as the user types in the Product and then tabs out of the field, I would like a DA to fire that will add the sequence of "-1" to the end of that product number. Then is the user then adds another row within the IG and enters the same product number, I then want it to append "-2" to the end of it.

Only when the product changes number, I need the sequence to reset to "-1" for that new product as per 556677 and so forth.

Other scenarios that should also be taken into consideration are as follows:

From above IG, the user entered 123456 again but this should calculate that the next sequence for 123456 is "-3"
The same needs to be catered for, when a Product is removed from the IG but to always look at the max sequence number for that product.

I was thinking of possibly using APEX_COLLECTIONS as a means of storing what is currently in the grid, since no changes have been committed to the database.

There does not appear to be a question (although there are lots of statements about your intentions and thoughts). It would help if you clarified exactly what you are asking for (or what code you have and what the errors are). — MT0
@MT0 - I am asking for assistance with how best to generate and maintain sequences within the product_item based on product value entered and to also take care of the two scenarios mentioned. Hope this is somewhat clearer. — tonyf

MT0 MT0 · Accepted Answer · 2019-11-12T12:43:42

Assuming you have a collection of product values (in this case, I am using the built-in SYS.ODCINUMBERLIST which is a VARRAY data type) then the SQL for your output would be:

SELECT id,
       id AS grid_row,
       product,
       product || '-' || ROW_NUMBER() OVER ( PARTITION BY product ORDER BY id )
         AS product_item
FROM   (
  SELECT ROWNUM AS id,
         COLUMN_VALUE AS product
  FROM   TABLE(
           SYS.ODCINUMBERLIST(
             123456,
             123456,
             556677,
             654321,
             654321,
             654321,
             123456
           )
         )
)
ORDER BY id

Output:

ID | GRID_ROW | PRODUCT | PRODUCT_ITEM
-: | -------: | ------: | :-----------
 1 |        1 |  123456 | 123456-1    
 2 |        2 |  123456 | 123456-2    
 3 |        3 |  556677 | 556677-1    
 4 |        4 |  654321 | 654321-1    
 5 |        5 |  654321 | 654321-2    
 6 |        6 |  654321 | 654321-3    
 7 |        7 |  123456 | 123456-3

db<>fiddle here

Interactive Grid:Process with PL/SQL only that isn't based off a table

2 Answers