Oracle APEX - How to populate interactive grid using APEX collection

Question

I am new to developing application using oracle apex. Please excuse me if this question seems very trivial in nature.

In a nutshell, I am trying to invoke a REST service programmatically to populate an interactive grid on oracle apex page. Here's what I already tried.

1. Created a page that has a button to invoke a process.
2. The process invokes a REST service to get all order lines belonging to a particular order. The sample response from the REST service is as below

    {
            "items": [{
                "HeaderId": 300100550016803,
                "FulfillLineId": 300100550016806,
                "SourceTransactionLineId": "1",
                "SourceTransactionLineNumber": "1",
                "OrderedQuantity": "10",
                "OrderedUOM": "Each",
                "RequestedFulfillmentOrg": "Vision Corporation"
            },{
                "HeaderId": 300100550016803,
                "FulfillLineId": 300100550016807,
                "SourceTransactionLineId": "2",
                "SourceTransactionLineNumber": "2",
                "OrderedQuantity": "15",
                "OrderedUOM": "Each",
                "RequestedFulfillmentOrg": "Seattle Manufacturing"
            }]
        }

3. If the rest service invocation was successful (http status code: 200), then I create the apex_collection as below in the same process. Also, I have set one of the page fields (P3_REFRESH_ORDER_LINES_GRID) to ‘Y'. On page load, the value for this attribute must be null.

if apex_web_service.g_status_code = 200 then --OK 
        dbms_output.put_line( 'Response : ' || l_data ); --if response was OK, print it
        apex_collection.create_or_truncate_collection( 'OrderLines' );
        apex_collection.add_member(
            p_collection_name => 'OrderLines',
            p_clob001 => l_data );
        
        :P3_REFRESH_ORDER_LINES_GRID := 'Y';

    end if;

4. I have then used the below SQL query to populate data into the interactive grid Set the region type to “Interactive Grid” Source: Local Database Type: SQL query

    SELECT ol.fulfill_line_id as FulfillLineId, ol.quantity as Quantity
    FROM APEX_collections c, 
    JSON_TABLE(
            c.clob001,  -- the following lines depend on your JSON structure
           '$.items[*]' 
            columns(
                fulfill_line_id number path '$.FulfillLineId',
                quantity number path '$.OrderedQuantity')
        ) ol
    WHERE c.collection_name = 'OrderLines';

5. Then, I have setup a dynamic action on the page item (Its a hidden text field)

- P3_REFRESH_ORDER_LINES_GRID
- Dynamic Action name : RefreshGrid
- When: Event Name: Change
- selection type: Item
- Item - P3_REFRESH_ORDER_LINES_GRID
- Client side condition - Type: Item is not null
- Item - P3_REFRESH_ORDER_LINES_GRID
- True condition: Action: Refresh, selection type: Region, Region: Order Lines (Name of the region containing the IG)

After I click on the button to invoke the rest service to fetch the order lines, the interactive grid does not display any data. Can you suggest where am I going wrong here?

Answer 1

Potential Issue(s)

• Step 1 You didn't really specify how your button works. If it is submitting the page, you may have problems with P3_REFRESH_ORDER_LINES_GRID field remaining null.

  • You could have Server-Side conditions preventing the invocation or on Page-Load you may be resetting P3_REFRESH_ORDER_LINES_GRID to null, and Step 5 will NOT trigger.

• Step 3(Most Likely Issue) If you are not submitting the page, and you just have a Dynamic Action, executing Server-Side code: you may have forgotten to include P3_REFRESH_ORDER_LINES_GRID in the Items to Return.

  • Your P3_REFRESH_ORDER_LINES_GRID flag will remain null and Step 5 will NOT trigger.

To Debug

1. You can try debugging your page here by checking Session under Developer Tools. I would make sure each step work and executes however I intend it to execute.
2. Also leverage APEX_DEBUG in my PL/SQL
3. Open your Browser's Developer Tools and look under Console. You should see Dynamic Action Fired lines there if Step 5 is being triggered at all!

As you have not shared how your debugging went, and other observations after clicking the button, such as:

• Was the collection created?
• Is there data in the collection?
• What's the value of P3_REFRESH_ORDER_LINES_GRID

You should be able to see what the answers are for the above using Session.