Introduction

Last but not least tip no. 10

#10 Two ways to fetch the current records

This will be the last blog in the series for now, but no less interesting than the others :-)

In this entry, we want to show how you can fetch the records of an interactive grid in two different ways.

The first method is using a JavaScript function, as the second method we employ a PL/SQL function. Both are easy to implement. You can determine your approach depending on what you want to do with the data. Personally, I prefer PL/SQL and avoid JavaScript code whenever it´s possible but every developer thinks differently here :-)

Let's have a closer look at how we can do this.

Fetch records via JavaScript

We start with a new application or page where we will add an interactive grid. In our example we use the table "EMP" as source. Then we give the Interactive Grid a Static ID, for example "my_ig". In our case we only want to fetch the values of the "EMPNO" column and save them in a page item. This is a very basic example, but it suffices to show how it works. Next we need a page item that we call "P1_EMPNO_JS". When that is done, we add a Dynamic Action and set the following:

• Event: 'Page change (Interactive Grid)'

• Selection type: 'Region'

• Region: 'The Interactive Grid you add to the page'

The Dynamic Action is fired every time the selection of the Interactive Grid changes. We can also use a button or some other DA event. The final thing we need is a 'True' action that executes JavaScript Code. Here we enter the following JavaScript function:

var l_empno;
// fetch the model for the interactive grid
var model = apex.region("my_ig").widget().interactiveGrid("getViews", "grid").model;

// loop through the records
model.forEach(function(igrow) {
   if(l_empno == null){
       // first row without seperator ":"
       l_empno = igrow[model.getFieldKey("EMPNO")];
   }
   else {
       // add seperator ":" to each additional row
       l_empno = l_empno + ':' + igrow[model.getFieldKey("EMPNO")];
   }
}
);

// Set Page Item
apex.item( "P1_EMPNO_JS" ).setValue (l_empno);

What we are doing here is getting the model of the Interactive Grid, and looping through each row and writing the value of "EMPNO" to a variable. At the end we store the result of the variable in a page item.

That´s it ;-)

Fetch records via PL/SQL

In our second example we use the same Interactive Grid as before. We need a second Page Item, which we call "P1_EMPNO_SQL", and another Dynamic Action, which, as in the previous example, is also fired on a "Page change (Interactive Grid)" event. For the DA we need a 'True' action that executes PL/SQL code and enter the following:

declare
    l_region_id number;
    l_context   apex_exec.t_context; 
    l_empno_ids number; 
    l_empno     varchar2(1000 CHAR);
begin
    -- Get the region id for the IG
    select region_id
      into l_region_id
      from apex_application_page_regions
     where application_id = :APP_ID
       and page_id        = :APP_PAGE_ID
       and static_id      = 'my_ig';

    -- Get the query context for the IG
    l_context := apex_region.open_query_context (
                        p_page_id => :APP_PAGE_ID,
                        p_region_id => l_region_id );

    -- Get the column positions for column(s)
    l_empno_ids := apex_exec.get_column_position( l_context, 'EMPNO' );

    -- Loop through the query of the context
    while apex_exec.next_row( l_context ) loop 
        if l_empno is null then
            -- first row without seperator ":"
            l_empno := apex_exec.get_number( l_context, l_empno_ids );
        else    
            -- add seperator ":" to each additional row
            l_empno := l_empno || ':' || apex_exec.get_number( l_context, l_empno_ids );
        end if;    
    end loop;

    -- Set Page Item
    :P1_EMPNO_SQL := l_empno;

    -- Close query context
    apex_exec.close( l_context );
exception
      when others then        
        apex_exec.close( l_context );
      raise;
end;

What we're doing here is the same process as before in our JavaScript function. We get the query context of the Interactive Grid, loop through each row and write the value "EMPNO" to a variable. Some APEX APIs are used to achieve this, which will not be described in further detail here, but we are going to have a closer look at them in future blogs, so keep an eye out.

Conclusion

The examples here are very basic but show what can be achieved with a bit of customization. You can do a lot this way, for example fetching the records of the IG in order to insert or update data in this or another table. Using the JavaScript approach, it is possible to make your page more interactive, for example to show or hide regions/items/buttons based on the selected data.

Feel free to let us know if these tips and tricks helped you.

If you have questions or suggestions, please leave us a comment ;-)

Here is the demo app for reference.

References

• https://docs.oracle.com/en/database/oracle/application-express/20.2/aexjs/grid.html

• https://docs.oracle.com/en/database/oracle/application-express/20.2/aeapi/APEX_REGION-OPEN_QUERY_CONTEXT-Function.html#GUID-BDB9F4B7-D1A7-4C9A-B4C7-45A57AD76427

• https://docs.oracle.com/en/database/oracle/application-express/20.2/aeapi/APEX_EXEC.html#GUID-3CF1D2DD-AEA4-4982-9857-548567AB7169