Friday, 21 June 2013

Performing DML operations on the resulting IR data

Around the time of 4.2 coming out, I blogged about the APEX_IR API, which enables us to get the report query. At the time, I demonstrated nothing more than rendering the resulting query, and listing all the bind variables. 

Well, now comes time to put this to use. The scenario. Updating a particular column for all the rows that have been returned from the IR filters and what not. Previously, to do this, I would typically have a hidden item by utilising the APEX_ITEM.HIDDEN API, along with the using the APEX_APPLICATION.G_F0X array in an on submit page process. The downside with this (or upside, depending how you look at it) is it is only good for the displayed results.

So firstly, it's important to enforce that some key field is included in the report - so that we can accurately update the data. In an example i've set up, I'm including the ID, then in the column attributes unticking the option that would enable them to hide the column.


At the top of a page, I have a HTML region with a simple select box with the values I'd like to be able to update all rows to - :P19_COL5.

Then we can have a process the uses the APEX_IR.GET_REPORT API: http://docs.oracle.com/cd/E37097_01/doc/doc.42/e35127/apex_ir.htm#BABEFDJE

My on submit process is looks a little something like this:

declare
    FETCH_COUNT constant NUMBER := 10;
    cur NUMBER;
    res NUMBER;
    number_tab dbms_sql.number_table;
    description_tab dbms_sql.desc_tab;
    l_col_count NUMBER;
    
    l_region_id apex_application_page_regions.region_id%type;
    l_report apex_ir.t_report;
begin
    select region_id into l_region_id
    from APEX_APPLICATION_PAGE_REGIONS 
    where page_id = :APP_PAGE_ID
    and application_id = :APP_ID
    and source_Type = 'Interactive Report';
    
    l_report := apex_ir.get_report(
        p_region_id => l_region_id
    ,   p_page_id => :APP_PAGE_ID);
    
    cur := dbms_Sql.open_cursor;
    dbms_sql.parse(cur, l_report.sql_query, dbms_Sql.native);
    
    for i in 1..l_report.binds.COUNT LOOP
        dbms_sql.bind_variable(cur, l_report.binds(i).name, l_report.binds(i).value);
    END LOOP;
    
    dbms_sql.describe_columns(cur, l_col_count, description_tab);
    
    for i in 1..l_col_count LOOP
    
        if description_tab(i).col_name = 'ID' then
            dbms_Sql.define_array(
                c => cur
              , position => i
              , n_tab => number_tab 
              , cnt => FETCH_COUNT
              , lower_bound => 1);

            res := dbms_Sql.execute(cur);

    
            LOOP
                res := dbms_sql.fetch_rows(cur);
                dbms_sql.column_value(cur, i, number_tab );
                exit when res != FETCH_COUNT;
    
            END LOOP;
    
    
            
        end if;
    
    END LOOP;
    
    
    dbms_Sql.close_cursor(cur);
    
    
   
    for i in number_tab.FIRST..number_tab.LAST LOOP
    
       update tabular_test set col5 = :P19_COL5 where id = number_tab(i);
    
    END LOOP;
    
end;

You can check out a working demo here: http://apex.oracle.com/pls/apex/f?p=14882:19

Some points to take note with regards to DBMS_SQL usage. If the column you are defining is a varchar, you would use c_tab instead of n_tab and dbms_sql.varchar2_table instead of dbms_sql.number_table. I don't think the required paramater name is entirely clear in the docs: http://docs.oracle.com/cd/E11882_01/appdev.112/e16760/d_sql.htm#i1025685

Also, when describing the columns, if you want to check the data type, the column type is returned as a BINARY_INTEGER. I couldn't seem find any oracle docs mapping data type to an int, only this blog: http://askanantha.blogspot.com.au/2007/09/dynamic-ref-cursor-with-dynamic-fetch.html

1 comment:

  1. I know this is from a few months ago, but I have a question in regards to your usage of the APEX internal get_report function and the query that it returns...

    Have you had any luck with this with aggregate rows on your interactive reports? Why I am asking is, I have a client who is using this function within a package to return Excel style files from their interactive reports, and finding the query returned is returning Extra columns with the aggregate values added to each row of the data returned.

    APEX seems to be adding the columns on to th eouter sql select to get them displayed versus just letting occur as they do in the report.

    I can post an example on my hosted instance if you would like top see an example of this..


    Thank you,

    Tony Miller
    texas.apex.developer@gmail.com
    LuvMuffin Software
    Ruckersville, VA

    ReplyDelete