Sunday, August 19, 2018

Automating service creation in Oracle APEX

Oracle Academy is a division in Oracle whose mission is to advance "computer science education globally to drive knowledge, innovation, skills development, and diversity in technology fields."  The programs that Oracle Academy offers are free to accredited secondary schools, technical/vocational schools, and two and four-year colleges and universities.

Oracle Application Express (APEX) has been hosted by Oracle Academy and used for many years to facilitate the delivery of curriculum for database design and programming, SQL, PL/SQL and even APEX.  To facilitate their business requirements, they had custom extensions written (in part, by our team) into the core APEX product.  But this type of solution becomes difficult to maintain, because every release of APEX requires migration and rewrite of these custom extensions.

A number of months ago, I met with the Oracle Academy team to try and encourage them to move the service creation of APEX workspaces into their own custom interfaces.  I told them that everything is provided to easily and programmatically create APEX workspaces, create database users (schemas), create administrators and developers within each workspace, and even pre-load APEX applications and custom database objects in each workspace, at the time of service creation.

Naturally, they asked for an example script to accomplish all of these tasks, which I authored and shared with them.  Because this type of logic is very relevant for many other purposes, e.g., testing, continuous integration, DevOps, I wish to share these same annotated scripts here.  If you are someone who has been using APEX for 10 years, you won't learn anything new here.  But for those just getting started with APEX, I hope you find it fruitful.

Starting with an empty Oracle database, the requirements are to develop a single script which will:

  1. Create a tablespace (storage) for each workspace
  2. Create and associate multiple database users (schemas) with each workspace
  3. Create an APEX workspace
  4. Create an administrator and developer account in each workspace, restricting the access of each developer to only one of the schemas mapped to the workspace.
  5. Create custom database objects in each schema
And here we go...
set define '^' verify on
set concat on
set concat .

Rem
Rem    Title:  Demo_of_Provisioning.sql
Rem
Rem    Description:  This script will demonstrate use of all of the APIs and SQL statements necessary
Rem                  to create tablespaces, data files, database users, and APEX workspaces.  From this
Rem                  example, it is assumed that the Academy team will be able to develop their own custom
Rem                  provisioning process and no longer rely upon custom extensions to the 
Rem                  Oracle Application Express software.
Rem
Rem    Notes:  It is assumed that this script is run as user SYSTEM.
Rem
Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem    jkallman    08/19/2018 - Created


column foo1 new_val LOG1
select 'Demo_of_Provisioning_'||to_char(sysdate,'YYYY-MM-DD_HH24-MI-SS')||'.log' as foo1 from sys.dual;
spool ^LOG1

timing start "Create demonstration tablespaces, schemas and workspaces"


--
-- Predefine the path used for tablespace datafile creation.  If you're using Oracle Managed Files
-- or grid infrastructure, then this isn't necessary and you'll remove the 'datafile' portion of 
-- the CREATE TABLESPACE statements
--
define DATAFILE_PATH='/u01/app/oracle/oradata/'


--
-- Step 1:  Create the Tablespaces
--
-- Since Oracle Academy will want to group multiple database users/workspaces into a single tablespace, we'll need to
-- first create the tablespaces, and then the database users in step 2
--
create tablespace OACADEMY_DATA1 datafile '^DATAFILE_PATH.iacademy_01.dbf' 
 size 50M autoextend on next 50M maxsize 2G extent management local 
 autoallocate segment space management auto;

create tablespace OACADEMY_DATA2 datafile '^DATAFILE_PATH.iacademy_02.dbf' 
 size 50M autoextend on next 50M maxsize 2G extent management local 
 autoallocate segment space management auto;



--
-- Step 2:  Create the Database Users (Schemas)
--
-- It's up to you how you wish to group database users/schemas into different tablespaces.  
-- You can do it by total number or free space or ratio of users to tablespaces.  It's all up to you.
--
begin
 -- DB users in DATA1 tablespace
    execute immediate 'create user OACADEMY_DB10 identified by "' || sys.dbms_random.string('X',30) || 
                      '" default tablespace OACADEMY_DATA1 quota unlimited on OACADEMY_DATA1 temporary tablespace TEMP account lock password expire';

    execute immediate 'create user OACADEMY_DB11 identified by "' || sys.dbms_random.string('X',30) || 
                      '" default tablespace OACADEMY_DATA1 quota unlimited on OACADEMY_DATA1 temporary tablespace TEMP account lock password expire';

 -- DB users in DATA2 tablespace
    execute immediate 'create user OACADEMY_DB20 identified by "' || sys.dbms_random.string('X',30) || 
                      '" default tablespace OACADEMY_DATA2 quota unlimited on OACADEMY_DATA2 temporary tablespace TEMP account lock password expire';

    execute immediate 'create user OACADEMY_DB21 identified by "' || sys.dbms_random.string('X',30) || 
                   '" default tablespace OACADEMY_DATA2 quota unlimited on OACADEMY_DATA2 temporary tablespace TEMP account lock password expire';

end;
/



--
-- Step 3:  Create the APEX Workspaces
--
-- Create the APEX workspaces and associate a default schema with each
--
begin
    apex_instance_admin.add_workspace(
     p_workspace_id   => null,
     p_workspace      => 'OACADEMY1',
     p_primary_schema => 'OACADEMY_DB10');
end;
/

begin
    apex_instance_admin.add_workspace(
     p_workspace_id   => null,
     p_workspace      => 'OACADEMY2',
     p_primary_schema => 'OACADEMY_DB20');
end;
/


--
-- Step 4:  Add additional schemas to the existing workspaces
--
--
begin
    apex_instance_admin.add_schema(
     p_workspace      => 'OACADEMY1',
     p_schema         => 'OACADEMY_DB11');
end;
/

begin
    apex_instance_admin.add_schema(
     p_workspace      => 'OACADEMY2',
     p_schema         => 'OACADEMY_DB21');
end;
/


--
-- Show a quick summary of the workspaces and schemas
--
column workspace_name format a50
column schema         format a40
select workspace_name, schema from apex_workspace_schemas;



--
-- Step 5:  Create an administrator account and a developer account in each worskpace
--
--
begin
    -- We must set the APEX workspace security group ID in our session before we can call create_user
    apex_util.set_security_group_id( apex_util.find_security_group_id( p_workspace => 'OACADEMY1'));

    apex_util.create_user( 
        p_user_name               => 'BOB',
        p_email_address           => 'bob@bob.com',
        p_default_schema          => 'OACADEMY_DB10',
        p_allow_access_to_schemas => 'OACADEMY_DB10',
        p_web_password            => 'change_me',
        p_developer_privs         => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL' );  -- workspace administrator

        apex_util.create_user( 
        p_user_name               => 'JUNE',
        p_email_address           => 'june@june.com',
        p_default_schema          => 'OACADEMY_DB11',
        p_allow_access_to_schemas => 'OACADEMY_DB11',
        p_web_password            => 'change_me',
        p_developer_privs         => 'CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL' );  -- developer

    commit;
end;
/

begin
    -- We must set the APEX workspace security group ID in our session before we can call create_user
    apex_util.set_security_group_id( apex_util.find_security_group_id( p_workspace => 'OACADEMY2'));
    
    apex_util.create_user( 
        p_user_name               => 'ALICE',
        p_email_address           => 'alice@alice.com',
        p_default_schema          => 'OACADEMY_DB20',
        p_allow_access_to_schemas => 'OACADEMY_DB20',
        p_web_password            => 'change_me',
        p_developer_privs         => 'ADMIN:CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL' );  -- workspace administrator

    apex_util.create_user( 
        p_user_name               => 'AUGUST',
        p_email_address           => 'august@august.com',
        p_default_schema          => 'OACADEMY_DB21',
        p_allow_access_to_schemas => 'OACADEMY_DB21',
        p_web_password            => 'change_me',
        p_developer_privs         => 'CREATE:DATA_LOADER:EDIT:HELP:MONITOR:SQL' );  -- developer

    commit;
end;
/

-- 
-- Show a quick summary of the APEX users
--
column workspace_name           format a30
column user_name                format a20
column email                    format a25
column is_admin                 format a10
column is_application_developer format a10
select workspace_name, user_name, email, is_admin, is_application_developer from apex_workspace_apex_users order by 1,2;


--
-- Install custom database objects in each schema by simply running one more SQL scripts.  Note that the
-- script below is something that you author and maintain.  It will do all DML and DDL you have in it,
-- and we simply iterate through the schemas and run the script each time.
-- 
alter session set current_schema = OACADEMY_DB10;
@custom.sql

alter session set current_schema = OACADEMY_DB11;
@custom.sql

alter session set current_schema = OACADEMY_DB20;
@custom.sql

alter session set current_schema = OACADEMY_DB21;
@custom.sql

timing stop
spool off


And to complete the lifecycle, I also authored a simple SQL script which will cleanup everything created above - removing the APEX workspaces, database users and tablespaces.
set define '^' verify on
set concat on
set concat .

Rem
Rem    Title:  Demo_of_Cleanup.sql
Rem
Rem    Description:  This script will cleanup all objects created by script Demo_of_Provisioning.sql
Rem
Rem    Notes:  It is assumed that this script is run as user SYSTEM.
Rem
Rem            **** THIS SCRIPT IS DESTRUCTIVE **** - It will drop tablespaces, data files, workspaces, schemas, etc.
Rem
Rem
Rem    MODIFIED   (MM/DD/YYYY)
Rem    jkallman    08/19/2018 - Created


column foo1 new_val LOG1
select 'Demo_of_Cleanup_'||to_char(sysdate,'YYYY-MM-DD_HH24-MI-SS')||'.log' as foo1 from sys.dual;
spool ^LOG1


timing start "Remove all workspaces, schemas and tablespaces"

--
-- Step 1:  Remove the APEX Workspaces
--
--
begin
    apex_instance_admin.remove_workspace(
        p_workspace      => 'OACADEMY1' );
end;
/

begin
    apex_instance_admin.remove_workspace(
        p_workspace      => 'OACADEMY2' );
end;
/



--
-- Step 2:  Drop the database users
--
--
drop user OACADEMY_DB10 cascade;
drop user OACADEMY_DB11 cascade;
drop user OACADEMY_DB20 cascade;
drop user OACADEMY_DB21 cascade;



--
-- Step 3:  Drop the tablespaces 
--
--
drop tablespace oacademy_data1 including contents and datafiles;
drop tablespace oacademy_data2 including contents and datafiles;

timing stop
spool off

Wednesday, July 04, 2018

In the British APEX Community? Then UKOUG Tech18 is where you need to be!


In 2003, even before Oracle APEX had a real name (even before HTML DB), I had the good fortune of presenting APEX at the UKOUG Tech conference in Birmingham, England.  I was with my good friend Sergio Leunissen, the original Product Manager of APEX.  Tom Kyte had kindly given us a few minutes on stage during his session to present Project Marvel.  We were able to demonstrate marvel.oracle.com, a free, browser-based, hosted, multitenant, declarative (low code) development and runtime environment.  This ultimately became today's https://apex.oracle.com, which gets just around 2,000 new signups every week!

Later that day, I also distinctly remember talking with an Oracle sales rep from the UK.  I had explained that there were plans to include APEX (née Project Marvel) with Oracle Database and not charge any additional fee for it.  He told me he thought it was a big mistake that Oracle wasn't planning on charging a customer for use of APEX, and unless there was a cost associated with it, there would be no perceived value and no one would use it.  Here we are 15 years later.  The industry has changed in extraordinary ways, and APEX and the APEX community continues to grow and expand at an accelerated rate.  And in 2018, when everyone expects tools and frameworks to be "free", I remain convinced that we made the right decision to include APEX with Oracle Database and not make it a for-cost option.

I am looking forward to returning to UKOUG Tech18 this year, where a lot of the original APEX community outreach began for us.  Some of my favorite customers are in the United Kingdom, and I've known and worked with a large number of them for many years.  The fine folks at UKOUG have graciously given me the opportunity to present the APEX Community Keynote on Monday, December 3, 2018.  It will be "Oracle APEX: State of the Union", and this will be a presentation on both what is happening for APEX inside of Oracle and also across the APEX community globally.  We will be in Liverpool Sunday through Wednesday, and we look forward to meeting as many customers as possible.  You have a story to tell and we'd like to hear it, and how we can help.

See you in Liverpool in December 2018!

Friday, June 15, 2018

Jürgen Schuster: APEX Distinguished Community Member



I just got back from the ODTUG Kscope18 conference in Orlando, Florida where, once again, the global APEX community descended.  During the Sunday Symposium at this conference, I had the privilege of honoring Jürgen Schuster, who has been the catalyst and engine for so many positive things in this wonderful APEX community.

For the past few months, I was unsure of what words I could use to introduce this award to Jürgen, which accurately and humbly conveyed the breadth of his impact.  But the words just came to me late one evening in early May, and I used them almost verbatim during the Sunday Symposium.  I would like to share these words here, for everyone else in our global community to appreciate the impact he has had, along with the sacrifices he has personally made.

To quote Jürgen: live APEX and prosper.




ODTUG Kscope18 Conference
Sunday Symposium, June 10, 2018

The APEX Community is awesome, and it's awesome to be here and be a part of this conference. This is my 12th Kscope and I firmly believe that this is where the APEX community really got its start. Additionally, some of my greatest friends in the world are in this room, and I’m really grateful to be here.

There are really so many people who have made this community special:  from the many plug-in developers, people who record training videos, members who write blog posts and books, create presentations with technical content and share them, manage and organize meetups, organize entire conferences or technical days or user group meetings or organize tracks at Kscope!  There are those who create Web sites: builtwithapex.com, translate-apex.com, for example. There are open source sites dedicated to APEX and the Oracle Database. There are testing frameworks and security analysis tools.  And the list goes on and on.

Many of these people who have worked so hard on all of these contributions are here in this room today. But among the many people who have given of their time and talent to the APEX community, there is one person who stands out. And I have two words to describe this person: passionate and selfless.

This person has spent a lot of time and a lot of their own personal money to carry the message of APEX across the globe.  It's pretty extraordinary.

I'm sure everyone is going to know this person when I start citing some of their amazing work.

I’m sure you've seen these coveted APEX stickers before. It was the genius of this person to create a sticker for the APEX community. And instead of using some low-quality, cheap-looking, shoddy sticker, he opted for something classy, high-quality, enduring. At a personal cost of more than $1 per sticker, this person has supplied them and shipped them all over the globe to anyone who asks.  At his own personal expense.

The next challenge? This person, with the help of others in this room, created apex.world - the APEX community site - being your one-stop portal for all things related to the APEX community - Slack channels, jobs, plug-ins, news, newsletters, awards, tweets, and more.  There are more than 3,300 members today on apex.world.  It’s the central starting point for the APEX community.

How many of you know how to create your own podcast and host it on iTunes? I don't. Neither did this person. Out of his own pocket, he paid a professional to educate him and show him everything you need to know to prepare and publish on iTunes. And since that time, he has recorded, edited and published 20 episodes of the Oracle APEX Talkshow.

The contributions go on - ODTUG APEX On Air Webinars, APEX Meetup organizer, he created a web site dedicated to APEX dynamics actions, and on.  And almost all of these contributions has cost him his own money and he’s done it for no real personal gain.

Remember the two words I started with:  passionate and selfless.

The person I’m proudly referring to is Jürgen Schuster, an energetic and passionate freelance consultant from Munich Germany.  We wanted to recognize Jürgen and his many generous contributions to the community.

Please join me in congratulating Jürgen Schuster, as we proudly honor him with the first ever APEX Distinguished Community Member Award.




Tuesday, May 08, 2018

Is APEX Suitable for an Enterprise Setting?

The APEX 18.1 release has significant new capabilities to consume a variety of remote data sources, from ordinary REST data feeds to ORDS-based Remote SQL.  Up until APEX 18.1, database links were the predominant way to access remote data sources, and of course, database links don't exist in the cloud.  Improvement in this area has been a core focus of ours for APEX 18.1.

A long-time Oracle tools analyst and consultant recently published a backhanded compliment to APEX.  In a blog post, he said:
"Among Oracle tools, APEX has been the old-school, monolithic holdout, together with Oracle Forms. Much modern application architecture is based on REST web services, and other Oracle tools like JET, VBCS and ADF have long had the ability to consume and/or produce REST web services."
Before I go on, let's correct a few points.  Firstly, APEX has long had the ability to produce REST and consume both REST and SOAP Web Services for years.  I know, because I authored the first support for SOAP Web Services for APEX in 2002.  Also, you can't produce REST with JET.  It's a toolkit.  There is no back-end data store, no ability to "host" a REST Service.  The JET product managers themselves use RESTful Services from apex.oracle.com when doing their demonstrations of JET!  Lastly, Oracle JET was released in October 2015 and ABCS (now VBCS) was first announced in June 2015.  If that constitutes "long had the ability", then so be it.

So back to the statements - old-school, monolithic holdout.  Not modern.  In response to Morten Braten (a luminary in the APEX community), this consultant replied that "monoliths are rarely a good choice in an enterprise setting."  In response to my request for a definition of "enterprise setting", the consultant kindly authored a blog post stating why monolithic tools are bad for the enterprise.

One of his arguments against an APEX architecture is that "data must be committed to the database before it can be seen by anybody else", which I think is an odd conclusion to reach.  Last time I checked, most business applications deal with data.  And 30 years from now, the interfaces and access methods to your data will change 10 times, but you will still have...your data.  As long-time APEX expert Billy Verreynne ranted in 2005, "What does any business application deal with? DATA! That is the core. That is what drives the business. Applications come and go. Data is forever. Where does the data live? In the database. The database is the core. The database has been that since the 80's. Is still that. Focus on the core. Design for the core. Leverage the core."

I often tell people that the intersection point with APEX and many other technologies is the Oracle database - it's a wonderfully rich, very capable database and application development environment.  It's an engine with interfaces, just like the many boxes this consultant showed in his enterprise architecture diagrams.  Concurrency, transactional integrity, durability - these problems were solved in the Oracle database many years ago.  And as a bonus, you get zero latency data access for free!  Committing data to the database before it can be seen by anybody else should be considered a feature and not a deficiency.

Back to the term "enterprise setting", every enterprise, large and small, has a variety of application needs, from tactical to enterprise.  You could consider it on a scale like this:


At the bottom of the scale are completely simplistic, tactical applications.  These would be very easy to build, low in complexity, developed by one or two people, and often with a finite lifespan.  These are often opportunistic applications.  At the opposite end are enterprise applications.  These have large teams (10, 20 or more developers), a project manager, a dedicated budget, are high in complexity (and cost), and are truly mission-critical to the enterprise.

On this scale, where would APEX be an appropriate fit for a certain class of applications?  This is where I believe this consultant and I differ.  I believe APEX is ideal for the bottom 90% of this scale.  Sure, APEX can be and is used by customers for large ERP, HR and CRM systems serving thousands of end users, but the sweet spot for APEX is in the bottom 90% of this application scale.


Every enterprise has "gaps" in their corporate systems.  Oracle, "the information management company ©" has gaps.  I see it every day.  No corporate system or enterprise system can solve all problems for all business needs.  And the question is, how will you solve those problems, or will they remain unsolved?  Corporate architects prefer to have a blessed, supportable technology stack, but that stack is often times unapproachable to most developers.  Why do you think Excel has proliferated in the enterprise and continues to do so today?

The enterprise architecture that this consultant espouses is most likely perfect for legitimate enterprise applications.  But at what point on the scale is that architecture and associated technology stack unnecessarily complex or too costly for more tactical applications?  How many truly enterprise applications are there in an enterprise versus non-enterprise?  10 or 20 or 30 enterprise applications, versus hundreds if not thousands of non-enterprise applications?  I'll gladly pitch the benefits of APEX to solve the bottom 90% of this scale and the thousands of application needs which every large enterprise has.

At Oracle, I see this bottom 90% being solved with APEX every day, from applications which track hardware allocation & utilization to applications designed to manage the collateral associated with blockchain use cases to applications for submitting questions to the payroll team - the "bottom" 90% is very large, and the question is, how will you solve them?  With paper?  With a spreadsheet?  Or with a proven, scalable, low-code framework on the Oracle database that takes care of all of the important aspects of Web app development and lets you focus on the business problem to be solved?  That, my friends, is APEX.

Sunday, May 06, 2018

The top 3 reasons to attend malagAPEX!


The explosion and adoption of Oracle APEX continues across the globe, and clear evidence of this is the introduction of not one but two new all-APEX conferences.  At the end of May 2018 is the latest APEX conference, malagAPEX.  If you're looking for an opportunity to get connected with the growing APEX community, this is an excellent place to start.

Here are the top 3 reasons why you should consider attending malagAPEX:

  1. The collection of speakers they have assembled for this conference is extraordinary.  They are all highly-respected and well-known luminaries in the Oracle APEX and Oracle application development communities.  Many of them are Oracle ACEs, recognized experts by Oracle and community champions.  Appdev is what they do for a living, and they will share their real-world experience with you.
  2. The agenda and topics covered at this conference are cutting edge - cloud, Docker, machine learning, Oracle JET, REST and more.  And of course, there will be plenty of APEX too!
  3. The location is breathtaking.  For those who may not be familiar with Málaga (or if you're American!), it is located in southern Spain, in Costa del Sol (Coast of the Sun) at the northern side of the Mediterranean Sea.  It will be beautiful, sunny and hot.  After the winter many of us recently endured, it sounds idyllic.

As I recently posted on Twitter this past week, one of the best things about APEX is...the APEX community!  Others have told me this, and I believe it too.  A community member Denis Savenko recently blogged about his first time experience in the Oracle APEX community at a new conference, and he said "awesomeness was in the air - everybody was extremely positive and ready to share their thoughts when it came to a discussion of any sort of a problem. I met a lot of interesting people from different countries, improved my professional contacts list dramatically and also had a chance to speak to really important people."  I can't state it any better than that.  This is representative of the awesome Oracle APEX community.  If you're looking for an opportunity to get plugged in, consider malagAPEX.

Saturday, February 10, 2018

APEX Alpe Adria - A New APEX Conference That You Should Attend



Have you heard of APEX Alpe Adria?  It's the latest "All APEX" conference, joining APEX World and APEX Connect as high-quality conferences dedicated to Oracle APEX developers and related technologies.

APEX Alpe Adria is a very modestly-priced one-day conference occurring on April 20, 2018 in Graz, Austria.  The list of speakers that they have organized is unbelievably impressive - they are all very knowledgeable & experienced, as well as very polished presenters.  All presentations will be in English.  Also, the evening before the conference, there will be an Ask an ACE session, where you can ask any question to a panel of highly experienced Oracle ACEs.

APEX Alpe Adria is the creation of three different Oracle partners, Dario Bilić from BiLog d.o.o., Aljaž Mali from Abakus Plus d.o.o., and Peter Raganitsch from FOEX Gmbh.  As Peter told me, they are not organizing this conference for commercial purposes.  Instead, their primary goal is to bring a high-quality dedicated APEX conference to a region where there is significant demand.  And they've developed a 100% APEX Conference - everyone and everything is driven by Oracle APEX:  the conference Web site, abstract submission, abstract voting, the back-end administration apps, everything.  The conference is already a great testament of what is possible with the Oracle Database & Oracle APEX.

While the location primarily caters to Austria, Slovenia and Croatia, the appeal stretches far beyond the immediate region.  As you can see in the map below, Graz is a short 500 miles (1.5 hour flight) from many places in central Europe.  And even though it may be a bit longer to travel for those outside the circle, I'm hoping that this conference will attract many of our other customers and partners from Eastern Europe and the Middle East.  Tickets have already been sold to conference attendees from Austria, Czech Republic, Germany, United Kingdom, Croatia, Macedonia, Russia, Slovenia, and the USA.  If you want an easy way to get connected and immersed into the global APEX community, please attend APEX Alpe Adria.  It's that simple.

Patrick Wolf & Christian Neumueller from the Oracle APEX Development team will be joining me to kick off this inaugural conference and show our support for this growing APEX community.  We hope to see you there!


Wednesday, February 07, 2018

How do I Refresh a Region or Report after a Dialog is Closed?

I was going to write this blog post back in December 2017, as this question comes up frequently.  And even though there are numerous examples available in the Sample Database Application and other sample applications, and it's even in the documentation, the solution still isn't always readily apparent.

I received this question from an internal employee today:
"When I edit the page and update the row and save and close the modal page, existing report doesn't refresh the page with revised value.   It was working on another page, but not working on this new page (I built last week).  Couldn't find a way to refresh it as soon as I close the modal window with saved data."
He has a report and a form in his application, and the form opens up in a modal dialog.  He simply wants the report on the parent page to refresh after the modal dialog is closed.  Simple, right?



For anyone well-versed in APEX, you'll find this blog post superfluous.  But for everyone else who is looking for the quick explanation, as I've received this question 3 times in the past couple months, here it is.

  1. On the report page, create a new Dynamic Action and enter the following attributes:
    • Name: <provide a meaningful name>
    • Event: Dialog Closed
    • Selection Type: Region
    • Region:<choose your report region>

  2. Select the True action of the Dynamic Action and enter the following attributes:
    • Action: Refresh
    • Selection Type: Region
    • Region: <choose your report region>

That's really all there is to it.  If you have multiple regions on the parent page, you could add multiple True actions for the dynamic action.  If the triggering event of the modal dialog is a button instead of a region, you could specify that as your Selection Type for the triggering event.  If there are multiple regions on the page which could open the modal dialog, you could even use a jQuery Selector and reference multiple elements as your Selection Type for the triggering event. 

For example, if you're converting a non-modal form and report to use a modal form, and you wish to refresh the report after the modal dialog is closed (after creating a new record), you'll need to associate the triggering event with the button.  The steps would be:
  1. On the report page, create a new Dynamic Action and enter the following attributes:
    • Name: <provide a meaningful name>
    • Event: Dialog Closed
    • Selection Type: Button
    • Region:<choose your button, for example, your Create button>

  2. Select the True action of the Dynamic Action and enter the following attributes:
    • Action: Refresh
    • Selection Type: Region
    • Region: <choose your report region>

You should get this behavior out of the box when creating an application with a report and form, or adding a report and form to your application.  But if you're manually adding components, then it's really quite easy and straightforward to add this functionality to your APEX app.

If you install the Sample Database Application, you'll find numerous examples of this dynamic action refresh operation after the close of a modal dialog.  And it is briefly documented in this tip.