Skip navigation.

Scott Spendolini

Syndicate content
Mostly Oracle APEX. Mostly.Scott
Updated: 4 days 17 hours ago

Refreshing PL/SQL Regions in APEX

Tue, 2015-11-10 08:03

If you've been using APEX long enough, you've probably used a PL/SQL Region to render some sort of HTML that the APEX built-in components simply can't handle. Perhaps a complex chart or region that has a lot of custom content and/or layout. While best practices may be to use an APEX component, or if not, build a plugin, we all know that sometimes reality doesn't give us that kind of time or flexibility.

While the PL/SQL Region is quite powerful, it still lacks a key feature: the ability to be refreshed by a Dynamic Action. This is true even in APEX 5. Fortunately, there's a simple workaround that only requires a small change to your code: change your procedure to a function and call it from a Classic Report region.

In changing your procedure to a function, you'll likely only need to make one type of change: converting and htp.prn calls to instead populate and return a variable at the end of the function. Most, if not all of the rest of the code can remain untouched.

Here's a very simple example:


PROCEDURE print_region
(p_item IN VARCHAR2)
htp.prn('This is the value: ' || p_item);


FUNCTION print_region
(p_item IN VARCHAR2)
l_html VARCHAR2(100);
l_html := 'This is the value: ' || p_item;
RETURN l_html;

On the APEX side, simply create a Classic Report and set the query to something like this that refers to your function:

SELECT package_name.function_name(p_item => :P1_ITEM) result FROM dual
You'll then want to edit the Attributes of the Classic Report and turn off Pagination, set the Headings type to None and ensure Partial Page Refresh is enabled. Next, click on the Template Options and Disable Alternating Rows and Row Highlighting and then check Stretch Report.

2015 11 10 08 56 05

Make any other UI tweaks that you need, and you should now have a Dynamic PL/SQL Region that can be refreshed in a Dynamic Action.

APEX 5 Cheat Sheet

Mon, 2015-11-09 21:18
On Twitter today, Jeff Smith tweeted about a new SQL Developer cheat sheet that he created with a site called
Not to be outdone, I created a cheat sheet for the APEX 5 Keyboard Shortcuts. Not only can you view it online, but you can also download a PDF version of it. Check it out and let me know if there's anything that you'd like to see added.

Hide and Seek

Tue, 2015-11-03 14:30

In migrating SERT from 4.2 to 5.0, there's a number of challenges that I'm facing. This has to do with the fact that I am also migrating a custom theme to the Universal Theme, as almost 100% of the application just worked if I chose to leave it alone. I didn't. More on that journey in a longer post later.

In any case, some of the IR filters that I have on by default can get a bit... ugly. Even in the Universal Theme:

2015 11 03 15 25 18

In APEX 4.2, you could click on the little arrow, and it would collapse the region entirely, leaving only a small trace that there's a filter. That's no longer the case:

2015 11 03 15 25 31

So what to do... Enter CSS & the Universal Theme.

Simply edit the page and add the following to the Inline CSS region (or add the CSS to the Theme Roller if you want this change to impact all IRs):

.a-IRR-reportSummary-item { display: none; }

This will cause most of the region to simply not display at all - until you click on the small triangle icon, which will expand the entire set of filters for the IR. Clicking it again makes it go away. Problem solved with literally three words (and some punctuation).

Universal Theme Face Lift

Fri, 2015-10-23 08:42
I'm a huge fan of APEX's new Universal Theme, and have been working quite a bit with it.  One of the coolest features is how easy it is to change the colors.  You don't even need to be good at design - just click Theme Roller, and spin all the things!

However, as much as you change the colors, the look and feel still largely looks the same, since the base font is unchanged.

So let's change it up! More importantly, let's change it up without making any changes to the Universal Theme itself, so that when we upgrade to APEX 5.1, our changes will be preserved.

First, head on over to Google Fonts ( and pick a font to use as your new base font.  It doesn't really matter which one you use.  For this example, I’m going to use Montserrat.  Once you've chosen which font to use, click on the Quick Use icon.  This will render a page with a number of different options as to how to include the font in your application.

Select which styles of the font that you want to include.  Some fonts will have bold and italic; others will not, so make sure the font you select also have the styles that you want, too.

2015 10 23 10 20 56

Next, pick the character set(s) that you want to include.  My choice was pretty simple.

2015 10 23 10 21 09

Since there’s no “APEX” tab, we’re going to have to make do with the @import tab.  You’ll want to copy just the URL portion of the snippet.  So in this example, it would be:

2015 10 23 10 21 25

Lastly, we’ll also need to copy the font-family name, as we’ll use that in Theme Roller.  For this example, we would only need Montserrat

2015 10 23 10 21 35

Now that we have all of the details from Google Fonts, head on over to APEX.  First, edit your application’s Shared Components and navigate to User Interface Attributes and edit the DESKTOP UI.  In the Cascading Style Sheets section, paste the URL that you copied from Step 3 of the Google Fonts page into the File URLs region.

2015 10 23 10 33 42

Scroll to the top and click Apply Changes.

Next, run your application and open up Theme Roller by clicking on the link in the developer toolbar.  Once Theme Roller opens up, expand the Custom CSS region and paste the following code there, replacing Montserrat with your font-family name defined in Step 4 of the Google Fonts page:
body {
font-family: 'Montserrat', sans-serif;
font-weight: 300;
line-height: 25px;
font-size: 14px;
Save your changes, and notice that the entire application should be using your new font!  Don’t like how it looks?  Go pick a different font and see if that helps; or simply remove the Custom CSS and File URL to revert to the default one.

Next Oracle APEX NOVA Meetup Date Set

Mon, 2015-10-12 14:26
The next Oracle APEX NOVA MeetUp is going to be held on November 12th, 2015 at 7PM.  We decided to mix things up a bit and are going to have it at Vapianos in the Reston Town Center.  We're also going to try a more informal agenda.  In other words, there will be no agenda.

So if you're around Reston on November 12th from 7-9PM (or so), feel free to stop by.  Here's the link:

Drop It Like It's Not

Thu, 2015-09-17 08:50
I just ran the following script:

FOR x IN (SELECT table_name FROM user_tables)

FOR x IN (SELECT sequence_name FROM user_sequences)
  EXECUTE IMMEDIATE ('DROP SEQUENCE ' || x.sequence_name);

FOR x IN (SELECT view_name FROM user_views)
  EXECUTE IMMEDIATE ('DROP VIEW ' || x.view_name);

Basically, drop all tables, views and sequences.  It worked great, cleaning out those objects in my schema without touching any packages, producers or functions.  The was just one problem:  I ran it in the wrong schema.

Maybe I didn't have enough coffee, or maybe I just wasn't paying attention, but I essentially wiped out a schema that I really would rather not have.  But I didn't even flinch, and here's why.

All tables & views were safely stored in my data model.  All sequences and triggers (and packages, procedures and functions) were safely stored in scripts.  And both the data model and associated scripts were safely checked in to version control.  So re-instantating this project was a mere inconvenience that took no more than the time it takes to drink a cup of coffee - something I clearly should have done more of earlier this morning.

Point here is simple: take the extra time to create a data model and a version control repository for your projects - and then make sure to use them!  I religiously check in code and then make sure that at least my TRUNK is backed up elsewhere.  Worst case for me, I'd lose a couple of hours or work, perhaps even less, which is far better than the alternative.