Friday, 12 December 2008

'Publish to APEX' from SQL Developer 1.5.3

Since SQL Developer 1.2.1 and APEX 3.0.1, we've had some useful integration between APEX and SQL Developer, the ability to import and deploy applications, browse the APEX metadata, remote debug PL/SQL in your applications and more. With the latest release of SQL Developer 1.5.3, it is now possible to create a quick and simple APEX application from within SQL Developer (thanks to a bug fix). This is possible through the 'Publish to APEX' feature and creates a simple 1 page APEX application containing an Interactive Report Region based upon a SQL statement.

(Note: Requires APEX 3.1 or above.)

This feature allows you to take any grid of data, right click on it and select to 'Publish to APEX'. (Note a 'grid of data' includes results from executing a SQL statement, results of pre-defined or user-defined reports from the 'Reports' tab, specific table / view properties such as columns, data, constraints etc. and I'm sure there are more.) Upon selecting 'Publish to APEX', the following dialog is displayed:

This dialog allows you to specify 5 properties:
1) Workspaces- The workspace where you want the application to be created (this list will only display workspaces that are associated with the schema of the context of your current grid of data).
2) Application Name - The name of the application that will be created.
3) Theme - The theme for your new application, specifying look and feel.
4) Page Name - The name of the page that will be created.
5) SQL - The SQL that will be used to generate an interactive report region within the page. This defaults to the SQL used to build the original grid of data, but can be changed.

Upon clicking 'Apply' SQL Developer will create the application and show a dialog with some basic creation information such as application name and ID. This creates a 1 page application in the workspace defined, containing an interactive report region with a source of the SQL specified in the dialog.

A few more points to note about this application:
- The application's authentication scheme defaults to 'Database Account Credentials' meaning that you'll need to authenticate into the application using valid database account username and password. This can obviously be changed to something else if required.
- The application will be created in an application group called 'Published from SQL Developer'.
- The interactive report region only displays the first ten columns of the report by default, but again this can easily be changed via the interactive reports menu bar 'Actions' green cog drop down, then select 'Select Columns'.

Here is an example of the application that is generated. I selected to publish data that showed access attempts to my workspace (selecting from the APEX metadata view apex_workspace_access_log). I then used interactive report features to refine my data to show all the failed login attempts for applications within a workspace, grouped by application:

Have fun publishing to APEX!!!

Friday, 29 August 2008

Out Now!! Application Express 3.1.2

APEX 3.1.2 is now available for download and fixes the following bugs. You can get hold of it by either:
  • Downloading the full version from OTN.

  • Download the patchset 7313609 from METALINK.
If your unsure about which download you need, please read Joel's blog. As Joel says, if your upgrading from any APEX version pre-3.1, then you'll need to use the full OTN release (which includes the version that comes pre-installed with 11gR1).

For details on future plans, please take a look at our Statement of Direction, which was updated just under 3 weeks ago with details of the 'Forms Converter' of APEX 3.2 and 'Websheets', 'Updateable Interactive Reports', 'Extensible Item Framework' and much more of APEX 4.0.

Happy patching!

Thursday, 7 August 2008

APEX with eBusiness Suite survey reminder

Readers of the APEX blogosphere will already know, we are currently conducting a survey in an attempt to better understand how people are using Application Express with Oracle eBusiness Suite / Oracle Applications. For example, have you implemented a killer reporting module with our 'Interactive Reports' to empower your users to get better information from their eBusiness Suite application data? Have you had other integration successes?

If so, this is just a quick reminder to anyone who hasn't yet completed David Peake's single page survey, please if you can take a moment to fill this in and tell us your thoughts, we would much appreciate it. Information provided is completely confidential, unless you state otherwise.

Thank you!

Wednesday, 16 July 2008

Dynamic Date Filtering in Interactive Reports

Update: Please note, in Application Express 4.0, it is now possible to define a 'Row Filter', where you can reference SYSDATE directly from the filter. For further information, please see the 'Adding a Row Filter' section of the user guide.

Whilst the filtering part of interactive reports currently supports a fair number of options, there isn't currently the facility to specify a dynamic date filter using SYSDATE. A common use case for this would be a dashboard report where you want to display all the overdue items in a dataset. In the interactive report filter definition, it would look something like this...

But unfortunately, we do not currently support the use of SYSDATE in the Expression field. So what can you do? I will discuss 3 possible workarounds for achieving this.

Note: Method 1 does not require any changes from a developer perspective, methods 2 and 3 require some developer work.

Method 1
Use the 'Is in the last' operator, and then specify some arbitrarily large amount (100 years).

This could then be saved by the user as a 'Named Report' for future use. Although this is the quickest and easiest method, it isn't very intuitive for the user to come up with and also it's not 100% water tight to use this filter, as it might be possible that a date is entered outside of the arbitrary range specified.

Method 2

It's possible to specify filters on interactive reports via the URL. This uses the 'IR%' prefix in the 'Item Names' argument in the URL syntax, as detailed in the documentation. This method involves dynamically provisioning a link to the interactive report page and could be implemented by creating a SQL report to look like a menu, like this:

1) Add a region of type 'SQL Report' and for the 'Region Source', specify:
select sysdate from dual
2) Ensure the following is set during the wizard creation:
- Report Template: template: [theme number].Borderless
3) Go the 'Report Attributes' page and specify the following:
- Pagination Scheme: No pagination.
- Untick the 'Show' checkbox for the SYSDATE column.
- Click 'None' for 'Headings Type'.
3) Add a column link to the report by going the 'Report Attributes' page and clicking the 'Add Column Link' link from the 'Tasks' menu on the right. For this column link, specify the following values:
- Link Text: 'Overdue Reviews'
- Page: 2
- Clear Cache: '2,CIR'
- Item 1 Value: #SYSDATE#
Note: My interactive report is on page 2 of my application, but change all occurences of 2 to to whatever your page is.

This generates a link like this:

Of particular interest here are...
  • Clear Cache = '2,CIR'
    This clears the cache for page 2, and also uses the new 'CIR' parameter, which clears all settings that could already have been applied to the interactive report (including any default report settings).

  • Item Names = 'IRLT_REVIEW_DATE'
    This can be broken down as follows:
    • 'IR': This is used to set interactive report filters dynamically over the URL.

    • 'LT': The characters directly after the 'IR' prefix are equivalent to the 'Operator' value in the interactive report 'Filter' dialog, so in this case 'LT' specifies a 'Less than' operator. Note: You could also use 'LTE' which would specify a 'Less than or equal to' operator. See here for more options.

    • 'REVIEW_DATE': Then after the underscore, this is the column name on which to apply the filter, equivalent to the 'Column' value in the interactive report 'Filter' dialog. In this case we are filtering on the 'REVIEW_DATE' column.

This method works well, but does require some sort of separate menu or link to go to the interactive report, which steps out of the normal use-case for interactive reports.

Method 3
The final method requires the developer to code a derived column to calculate if the record is overdue in the report SQL. A snippet something like:
when review_date < sysdate then 'Yes'
end) review_overdue
Once this has been done by the developer, the user could then choose to apply a filter on this column as shown in the screenshot:

This could then be saved by the user as a 'Named Report' for future use.

Note: With methods 1 and 3, the developer could also default these reports for users, saving the report as 'Default Report Settings' in the 'Save Report' dialog. This means that when any user logs into and sees this report, they will default to these filters. One consideration here though, when the developer saves the 'Default Report Settings', you cannot specify a 'Name' for the report, so the user would see the filter as it is coded without a named tab.

So method 3 would be my preferred choice, because it works and I believe provides the most user friendly solution for the users whilst keeping within the standard use-case for interactive reports. One consideration of this approach though would be that no index would be used on that column (see comments).

A couple of questions for the community...
  • Would you find it useful to be able to filter by SYSDATE?

  • Would you find it useful to be able to filter not just by a value, but by another column value?

  • What else would you like to see incorporated into Interactive Reports to make them even better?

Further Reading...
General overview and information on interactive reports
Marcie Young's Advanced Interactive Report Tutorial
APEX documentation page on dynamic interactive report filtering via the URL
David Peake's (APEX PM) blog post on 'Advanced Interactive Reports

Thursday, 10 July 2008

New job...

Just a quick note to say I've recently made a commitment to Application Express and joined the APEX development team here at Oracle. I am very excited to work for a cracking team on a great product and look forward to blogging much more about it in the future.


Sunday, 10 February 2008

Dynamic Quick Picks - APEX style!

Have you ever admired the quick pick links that appear on some of the APEX pages under select lists. For example editing the 'Display As' property of a page item you can quick pick common display types, as shown here:

So, I wanted to implement this same feature in my own applications, only dynamically generating the quick pick options based on data. For example, in the standard EMP table there is a column called Job. I wanted to display 2 quick picks for this field, based on the top 2 occurences of values for jobs in the EMP table.

Step 1) Firstly we need a generic database function to handle rendering the links. Here is the code:
create or replace
( p_query VARCHAR2
, p_item VARCHAR2) AS
  TYPE cur_type IS REF CURSOR;
  cur cur_type;
  v_display VARCHAR2(4000);
  v_return VARCHAR2(4000);
  l_query VARCHAR2(1000);
  l_query := p_query;
  --get any binds and replace with the value from session
  l_names := WWV_FLOW_UTILITIES.GET_BINDS(l_query);
  FOR i IN 1..l_names.COUNT LOOP
  l_query := REPLACE( LOWER(l_query),
                      '(select v('''|| 
                        LOWER( LTRIM(l_names(i), ':')) ||
                        ''') from dual)');
  OPEN cur FOR l_query;
    FETCH cur INTO v_display, v_return;
    HTP.ANCHOR('javascript:setValue(''' || 
                p_item || ''',''' || 
                v_return || ''');',
    '[' || v_display || ']',
  CLOSE cur;
So this procedure takes a SQL query (p_query) and a page item ID (p_item) as parameters. The results from the query specified in p_query will be used as the display and setting values of each quick pick link, so the query must contain two columns in the select statement (the first selected column should be the display value to be used and the second the return value, see query below for an example). The item name specified in p_item will be the item that the quick pick link will set.

This procedure just opens a dynamic cursor based on the query specified in the p_query parameter. Then calls the HTP.ANCHOR function to generate the anchor HTML tag. The procedure also pads the lot in a div tag and adds a line break at the end for presentation (you may want to change this if you have another page item on the same line as the select list being set). The following is an example of the HTML generated by this procedure:
  <a class="itemLink"
  <a class="itemLink" 

Step 2) Secondly, we need to call this function in APEX. You can do this by adding a page item with the following settings:
Name > Name: [whatever you want, P1_QUICK_PICK_JOB would be fine for this]
Name > Display As: Display As Text (based on PL/SQL, does not save state)
(Note: This display type is not available when creating a page item, so it must be set after creation.)
Displayed > Sequence: [just after the select list item you wish the quick pick link to set]
Label > Label: [blank]
Label > Template: No Label
Source > Source Type: PL/SQL Anonymous Block
  QUICK_PICK('SELECT  summed_rows.job display
              ,       summed_rows.job return
                      FROM  (SELECT    job,
                                       SUM(1) qty
                             FROM      emp
                             WHERE     job IS NOT NULL
                             GROUP BY  job
                             ORDER BY  SUM(1) DESC
                             ) summed_rows
                      WHERE  ROWNUM    <= 2'
                    , 'P2_JOB');
This example uses a query that selects the top 2 occurences of job values in the emp table. The second parameter indicates that the page item 'P2_JOB' will be set by the quick pick link.

Step 3) So finally, there is one more thing to do, create the css used by the link. I have copied the inbuilt class used by APEX to render these links and re-specified it in its own 'custom.css' file. You can do this by creating a file with the following contents:
a.itemLink:link {
margin:0pt 5px 5px 0pt;
Then save this as 'custom.css' and upload it to APEX via 'Shared Components > Cascading Style Sheets'. Finally to make it available to the application, we need to reference this in the page template, so add the following line to the 'Definition > Header' code, after the default theme css declaration:
<link rel="stylesheet" href="#WORKSPACE_IMAGES#custom.css" 
      type="text/css" />

The results can be seen here:

So that's it. Then in order to add more quick pick links to other select items, just create other page items referencing the QUICK_PICK database procedure as detailed in step 2.

Limitation: Currently the css doesn't work in IE, only Firefox, so the links just display as normal text. When I find out why and will post an update.

Hope it helps,

Following a discussion on the APEX forum regarding this post, I have improved the QUICK_PICK procedure so that it can handle a query containing bind variables. This is making use of the in-built function WWV_FLOW_UTILITIES.GET_BINDS(), with a technique proposed in the forums here.

Another update...
Please note, if you are working in APEX 4.0 or above, the item type used to display the quick pick links has changed slightly, as part of a wider item consolidation that took place in the 4.0 release. You should use the 'Display Only' item type, with a 'Setting > Based On' attribute equal to 'Output of PL/SQL Code' and then define the call to the QUICK_PICK procedure in the 'PL/SQL Code which emits HTML' attribute. If you have written this in a release prior to 4.0, then when upgrading you are automatically migrated to use these new settings.

Sunday, 27 January 2008

UKOUG - My presentation slides are now available...

Just a quick post to mention my slides from my UKOUG presentation, 'Building The Rich User Interface with Oracle Application Express and Ajax' are now available to download.

Sorry about the delay for anyone who wanted to take a look at this sooner, I wanted to tidy up some of the code before making it available and have only just had a chance to look at it.

If you are interested in catching this again, I will be doing a very similar presentation at the UKOUG Combined SIG Day at Baylis House in Slough on 27th February.

So come along and say hi!