Patching to APEX 4.2.1

I found the patch for APEX 4.2.1 to be easy, as usual - but I've come out with one question.

When in the APEX builder, keep an eye out for this region on the right hand side - if you haven't heard about a patch through the copious amounts of blogs or social media, then this is on place you might see the prompt.

As described on the APEX download page, you can either download the entire product; for English only; or if you're already on 4.2.0, you can download the 66mb patch set from My Oracle Support - patch #14732511

The patch set notes can be found here, or as part of your download.

My first step to upgrade the instance on my Windows 7 laptop was to terminate APEX Listener with Ctrl-C

Now open a command prompt, change current folder to where the patch is, connected to SQL*Plus and ran the install script
cd \apex4.2\patch
sqlplus /nolog
conn sys as sysdba

Ten minutes later, I found no errors in the apxpatch.log although the log didn't capture the final output I saw in the command display.
It finished in the middle of
Application Express 4.2.1 patch
... lots of stuff
... Sample Access Control: Application ID 7600
... some more stuff
......region template 5047053830717713422
-- no more logs!

I opened f7600.sql, found the exact location but saw no reason why the log would stop.

Moving on, it came to updating the images directory when using Oracle APEX Listener, and for me it got a little obscure.

Ever since I can remember, this step involved copying the images folder to where the relevant web server sourced the documents, but the current documentation suggests creating a web archive.

If you are using the Oracle Application Express Listener, you need to create a Web Archive for Oracle Application Express images

I decided not to do this in my circumstance - stand alone APEX listener, with no WLS/Glassfish because the  documentation for this option didn't require it.

So I made a backup of my old images folder by renaming to /images4.2/

Then dragged /images from the patch folder to my apex4.2 directory.

Now time to restart the APEX Listener using my batch file on the desktop, which runs
cd c:\apex_listener2\
java -jar apex.war standalone --apex-images \apex4.2\images

I also checked my older post regarding APEX 4.1 upgrade to see if there was something I missed.

Opening the localhost:8080/i/apex_version.txt showed the patched version, so no harm done?

My question - under what circumstances would you need to create the web archive - and what advantage/functionality does it provide?

Take care when importing applications from 4.2 to 4.2.1 - you will need to re-apply the theme (see patch notes)
It will also pay to read notes regarding jQuery Mobile 1.2.0, which is part of this patch - there are some interesting widgets to play with.

Now I very much look forward to the new year, upgrading my current client from 4.1 so we can start a project building an application for a tablet.

Scott

Grassroots blog self reference map

I've published a page you'll find linked in the top menu to a list of articles I've posted on this blog.
grassroots-oracle.com/p/blog-reference-map.html

It's not all of them, and I can never promise it will be up-to-date - of course a google search also works
(or the little Search Me widget in the side-bar)

Let me google that for you

I will be using it as a reference page for myself - quick browser search to find content I know I have and need some syntax from.

I've had this drafted for a long time, but for some reason it wouldn't publish.  I had a moment of inspiration and just re-created the page.

Scott

Review: Oracle APEX Best Practices

November 2012 was the launch of a new APEX book from Packt publishing. Kudos to authors Learco, Iloon & Alex.

I'd like to offer the following review.

Oracle APEX Best Practices

Front page - title & byline to the point; three author contribution - nice to keep things balanced.  I'd probably buy it without looking at the contents. Sometimes it's a shame not to still discover these books for the first time on shelves.

Ubiquitous scenic photograph included - that I'm sure the authors had no influence on ;-)

Chapter 1, Prepare and Build

Not just your obligatory first chapter on setting up APEX - it covers plenty of best practices just in this first chapter. It doesn't go into a huge level of detail - it's not going to be the one-stop-shop for all information, but awareness is key. For this reason it is actually quite a large chapter!

Some highlights:
web server choices; multiple application framework; page design ideas based on table configuration

Chapter 2, Leveraging the database

Instrumentation is what every application should have - the APEX team includes some by default, and the database data dictionary views are also considered instrumentation. This chapter first covers Tyler Muth's logger package - although I would have liked to have read some use case scenarios to really seal the deal.

It then breaks down how to speed up your lookup tables with hash clusters and/or index organised tables - don't sound too scared. It does scratch the surface and explains it fairly well, but I recommend further research.

I think analytic functions are a little like learning to ride a bike. The authors do fairly well in explaining what they're all about, then provide some good examples - especially a cool one combining row_number() and pivot, and another on the essential string aggregate listagg(). In the caveat section, they show an example of analytic overkill - using row_number() to identify duplicates when "group by" will do. However, there is an advantage in using analytics if you need to identify the primary keys as well.

The authors then cover group by features such as grouping & rollup/cube.
Quality goes on with describing how to use scheduled jobs to encapsulate longer running jobs the user does not need to wait for; then looks at another favourite of mine - pipelined functions.
The chapter finishes with a look at ordsys for image processing & context indexing for document content searching just like google.

Best. chapter. ever.

To illustrate how useful & ubiquitous this chapter is, I already used most of these features in a recent APEX project - instrumentation, pipelining, analytics, ordsys, jobs.

Some highlights (I think I knew what these were going to be just by reading the summary):
instrumentation, analytical functions, grouping, pipelined functions, image proccesing, context indexing

Chapter 3, Printing

The most common question with the least definitive answer.

It quickly turned into a reference manual installation guide, but why not? - it explains the process well. There are good nuggets of information in this chapter, just depends on what you're looking for: BI Publisher, Apache FOP, Oracle Reports, Cocoon, JasperReports & two plug-ins, Reports to PDF & Embedded PDF.

My only complaint - there was only a brief note on the 32k limit for report columns. This means images you source dynamically from the database must be less than 22k + message fluff. This is what I experienced in 4.1, and it would have been nice to have dedicated paragraph on the matter - but there is plenty on the web!

I spoke too soon!! Writing this as I went along, I came to a brilliant summary of the 32K limit.

Some highlights:
incorporating images, Print API, suggested alternatives

Chapter 4, Security

Good intro, and great quote to start "Oracle Application Express is secure, but developers can make it insecure"

It covers functional settings, wide-ranging technical aspects - The first half covers administration aspects that you won't find in my APEX Security presentation - they didn't really have the sizzle a presentation needs ;-)

The second half covers the tools a developer needs to be aware of. It has great coverage for cross site scripting (XSS). For added humour, the authors referenced an XKCD comic to illustrate SQL injection. A few authorisation aspects not normally covered were in there. A couple of third party suggestions added to the end.

Some highlights:
VPD, APEX Listener, XSS

Chapter 5, Debugging & Troubleshooting

Starts with good detail on debugging in APEX. Later some browser extensions are mentioned - but I prefer the inspection tools native to Chrome.
It has only a small section on error handling, but really covers or at least mentions everything an APEX developer should be aware of.

Some highlights:
remote debugging, JavaScript console wrapper

Chapter 6, Deploy and Maintain

Another topic that probably needs more good published content, and the authors again do a good job.

Given the quality of the other chapters, I was looking forward to a deeper discussion regarding application IDs, particularly related to Interactice Reports.
They cover feedback well, but miss migrating it between environments.
They also mention my favourite APEX activity report though - Weighted Page Performance.
In regard to pro-active monitoring, I think error handling should have been be covered - with this we could log errors and actively notify the development team before the user picks up the phone to support.

Some highlights:
Version control, Feedback

Appendix, Database cloud service and APEX 4.2

The appendix, while probably vestigial in humans, here includes some interesting supplementary information on the Oracle Cloud Service, RESTful web services & a super quick mention of the data load feature in SQL Workshop.

Summary

Buy this book. Perfect information if you are intermediate to advanced, but what should be a required read for all APEX technologists. Kudos to the authors & publisher.

Past reviews

In the past I've reviewed other Oracle APEX books
Oracle Application Express Forms Converter
Oracle APEX 4.0 Cookbook
I've been technical review for some others, which I should also write up a light review for the blog.

Scott

APEX 5 Desires

No doubt about it, APEX 5 could be the opportunity to really think number 5 is alive! If you missed it, I commented on the statement of direction. It's a good list, and I still think the APEX product team have their fingers on the pulse regarding what the community needs & wants.

The APEX 4.2 wish list was certainly a success when it came to encouraging the community to speak out with what they wanted. There were many features suggested, all with varying degrees of complexity in regard to fitting in with the current data model & infrastructure. I have drafted a retrospective post to analyse some hits & misses.

Oracle now provides a dedicated application to listen your APEX feature requests. There is a similar one for SQL Developer.

APEX Feature Request application

I'm interested in something a little broader. I'm asking if the APEX community could have one major change applied to APEX 5, regardless of what the poor elves in Oracle development need to do under the hood to make it happen, what would it be? With the introduction of mobile applications with user interfaces, the bar has been raised.

What big change would you like to see in APEX 5?

Eight ideas to get your brain juices flowing:

1. A major restructure of Interactive Reports, offering more control with UI options, sharing reports, migration
2. Bigger, more dedicated leap into responsive web design for at least 3 devices, offering more features
3. A super easy to integrate, supported, WYSIWYG reporting engine/development tool
4. Remotely integrated migration engines operating over a database link
5. Built-in version control, perhaps with super-changed build option features
6. A simpler (than plug-ins) transparent porting facility to allow developers more easily integrate jQuery plug-ins, typically for menus & lists
7. More cross-application features, particularly the management of subscriptions
8. More integrated use of page aliasing, optionally applied "instead of" page numbers, and of course :APP_PAGE_ALIAS

As I did last time, I mentioned this on the OTN APEX forum - but I encourage you to use the Oracle's feature request application to manage these ideas, allow the community to help assess their viability & popularity, and help see some into fruition.

You'll get more awareness for these ideas using the application than relying on post 42 on 18 pages of posts, but it would be nice to see your big ideas repeated as comments/forum posts regardless to help seed activity.

The APEX development team are are listening.

Scott

Thursday Thought: Pacifism

Of course the many forms of internet media are rife with commentary on the tragedy of Sandy Hook, and this morning after reading a relatively objective article by someone I admire - Steve Novella, and I felt compelled to add an observation on the taboo, gun control.

I'll start with the reason I find Steve's opinions worthy, particularly those relating to his medical profession - the and the fact he seems to know facts about everything!
When he starts assessing gun control:

I just want to make the point that it is reasonable to address this issue, the discussion should be evidence-based, we currently need more and better evidence, meanwhile we can make some rational decisions based on the evidence we have.

I think it comes down to attitude. Another piece of the puzzle to my epiphany:

US attitude to gun control

I think many Australians (a culture I'm familiar with) think less guns all around, less chance of something horrible occurring. All firearms must be licensed for particular uses only and kept in a secure safe. Notably, with the exception of Northern Ireland, UK police force don't carry guns.

So I take this to a logical conclusion - each side always needs the bigger gun. You end up with a cold war, which lead to humanity realising it must stop or reach mutually assured destruction.
Conflict in the regions around the world continue, sustained often by revenge or vengeance, or other similar retaliation.

Call me a pacifist (a word I learnt watching the classic Hunt for the Red October), but can't we do this to the lowest denominator we can go? Trained properly, police could defend themselves from most crime incidents with a three or six foot wooden pole.

On a lighter note, if you are interested I encountered a small piece from PZ Myers inspired by Victoria Soto praising teachers around the world for their contribution to human interest.

Finally, both Steve & PZ are on distant wings ends on the spectrum of secularism. I dare not mention some of the horrid things I saw on the ends of other spectra, perhaps I just don't understand them.

What turned out to be a rant, over.

The problem with Interactive Reports

Migrating APEX applications between environments can be a real pain, especially when it comes to Interactive Reports.

Oracle gurus Joel, David & Martin have previously commented on this, links in references below - but I'd like to describe a situation we found that may help others.

Our problem

We have an APEX application that migrated periodically, and we didn't maintain a specific application ID during the migration process - a method that I find makes migration simpler.

At one point we had users begin to use the IR saved reports feature, so we had to now make sure these reports were preserved during migration.

When migrating an application to prod considering behaviours described in David's post, our save reports were still lost. This was ultimately due to the fact the application in development was copied (snapshots of old versions) since users started using saved reports - so our application ID and hence region IDs were different.

We considered the issues raised in Joel's post regarding the apex_application_install API and offsets for metadata IDs, but it didn't help in our case.

We got a little inspiration from Martin's post, but our situation seemed a little different.

Our solution

We imported the production application with the saved private reports to development.

Then I updated the core table to align the flow_id to match, similar to Martin's suggestion - and in hindsight I see were I crossed the wires.

update apex_040100.wwv_flow_worksheet_rpts
set flow_id   = 108 -- current version in dev
where flow_id = 200 -- from production
and page_id   = 301 -- IR page
and session_id is null
and report_alias is null;

Note: updating tables owned by the APEX user not supported by Oracle

When we migrated the application we were still losing the saved reports, so my SAGE colleague Kate Marshall found solution inspired by this OTN Forum post

Just like Martin said, we still had to match the interactive report region_id to the worksheet children.
Hopefully this screenshot paints the picture, where the IDs in green needed to match, as do the application ID.

Saved IR meta-data

We initially missed the region_id -> worksheet_id, and if I recall that left us with orphaned records after the migration.

Once updated, we then migrated the application from development, including the saved reports we copied from production.

So now when migrating subsequent updates to the application, we don't include private IR reports from development - and it doesn't overwrite/lose whatever is in production.

Wish list

I would like to see the APEX development team improve this process regarding saved reports, perhaps similar to the way questions are asked during import/export regarding supporting objects.

Our users would also like the ability to share saved reports to selected groups of people. I wonder if logistics would allow this for either authorisation schemes, APEX user groups, or some other mechanism.

If you have your own feature requests - there's an app for that

Good references

David Peake - Preserving saved interactive reports
Joel Kallman - Where did my saved interactive reports go?
Martin Giffy D'Souza - Saving saved interactive reports when updating application

Managing APEX using APIs

You can still manage much of your APEX workspace from the command line - whether that be SQL*Plus, SQL Developer, or some other tool of choice.

They key thing is, not everything needs to be point and click - that feels a little important.

I like to frequently blow away & re-create a bunch of users, workspaces & applications in one foul swoop. I'd like to share a little of what I've done since it was a topic of discussion recently at the Perth APEXposed.

To use the APIs below, your schema will require the APEX_ADMINISTRATOR_ROLE, described by Martin here.

To create & define all the training accounts, I use the following procedure in a package I compile in my parsing schema.
Privileges are a little different when encapsulated in a package, so direct EXECUTE access on APEX_INSTANCE_ADMIN would be required to avoid the infamous PLS-00201. Permissions such as CREATE USER would also be required.

PROCEDURE define_users
  (p_nbr_users  NUMBER)
IS
  lc_user  VARCHAR2(10);
BEGIN
  << define_users >>
  FOR i IN 1..p_nbr_users LOOP

    lc_user := 'train'||i;

    << drop_user >>
    DECLARE
      e_no_user exception;
      pragma exception_init(e_no_user, -1918);
    BEGIN
      EXECUTE IMMEDIATE 'DROP USER '||lc_user||' CASCADE';
    EXCEPTION WHEN e_no_user THEN
      NULL;
    END drop_user;
    EXECUTE IMMEDIATE 'CREATE USER '||lc_user||' IDENTIFIED BY '||lc_user;

    EXECUTE IMMEDIATE 'ALTER USER '||lc_user||' QUOTA 100M ON users';
    EXECUTE IMMEDIATE 'GRANT CONNECT TO '||lc_user ;

    EXECUTE IMMEDIATE 'GRANT CREATE SYNONYM TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE VIEW TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE PROCEDURE TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE TYPE TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE SEQUENCE TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE TRIGGER TO '||lc_user;
    EXECUTE IMMEDIATE 'GRANT CREATE TABLE TO '||lc_user;

  END LOOP define_users;
END define_users;

All my other procedures just encapsulate the following steps and example API calls. I highly recommend specifying formal parameter names as the signature to these APIs often between APEX versions.

My procedure to define my workspace makes calls

APEX_INSTANCE_ADMIN.REMOVE_WORKSPACE(lc_user,'N','N');

Create the workspace

APEX_INSTANCE_ADMIN.ADD_WORKSPACE
  (p_workspace_id   => ln_workspace_id
  ,p_workspace      => lc_user
  ,p_primary_schema => lc_user
  ,p_additional_schemas => '')

A new call required for 4.1.1, found by Dimitri, announced by Patrick

apex_instance_admin.enable_workspace;

Set the context regarding which workspace

apex_util.set_security_group_id(p_security_group_id => ln_workspace_id);

Create a number of users, with various developer privileges.

APEX_UTIL.CREATE_USER(
        p_user_name                     => 'ADMIN'
       ,p_web_password                  => /* my attempt to keep generic accounts passwords unmentionable */
       ,p_email_address                 => 'username@sample.com.au'
       ,p_developer_privs               => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL'
       ,p_default_schema                => lc_user
       ,p_allow_access_to_schemas       => lc_user
       ,p_change_password_on_first_use  => 'N');

Then before installing an application, you need to set the workpace and schema

APEX_APPLICATION_INSTALL.SET_WORKSPACE_ID(p_workspace_id);
APEX_APPLICATION_INSTALL.SET_SCHEMA(p_schema);

Then you can either nominate the application id or have one generated

APEX_APPLICATION_INSTALL.SET_APPLICATION_ID(p_app_id);
-- or
APEX_APPLICATION_INSTALL.GENERATE_APPLICATION_ID;

You may also choose to set such properties as application name and alias

APEX_APPLICATION_INSTALL.SET_APPLICATION_NAME (p_app_name);
APEX_APPLICATION_INSTALL.SET_APPLICATION_ALIAS(p_app_alias);

Then you generate an offset for all the IDs in the export script

APEX_APPLICATION_INSTALL.GENERATE_OFFSET;

If you're creating multiple applications for one workspace, all you need each time is

APEX_APPLICATION_INSTALL.GENERATE_APPLICATION_ID;
APEX_APPLICATION_INSTALL.GENERATE_OFFSET;

To execute these APIs among calls to import previously exported applications in SQL*Plus (wasn't that a mouthful), I used

set define '^'
accept my_app DEFAULT 'C:\my_everything\f_123_example.sql'

-- set app context
exec apex_application_install.generate_application_id;
exec apex_application_install.generate_offset;
-- install app from export
@^my_app
-- rinse and repeat

And that's essentially how I do it, just bundled up to suit my needs. It'll be pretty easy for you to grab what you need and sort out your own scripts.

Scott

Webinar reminder - APEX Mobile

Just a quick reminder about a coming webinar on APEX mobile on 18th/19th December.

If the time zone suits you, it would be wonderful if you could join in

• US west coast - noon
• US east coast - arvo
• Europe/Africa - PM
• Australia east cost - AM
• Australia west coast - too early, and you probably saw it at the Perth Conference ;-)
• any other suitable regions I neglected to mention, my apologies

Register via ODTUG.

Scott