If you want to default the search column in an Interactive Report, you can easily accomplish this with a Dynamic Action on the After Refresh event of your IR. This way instead of the report searching all columns, it will search the one you set it to (unless the user selects something different, of course)

No default    With Default Column

Dynamic Action: Set Default IR Search Column
Event: After Refresh
Selection Type: Region
Region: select the IR region on your page
Condition: JavaScript expression with the code:

$v("apexir_CURRENT_SEARCH_COLUMN") == ""

True Action: “Execute JavaScript Code” with the following code:

$s("apexir_SEARCH_COLUMN_DROP", "Empno");

Make sure it does fire on Page Load.

There are two values that need to be set. What the user sees on the screen “apexir_SEARCH_COLUMN_DROP” and what the IR will use as a column “apexir_CURRENT_SEARCH_COLUMN”, make sure this last one matches your column name.

By using the After Refresh event we make sure the default search column is set again after the report refreshes.

UPDATE March 15, 2017: If you’re on APEX 5.0 or later and need to remove the “All Columns” option, use this line of code provided by John Snyders. His blogs on working with the menu widget will help you a lot.

$("#_column_search_drop").on("menubeforeopen", function(event, ui) {ui.menu.items[0].hide = true;});

When you enter data on a page we often think about APEX Validations when we want to validate our inputs and give errors to the user. (i.e. Value is required. Dates are not within range, etc…)

However, sometimes you may not be able to report all errors with a validation.  Or perhaps the code you need to call performs validations and processing all in once transaction like in the case of a 3rd party API.  Enter APEX_ERROR.ADD_ERROR

From the documentation.
ADD_ERROR Procedure: This procedure adds an error message to the error stack that is used to display an error on an error page or inline in a notification. It can be called in a validation or process to add one or more errors to the error stack.

For example, if you’ve used some of the Oracle eBusiness Suite API, you know that it could take a lot of code to setup and call one of these API. Then, the API may return with errors that you may want to display back on your page. If you don’t handle them correctly, your APEX page will just display a single error or exception.

Lets say you have an error record type that will hold multiple errors:

type message_rec_type is record
  message_text varchar2(4000)
  -- ... other attributes ...
type message_tbl_type is table of message_rec_type
  index by binary_integer;

Then, if our API call returns a bunch of errors in a message_tbl_type structure, we can put those on our APEX page like so:

for i in 1 .. l_msg.COUNT loop
   apex_error.add_error(p_message => l_msg(i).message_text
                      , p_display_location => apex_error.c_inline_in_notification);
end loop;

For a quick and dirty example, I created a Page Process with this code:

  p_message => 'Your date range overlaps with other periods.'
, p_display_location => apex_error.c_inline_in_notification
  p_message => 'The category combination is invalid for the selected item.'
, p_display_location => apex_error.c_inline_in_notification

Submit the page and the result is:

Validation errors.

Validation errors.

Tagged with: , , ,

Create Feedback Page

Create Feedback Page

I find that using the Feedback functionality on APEX is just perfect when users are testing and reviewing your work on a dev or test environment.

It’s incredibly easy to setup.  Add a new page of type “Feedback Page”. Select the page number you want for this new page. The wizard allows you to create a Feedback Navigation Bar Entry and, while you’re there, enable feedback for your application for good measure.

At this point you’re done!  A brand new page is created and a “special” Navigation Bar Entry is added.  I say special because when you disable Feedback for your application (Application Properties -> Allow Feedback) the Nav Bar Entry goes away.

Feedback entries are added as part of Team Development for later review. But…

My problem with Feedback; I rarely know that it’s there!

I have gone days before I noticed someone added a new Feedback entry. Then the user asks… “what do you think about the issue I reported”. Enter blank stare.

To “solve” this, I like to add a little snippet of code using apex_mail to make people in the project aware of the new entry by sending out an email regarding the new Feedback entry.

If you look at the Feedback page, there’s a process in there “Submit Feedback”:

apex_util.submit_feedback (
    p_comment         => :P900_FEEDBACK,
    p_type            => :P900_FEEDBACK_TYPE,
    p_application_id  => :P900_APPLICATION_ID,
    p_page_id         => :P900_PAGE_ID,
    p_email           => null);

I like to change it to something like this:

  l_to app_parameters.value%TYPE;
  l_from varchar2(200);
  g_email_prefix app_parameters.value%TYPE;
  l_body clob;
  l_subject varchar2(200);
  i number;

  apex_util.submit_feedback (
      p_comment         => :P900_FEEDBACK,
      p_type            => :P900_FEEDBACK_TYPE,
      p_application_id  => :P900_APPLICATION_ID,
      p_page_id         => :P900_PAGE_ID,
      p_email           => null);

  l_to := app_util.get_param('FEEDBACK_EMAIL');
  l_from := nvl(app_util.get_email(:APP_USER), 'generic@company.com');
  g_email_prefix := app_util.get_param('EMAIL_PREFIX');
  l_body := 'New feedback for page: ' || :P900_PAGE_ID || utl_tcp.crlf
   || 'From: ' || :APP_USER || utl_tcp.crlf
   || utl_tcp.crlf
   || :P900_FEEDBACK || utl_tcp.crlf;

  i :=  instr(:P900_FEEDBACK, chr(10));
  if i = 0 or i > 80 then
    -- if the first line entered is too long or it's all in one line
    -- then grab the first 90 charachters entered by the user
    i := 90;
  end if;

  l_subject := g_email_prefix || 'Feedback: ' || substr(:P900_FEEDBACK, 1, i);

  apex_mail.send(p_to   => l_to
               , p_from => l_from
               , p_subj => l_subject
               , p_body => l_body);

The call to get_param(‘FEEDBACK_EMAIL’) returns a comma delimited list of all the emails that need to be notified. The get_param(‘EMAIL_PREFIX’) is used to be a good netcitizen and make it easy for people to filter these emails out. I usually set it to the Short App Name + DEV or TEST.
The email itself will include everything that was typed in by the user in the Feedback textarea. However, the Subject will be the first line typed or the first 90 characters.
See as long as you keep calling apex_util.submit_feedback to record the feedback, you can do anything you want. You could even send the feedback entry to some other tracking system and skip the call to apex_util.submit_feeback all together.

This is Part 2 in the series, checked out Part 1 if you missed it.

First order of business, make a copy of the public pages application. Install Theme 25 and make it current. Cool! So far so good, the Verify Compatibility step didn’t have any issues we couldn’t resolve.

Theme Switch Verify Compatibility

Theme Switch Verify Compatibility

But the joy didn’t last long. As expected, the home page wasn’t that big of a shocker since most of the look & feel AND navigation was hardcoded in the template. (Editor’s note: Kids, this is why you don’t want to hardcode your navigation in the template.)

Before and After Theme 25 Switch

Before and After Theme 25 Switch

But the rest of the pages were just plain scary. Lets look at the Calendar (p4) and Event Details page (p5)

Calendar Page

Calendar Page


Event Details Page

Event Details Page

Yeah, it was bad

Oh boy, I had my work cut out. But perhaps, it was not all completely terrible. Perhaps, the correct page template needed to be selected and regions moved to the correct template position.

I settled on pages with tabs and right sidebar (most of the time at least). So you’ll want to set that up in your Theme Defaults (Shared Components -> Themes -> Click on a theme, but make sure you’re on the Report View).

Theme Defaults

Theme Defaults

Page Details

Page Details

I tested the pages and there was absolutely no change. That’s because quite a few pages had the Template specified in the page details. This setting will override the default. For consistency, it’s best to set your page template to “Use Theme Default”. Then, only change it on a page by page basis when you need to.

The Grid

Ok, the Event Detail page is still looking terrible. You’ve probably seen something like this before. The reason lies in the big difference between how table layout for regions was handled before APEX 4.2 and how The Grid affects region layout now.

Event Detail page showing "The Grid" columns

Event Detail page showing “The Grid” columns

This page had two standard regions before. One in Column 1 (Event Information), the other (Actions) in Column 2. This got carried over to The Grid as Column 1 and Column 2 respectively, but it really didn’t get translated. There’s also a new element available to us now and that’s the Column Span, which was simply left as Automatic.

Region Column Span

Region Column Span

What APEX ends up doing, while processing things for “The Grid”, is see that the first region says Column 1 and Column Span is Automatic, so it says “I need to see what the next region wants”. The second Region (Actions) wants Column 2. This means it needs to start on Column 2. That means that Region 1 cannot span any columns and it must be set to a width of ONE COLUMN. We’ll get into The Grid in a moment but, for now, take note that The Grid for this template has 12 columns. No more, no less. Now it’s time to render Region 2 (Actions), this one starts on Column 2, the Column Span is set to Automatic and there are no other regions following. The only other limitation is the Right Sidebar, which is defined as 2 columns. That makes Region 2 span 9 columns. Which effectively explains our weird layout.

In this inspect image we can see the same thing, but now with the corresponding HTML and classes.

Inspect Showing the Assignments and the 9 column span class

Inspect Showing the Assignments and the 9 column span class

Notice the div id=”uMidCol”, this is our content region. It’s defined with a span of 10 columns (apex_span_10). Then there’s the right sidebar (div#uRightCol) set to a span of 2 columns (apex_span_2). Together we have the 12 columns available to our layout.

Within the uMidCol, we can see our Event Information div which gets a apex_span_1, effectively making it the width of ONE column only. The highlighted second region (Actions) get a class of apex_span_9. The alpha and omega classes (first and last letters of the Greek Alphabet) are used to define beginning and end of a grid layout.

How do we fix this? We have several permutations and more than one may yield acceptable results.

Option 1. Change everything to Automatic: Column = Automatic and Column Span = Automatic
Option 2. Chose the number of columns you want to use for each of the regions. Knowing that you have 12 columns, set your spans manually to size things as you wish. This is one of the benefits of using a Grid system.
Option 3. If you use a template with a sidebar, move the small region to the sidebar template position (The right sidebar is #REGION_POSITION_03#), keep your main region in the body and set the Column and Column Span to Automatic.

I settled with Option 2 for now, but Option 3 would have worked well for this layout also.


Region 1:Column = Automatic, Colspan = 10
Region 2: Column = Automatic, Colspan = Automatic (because it will take the remaining 2 columns)

The same type of fix was required for just about all the other pages. Now, as you can see, the regions are looking a lot better. On the next installment, we’ll talk about why the items are all squeezed into a tight column.

Tagged with: , , , , ,

This is the first in a series of posts documenting and sharing my experience changing an ancient (often hardcoded) theme to something more modern. The application started with HTMLDB 1.6 and Theme 4. The theme didn’t remain vanilla for long and was highly customized to accomodate the look and feel of a site coded with .NET. As you can expect from a site that has been around for so long, there will be some interesting situations and challenges. However, I’m confident that, thanks to APEX flexibility in separating the look & feel from the logic, this won’t be an overly painful experience (I guess we will see, won’t we?)

Read the rest of the story…

Tagged with: , ,

Who doesn’t love a shiny object? In this case I’m talking about tools; tools that make our life easier. TextExpander (Mac only ) is one of those tools. (No, I don’t get paid to say this and no I didn’t get a free license either.)

Basically, TextExpander allows you to assign some letters to be expanded into lots of letters. For example you type wwbr and TextExpander will change that to “With best regard, {your name}”

Here are some of the ones I use with APEX:
Abbreviation: aurl

This one is pretty straight forward, I type aurl (which reminds me of APEX URL) and a full APEX URL gets pasted into whatever textbox or editor my cursor happens to be. This is a nice way to remember or avoid typing all the positional parameters.

Abbreviation: ;;asl

%clipboard is a TextExpander keyword that will use the contents of the clipboard.
I simply: 1) type a page number, 2) cut it to my clipboard, 3) type ;;asl and as I hit tab or space, the abbreviation gets expanded and the URL gets added with the page number I wish to redirect to.

Here are a couple more:
;;anchor → <a href="f?p=&APP_ID.:PAGE:&SESSION.:">%clipboard</a>

This one will paste the contents of the clipboard as the link name, then I just need to edit PAGE to the correct destination.

gaevnt → onClick="_gaq.push(['_trackEvent', pName, 'ACTION', 'LABEL']);"

This is a Google Analytics Event. There was a project where several links and buttons required a GA event added. I still had to change the ACTION and LABEL manually, but this avoided a lot of typos.

In the previous example, I could have used TextExpander Fill-In Fields feature to open a “form” where I could fill in (ACTION and LABEL). You could also execute a script (In Python, Ruby, etc…) and use the output as the expanded result. You can see how the sky is the limit here.

I think you get the idea. We’ve only scratched the surface of what TextExpander can do. Perhaps you have canned email responses you need to send every so often. Or you transpose letter in certain words. Or need to use different email signatures depending on the destination. I just wanted to get you introduced to this workflow that perhaps can save you some keystrokes in APEX or otherwise.

Please share some other ways you can use this type of tool.

‡ TextExpander is a Mac only application. However, a quick search shows there are several Windows alternatives, some even import TextExpander snippets.

Tagged with: , , ,

For the second year in a row, I have had the privilege to present at KScope13 which was held at New Orleans, LA. My presentation was That’s Not Where I Want That!  It’s about the frustrations we have all experienced while attempting to improve the layout of our APEX applications. It’s a vast topic – several techniques and disciplines are required to be successful.  I covered a little about what APEX provides out of the box and some CSS as well. Here’s the live link to the actual presentation. You can use the arrow keys to navigate the slides, but I recommend just using the space bar to move “forward”. The embedded APEX application is hosted in apex.oracle.com. It should be valid at least up to APEX 4.2.2, hopefully much longer. Who knows what will happen years from now.



I didn’t use Powerpoint or Keynote. My whole presentation is one single HTML file, with CSS and javascript that can be viewed offline. Pretty cool I think.

I used reveal.js to create the presentation itself. It’s a fantastic javascript presentation framework created by Hakim El Hattab, and incredibly talented individual. However, instead of trying to explain what it is or what it can do, just check out the live demo.
At some point, I switched to using Slid.es – also created by Hakim – to build the actual presentation. It made the whole process painless. Slid.es supports PDF downloads, versions, self contained presentation downloads for offline use, custom CSS, ability to edit the raw HTML, themes, etc… etc…

For the APEX application screens, embedded in the presentation, I simply used an iframe tag.

On some browsers and for some of the pages the height and width were not needed, but having them, made everything more reliable. (more…)

Tagged with: , , , ,