Wednesday, 31 July 2013

Customising a charts legend label to reference a page item

I am generating a report (Graph) based on a date field. The requirement is for the legend label to display the data beside the legend label. This leads me on the AnyChart documentation.

http://www.anychart.com/products/anychart/docs/xmlReference/index.html
http://www.anychart.com/products/anychart/docs/users-guide/legend-text-formatting.html

First, you need to specify the legend attribute ignore_auto_item to True.

If you don't do this part, whilst the item will be found (below), the format won't be applied, despite matching successfully.

Then, you can define custom formats for each series. In my case, I have two series (Current Report and Previous Report). So, inside the legend node, add the following for each series:

<items>
    <item source="Series" series="CURRENT REPORT">
        <format><![CDATA[{%Icon} Current Report - &P22_CURRENT_REPORT.]]></format>
    </item>
    <item source="Series" series="PREVIOUS REPORT">
        <format><![CDATA[{%Icon} Previous Report - &P22_PREVIOUS_REPORT.]]></format>
    </item>
</items>

The result:






Sunday, 21 July 2013

Accessing the last request value from a page submission

When you submit the page, you will typically have page processes that run. For me, I use these request values to determine which processes are run. This is rather straight forward for on submit processes as you can just specify the condition request = Expression 1

Or, if you have multiple requests that link to this process, Request is Contained within Expression 1, and Expression 1 would be a comma delimited set of values.

Request simply refers to the bind variable :REQUEST (or apex_application.g_request).

If we try and do the same on a page rendering process, we no longer have access to this value - which makes sense.

If you update the branch, to set the request portion of the URL, we will then be able to access the bind variable :REQUEST in all page rendering processing points.




























This has the obvious downside that if the user wants to reload the page,  clicks in the address bar and hits enter (or clicks refresh), the processes on that condition will run again.

It also doesn't seem to give us access to the request in javascript which we may want to use in a dynamic action of some sort.

The accepted solution seems to be just to create your own hidden item to store the value in and use that. One important tip however is to clear the value after you've used it, so that the processes don't continually get called when refreshing the page.

So, have an on submit process that runs PL/SQL with something similar to:

:P1_HIDDEN_ITEM := :REQUEST;

Then in your on load dynamic action, after all the logic you implement, issue the following to clear the session state:

$s('P1_HIDDEN_ITEM','');
apex.server.process('', { pageItems: ['P1_HIDDEN_ITEM']}, {dataType: 'text'});

If you wanted a re-usable solution, you could do this.

Create a hidden region on global page, and add a hidden item (P0_SUBMITTED_REQUEST)

Create two application processes:

On Submit: After Page Submission - Before Computations and Validations, a with process text:

:P0_SUBMITTED_REQUEST := :REQUEST;

Specify sequence as 1 so it's the first process run when submitting the page.

And then another:

On Load: After Footer (page template footer)

:P0_SUBMITTED_REQUEST := NULL;

Specify a high enough sequence that it will run after all other sequences. e.g. 1000 should be high enough.

Then in your page page rendering processes (well, any processes really), you can specify a condition that Value of Item / Column in Expression 1 = Expression 2:


Then in JavaScript (assuming you call it before the value is cleared) you can access the value with $v('P0_SUBMITTED_REQUEST'). This works with an on load dynamic action as the code is called before the page has completed i.e. calling the process which clears the value.

You can refrain from clearing it, however I prefer clearing it as if the user reloads the page, the value will still be lingering and all processes will be re-called.

For another useful solution when dealing with checksums, check out Recx's blog: http://recxltd.blogspot.co.uk/2013/07/oracle-apex-url-checksums-and-jquery.html

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

Friday, 10 May 2013

Global list region conditions

Quite often then not, I'm adding a list region onto the global page (page 0), and specifying the condition: Current Page Is Contained Within Expression 1 (comma delimited list of pages).

The trouble with this is that each time you add a new list entry, you then need to go back to page 0, edit the region conditions, and update the list of pages. Then if you delete an entry from the list, you need to remember to go and update it.

It's just occured to me there has to be a simpler way where you can just query the data dictionary to get the pages list, and check if it exists in that.

When you specify the target type as 'Page in this application', you will notice that the column 'entry_target' returns the URL (or similar): f?p=&APP_ID.:14:&SESSION.::&DEBUG.:::: where 14 is the page you specified.

So, we can get a list of pages the list relates to with a query similar to the following:

select regexp_substr(apex_application_list_entries.entry_target, 'f\?p=&APP_ID\.:(\d+).*', 1, 1, 'i', 1) page_id
from 
    apex_application_lists
    join apex_application_list_entries on (apex_application_lists.list_id = apex_application_list_entries.list_id)
where apex_application_lists.list_name = 'Experimental'

That is, get the page number with the above regular expression. Obviously substituting apex_application_lists.list_name = 'Experimental' for the actual list name.

So then, on our region on page 0, we can change the condition type from: Current Page Is Contained Within Expression 1 (comma delimited list of pages) to Exists (SQL query returns at least one row).

Then specify a query similar to the following:

select 1
from dual 
where :APP_PAGE_ID in (

    select to_number(regexp_substr(apex_application_list_entries.entry_target, 'f\?p=&APP_ID\.:(\d+).*', 1, 1, 'i', 1)) page_id
    from 
        apex_application_lists
        join apex_application_list_entries on (apex_application_lists.list_id = apex_application_list_entries.list_id)
    where apex_application_lists.list_name = 'Experimental'
)
or :app_page_id = 9

In this example, I've also added page 9 to the condition - as that is an index page that never actually appears in the list.

So now, whenever you add a new entry to the list, the list region will appear without going to update the comma delimited list of pages.

Wednesday, 20 March 2013

A word of caution when using an SQL report with hidden items on the same page as a tabular form

tl;dr: if you are going to have a tabular form and an sql report on the same page with a hidden item, don't hide it using column attributes.

I've set up the following page to document the behaviour: http://apex.oracle.com/pls/apex/f?p=45448:23:11484095074702:::::

Basically, when you have a tabular form, elements are given a name attribute such as f01. Then, any processes can reference these elements. 

The trouble is, I have a SQL report, and I want to have the name column a link, which uses the ID column. I also don't want to leave the ID column showing, so I set it to hidden in column attributes.



Now if I try to delete a row in the tabular form, I will run into the following issue:



Now if I look at the source of the people report, I will see the hidden item has also named that field the same way as the tabular form, which will be causing a conflict for the MRU,MRD processes, and any custom process that uses the apex_application.g_f0x array.

The better solution that setting it to hidden in column attributes, is to leave the display as in column attributes to Display as text, and in column definition, set show column to No.




What this does is not have it in the report as a hidden input field, thus no need to set the name attribute of the input field. The downside of using this technique is it doesn't output the field into the page source, but I can't really see any need for this as we just have it there to build the link - unless you needed it in there for some dynamic action.