Tuesday, November 10, 2015

Refreshing PL/SQL Regions in APEX

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.

Monday, November 09, 2015

APEX 5 Cheat Sheet

On Twitter today, Jeff Smith tweeted about a new SQL Developer cheat sheet that he created with a site called cheatography.com.
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.

Tuesday, November 03, 2015

Hide and Seek

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).

Friday, October 23, 2015

Universal Theme Face Lift

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 (https://www.google.com/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: https://fonts.googleapis.com/css?family=Montserrat

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.

Monday, October 12, 2015

Next Oracle APEX NOVA Meetup Date Set

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 MeetUp.com link: http://www.meetup.com/orclapex-NOVA/events/226009784/

Thursday, September 17, 2015

Drop It Like It's Not

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.

Monday, June 01, 2015

Sumner Technologies: Take Two

About a month ago, I left my position at Accenture Enkitec Group. I had a couple of ideas as to what I wanted to do next, but nothing was 100% solid.  After considering a couple of different options, I'm happy to announce that together with Doug Gault & Tim St. Hilaire, we're re-launching Sumner Technologies.

Much like last time, the focus will be on Oracle APEX; but we’re going to refine that focus a little bit.  In addition to traditional consulting, we’re going to focus more on higher-level services, such as security reviews and APEX health checks, as well as produce a library of on-demand training content.  APEX has matured tremendously over the past few years, and we feel that these services will complement the needs of the marketplace.

It’s exciting to be starting things over, so to speak.  Lots will be the same, but even more will be different.  There’s a lot of work to be done (yes, I know the site is not in APEX - yet), but we’re excited at the potential of what we’re going to offer APEX customers, as the APEX marketplace is not only more mature, but it’s also grown and will continue to do so.

Feel free to check out what we’re up to on Facebook, Twitter, LinkedIn and our website.  Or find us at KScope in a couple of weeks!

Monday, April 20, 2015

Destroying The Moon

Just under three years ago, I joined Enkitec when they acquired Sumneva.  The next three years brought a whirlwind of change and excitement - new products, additional training, and expanding the APEX practice from an almost nonexistent state to one of the best in the world.

Like all good things, that run has come to an end.  Last Friday was my final day at Accenture, and I am once again back in the arena of being self-employed.  Without any doubt, I am leaving behind some of the best minds in the Oracle community.  However, I am not leaving behind the new friendships that I have forged over the past three years.  Those will come with me and hopefully remain with me for many, many years to come.

Making the jump for the second time is not nearly as scary as it was the first time, but it's still an emotional move.  Specifically what's next for me?  That's a good questions, as the answer is not 100% clear yet.  There's a lot of possibilities, and hopefully things will be a lot more defined at the end of the week.


Tuesday, March 03, 2015

Little League, Big Data

Last week, I participated in my first Little League draft for my son's baseball team.  This was new territory, as up until now, play has been non-competitive.  This year we will actually have to keep score, and there will be winners and losers.

In preparation for the draft, we had tryouts a few weeks ago where we evaluated the kids on a number of different criteria.  Never have I seen so many scared 7 and 8 year olds march through the cages as dozens of coaches with clipboards watched and recorded their every move.  I camped out and watched them pitch, as from what many veteran coaches told me, the key to keeping the game moving along is the pitcher.

In preparation for the draft, we were sent a couple of key spreadsheets.  The first one had an average rating of all of the kids tryouts assessments, done by the board members.  The second one contained coaches evaluations for some of the players from past seasons. Lots and lots of nothing more than raw data.

Time to fire up APEX.  I created a workspace on my laptop, as I was not sure if we would have WiFi at the draft.  From there, I imported both spreadsheets into tables, and got to work on creating a common key.  Luckily, the combination of first and last name produced no duplicates, so it was pretty easy to link the two tables.  Next, I created a simple IR based on the EVALS table - which was the master.  This report showed all of the tryout scores, and also ranked each player based on the total score.

Upon editing a row in EVALS, I had a second report that showed a summary of the coach's evaluation from prior seasons.  I could also make edits to the EVALS table, such as identify players that I was interested in, players that were already drafted, and any other comments that I wanted to track.

After about 20 minutes of reviewing the data, I noticed something.  I was using data collected while the player was under a lot of stress.  The data set was also small, as each player only got 5 pitches, 5 catches, 5 throws, etc.  The better indicator as to a player's talents was in the coach's evaluations, as that represents an entire season of interaction with the player, not just a 3-4 minute period.

Based on this, I was quickly able to change my IR on the first page to also include a summary of the coach's evaluations alongside the tryout evaluations.  I sorted my report based on that, and got a very different order.  This was the order that I was going to go with for my picks.

Once the draft started, it was very easy to mark each player as drafted, so that any drafted player would no longer show up in the report.  It was also trivial to toggle the "must draft" column on and off, ensuring that if there were any younger players that I wanted, I could get them in the early rounds before we had to only draft older players.

Each time it was my pick, I already knew which player that I was going to draft.  Meanwhile, the other coaches shuffled stacks of marked up papers and attempted to navigate multiple spreadsheets when it was theirs.  Even the coordinator commented on how I was always ready and kept things moving along.

Unless you're some sort of youth athletics coach that does a draft, this application will likely do you little good.  But the concept can go a long way.  In almost any role in any organization, you likely have data for something scattered across a few different sources or spreadsheets.  This data, when isolated, only paints a blurry part of the whole picture.  But when combined and analyzed, the data can start to tell a better story, as was the case in my draft.

The technical skills required to build this application were also quite minimal.  The bulk of what I used was built-in functionality of the Interactive Report in APEX.  Merging the data and linking the two tables was really the only true technical portion of this, and that's even something that can be done by a novice.

So the next time you have a stack of data that may be somehow related, resist the temptation to use old methods when trying to analyze it.  Get it into the database, merge it as best you can, and let APEX do the rest.

Thursday, February 19, 2015

Screaming at Each Other

Every time I attend a conference, the Twitter traffic about said conference is obviously higher.  It starts a couple weeks or even months before, builds steadily as the conference approaches, and then hits a crescendo during the conference.  For the past few conferences, I’ve started my sessions by asking who in the audience uses Twitter.  Time and time again, I only get about 10-20% of the participants say that they do.  That means that up to 90% of the participants don’t.  That’s a lot of people.  My informal surveys also indicate a clear generation gap.  Of those that do use Twitter, they tend to be around 40 years old or younger.  There are of course exceptions to this rule, but by and large this is the evidence that I have seen.

I actually took about 10 minutes before my session today to attempt to find out why most people don’t care about Twitter.  The answer was very clear and consistent: there’s too much crap on there.  And they are correct.  I’d guess that almost 100% of all Tweets are useless or at least irrelevant to an Oracle professional.

I then took a few minutes to explain the basics of how it worked - hash tags, followers, re-tweets and the like.  Lots of questions and even more misconceptions.  “So does someone own a hash tag?” and “Can I block someone that I don’t care for” were some of the questions that I addressed.  

After a few more questions, I started to explain how it could benefit them as Oracle professionals.  I showed them that most of the Oracle APEX team had accounts.  I also highlighted some of the Oracle ACEs.  I even showed them the RMOUG hash tag and all of the tweets associated with it.  Light bulbs were starting to turn on.

But enough talking.  It was time for a demo.  To prove that people are actually listening, I simply tweeted this:
Over the next 30 minutes, I had 10 people reply. At the end of the session, I went through the replies, and said what I knew about those who did reply.  Oracle Product Manager, Oracle Evangelist, Oracle ACE, APEX expert, etc.  The crowd was stunned.  This proved that Twitter as a medium to communicate with Oracle experts was in fact, real.  

More questions.  “Can I Tweet to my power company if I have an issue with them?” and “Do people use profanity on Twitter?” were some of the others.  People were clearly engaged and interested.  Mission accomplished.

The bigger issue here is that I strongly feel that the vast majority of the Oracle community is NOT on Twitter.  And that is a problem, because so much energy is spent tweeting about user groups and conferences.  It's like we’re just screaming at each other, and not at those who need to listen.  

We can fix this.  I encourage everyone who presents at a conference to take 5 minutes at the beginning or end of their session to talk about the benefits of Twitter.  Demonstrate that if you follow Oracle experts, the content that will be displayed is not about Katy Perry, but rather about new features, blog posts or other useful tidbits that can help people with their jobs. Take the time to show them how to sign up, how to search for content, and who to follow.  I think that if we all put forth a bit of effort, we can recruit many of those to join the ranks of Twitter for all the right reasons, and greatly increase the size of the Oracle community that’s connected via this medium.