Martin Giffy D'Souza on Oracle APEX

APEX Process Condition with Multiple Buttons

2011-12-13T07:00:00.000-07:00

For new APEX developers, adding a page process condition can be a bit confusing at first when basing it on multiple buttons. This post will go through a scenario on how to easily use multiple buttons in a page condition.

The Setup
A classic example of having multiple buttons on a page is when you want to save or update a record. The Save button should only appear for new records, whereas the Update button should only appear for existing records. The catch is that when saving and updating the page you may need to run the same process.

The following setup highlights this classic example. Create a region in a page with two fields: P1_X and P1_Y. Then create three buttons: Save, Update, and Cancel. All three buttons should submit the page and an example of the page is shown below

Create the following page process:
Name: Update P1_Y
Process Point: On Submit - After Computations and Validations
Process: :P1_Y := :P1_X * 2;

The Problem
You'd like this process to run when both the Save and Update buttons are clicked. If you scroll down to the process's conditions area you'll notice that it has an option to restrict the process when a particular button is clicked (shown below).

As you can see you can only select one button from the list. At first glance the only option that you have to resolve the two button issue is to create a new process, which is a copy of the existing process, and set the condition to the other button. That doesn't necessarily make sense and can be an obvious maintenance nightmear.

The good news is that there is a very simple way to get around this issue. When you click a button it actually sets the REQUEST variable to the button name. The button name is defined in the button. For example the Save button's request is actually SAVE (shown below).

Going back to the original problem, and leveraging what we know about the REQUEST variable, you can modify the process's condition to have it run when either the Save or Update buttons are selected as shown below.

Note: They're other ways to use the REQUEST variable in the conditions section. This one highlights a very simple use.

Moving the APEX Developer Toolbar

2011-12-08T07:00:00.000-07:00

When you are developing an APEX application there may be certain situations (primarily depending on your page template) that require you to move the APEX toolbar from the bottom of the screen. The screen shot below shows such a case where there is content all the way to the end of the screen and the toolbar is preventing you from accessing/clicking on something at the bottom. If you try to click the last Edit button nothing happens since the toolbar is overlaying it. Note: I modified the page template to highlight this issue.

This doesn't occur very often but when it does there's a simple solution to move the toolbar from the bottom to the top of the screen. Run the following JavaScript code in your browser's console window and the toolbar will move to the top of the page.

//Note on older versions of APEX you'll need to check the toolbar's ID
$('#apex-dev-toolbar').css({
  top: 0,
  height: '22px'
});

Once at the top it will look like the image below. Note that the logout link (top right corner) is no longer clickable.

If this happens a lot in development you could create a special button for the JavaScript code and add it as a dynamic action.

apex_application.stop_apex_engine

2011-12-05T07:00:00.000-07:00

Something that was upgraded in APEX 4.1 with little to no far fair was the addition of a new (supported) procedure called apex_application.stop_apex_engine. The apex_application.stop_apex_engine essentially stops the APEX engine and, as a result, will stop processing the rest of the page. It is most commonly used when forcing URL redirects from a page.

Before APEX 4.1 you could still use a similar feature however it was an unsupported variable in the apex_application package called g_unrecoverable_error.

I've included an example below of the before vs. after 4.1 ways to stop the APEX engine.

-- Pre-APEX 4.1 (not supported)
...
owa_util.redirect_url('http://www.clarifit.com');
apex_application.g_unrecoverable_error:= true; -- End APEX session

-- APEX 4.1 and above (supported)
...
owa_util.redirect_url('http://www.clarifit.com');
apex_application.stop_apex_engine; -- End APEX session

For those who have used the apex_application.g_unrecoverable_error variable in some of your applications you should go back and upgrade to the new procedure. For more information about the stop_apex_engine procedure please read the APEX documentation.

ClariFit From/To Date Picker Plug-in

2011-12-01T07:03:00.001-07:00

In my previous post I discussed an issue about dynamic min and max dates using the APEX date picker. If you haven't read the article please take a few minutes before continuing.

At the end of my previous post I left off with the following question: How do you solve the dynamic min/max date issue? The answer is to use a new plugin that I created called ClariFit From To Date Picker. This is a free and open source plugin created as part of the ClariFit plugin set and is covered in detail in the book: Expert Oracle Application Express Plugins.

The plugin is an item plugin. Its options (shown below) are similar to the APEX date picker. The main difference is that instead of having Minimum and Maximum date attributes you now have Date Type and Corresponding Date Item attributes. The Date Type can either be a From or To date (i.e. min/max date). The Corresponding Date Item is the item name for the date picker that will determine the date's restrictions.

I've created a demo on plugins.clarifit.com. If you select a date in the From Date field (in this case 24-Nov-2011) then open the To Date date picker you'll notice that you can't select anything before 24-Nov-2011 as shown below.

The plugin also comes bundled with some nifty features that may not be visible at first but are really useful:

Allows for different From/To date formats (i.e. they don't need to be the same).
Built in validations:

Validates that the input is a valid date

Validates that From Date is less than or equal to the To Date

Javascript Console instrumentation (run the page in debug mode and look at the console)

Note: The built in validations will run as part of the page's validations and are only executed if you enable validation as part of your submit button.

The plugin can be downloaded from apex-plugin.com.

Min and Max Dates in APEX

2011-11-30T13:20:00.001-07:00

APEX 4.0 introduced native support for the jQuery UI Date Picker. When you create a date item in APEX you now have the option to set a Minimum and Maximum date as shown below. The catch is that those dates restrictions are defined when the page is rendered and aren't updated in real time. This is an issue if you want those constraints to dynamically change based on user input.

Date Picker Settings

In some cases you'll need the min and max dates to change dynamically. A classic example is when you book a plane ticket and set your departure and return dates. When you select a departure date then select a return date, the return date can't be before the departure date. Dates before the departure date are usually greyed out. Using APEX you could try to implement this example by doing the following:

- Create a Date Item, P1_DEPT_DATE. Set the Maxium Date to be &P1_RETURN_DATE.
- Create a Date Item, P1_RETURN_DATE. Set the Mininum Date to be &P1_DEPT_DATE.

Run the page and select a date for the Departure Date (in this example 24-Nov-11). Next, select a Return Date. You'll notice that the calendar picker does not prevent you from selecting a return date before 24-Nov-2011.

Note: If you submit the page now it'll raise an exception since the Min and Max attributes requires the date to be in YYYYMMDDHH24MI format.

So how do you have the min and max date restrictions change based on the user input? Excellent question. My next post introduces a plugin that resolves this issue.

ClariFit Dialog Plugin

2011-11-29T11:03:00.001-07:00

Over a year ago I released the ClariFit Simple Modal plugin. Since it's inception it's been downloaded almost 5000 times and has been used in several production applications (that I know of).

Today I'm please to release an "updated" version of plugin called ClariFit Dialog plugin. Like the Simple Modal plugin, the Dialog plugin is a dynamic action and allows you to have a modal or dialog window. It also has a lot of new features (listed below). Going forward if you're going to use a modal/dialog window plug-in I recommend using the ClariFit Dialog plugin.

A demo is available on plugins.clarifit.com and it can be downloaded from apex-plugin.com.

ClariFit Dialog Plugin Settings

List of features:

Uses the jQuery UI dialog code and leverages the jQuery UI theme currently incorporated in your APEX application.
Support for open/close directly in same plugin.
Option to have either a modal or dialog window.
Hide affected elements on page load. If set, this option will hide the region after the page is loaded. This removes the need to have separate region templates.
Chose visible state of the modal window after it's closed.

Remainder of 2011

2011-11-29T09:41:00.001-07:00

Dear Readers,

It's been a while (over 4 months) since I've posted a technical article on this blog. After presenting at ODTUG KScope this summer I've been really busy working at ClariFit and with other commitments. During the past four months there have been many times where I would have loved to roll up my sleeves and write a few articles but unfortunately other priorities took over.

The rest of my 2011 calendar is filling up quickly and I hope to squeeze in a few more articles by the end of the year. Here's what you can expect for the remainder of 2011:

Free Ask the ClariFit Experts Webinar on Wednesday December 14th at 1:30 PM EST. Note: it's still not to late to submit an APEX or PL/SQL question. Check the link for more info on how to submit questions and register.
Two free plug-ins to be launched this week (actually one will be today!)
Guest blog post on All Things Oracle about the Expert Oracle Application Express Plugins book.
Last but not least, (time permitting of course) a few APEX related articles. I have a very large list to write about and really look forward to dig into them.

- Martin

Ask the ClariFit Experts Webinar

2011-11-15T08:47:00.001-07:00

On December 14th at 1:30 PM EST Chris Hritzuk and I will be taking part in an “Ask the ClariFit Experts” webinar hosted by my company, ClariFit Inc. Leading up to the webinar the development team at ClariFit will be accepting APEX, SQL, and PL/SQL questions by email at AskTheExperts@clarifit.com. The plan is to choose a subset of questions from the list received and provide a live, step-by-step demonstration of the solution during the webinar. We’ll also be leaving some time at the end of the session to field any questions that come up during the webinar.

To find out more about the webinar, including how to sign up, click here: http://www.clarifit.com/1/post/2011/11/ask-the-clarifit-experts-webinar.html

Running for ODTUG Board of Directors

2011-10-12T09:52:00.000-06:00

For nearly four years I’ve enjoyed giving back to the Oracle APEX community in various ways such as the Oracle Forums, this blog, talks at various conferences, authoring free plug-ins, and webinars to name a few. Now I want to continue to help out in a different fashion by running for the ODTUG Board of Directors. I believe I will be able to help promote and grow the APEX community through the Board, and I would relish the chance represent all APEX developers.

I’m officially running for the ODTUG Board of Directors and would like your support. If you're a full (paid) ODTUG member please vote for the Board of Directors here: http://goo.gl/8xqxs Below is a copy of my official nomination:

Campaign Statement
I have attended ODTUG Kaleidoscope for several consecutive years and have been a presenter for the last three. The conference has allowed me to develop strong relationships with many others in the community, and the importance of these relationships has proven invaluable. I continually strive to give back to the community, using my personal time to answer questions through email, blogs, the Oracle forums, and by writing technical books. I would like to continue this spirit of giving back by joining the ODTUG Board of Directors.As a new board member I will bring a fresh perspective and out-of-the-box ideas to help promote ODTUG and deliver our message to the world. I am fortunate enough to have a successful blog with several thousand unique monthly visitors. It is through this platform, along with other opportunities such as my consulting firm blog, social networking, and the multiple annual conferences that I attend from which I intend to help share the ODTUG mission and values.The Board plays a pivotal leadership role as both a driving force and a face of the ODTUG community. I feel that my professional experience as a leader and mentor will help the Board guide and develop ODTUG for the future. The Board has responsibility to its most important group - the members. I will help ensure that the Board serves as both a voice and an ear for the entire ODTUG community; developers, DBAs, and technical experts of all things Oracle.Many thanks for your consideration.

Biographical Statement
Martin Giffy D’Souza is an Oracle ACE and award winning presenter and speaker. Most recently Martin was honored with the ODTUG Kaleidoscope 2011 Presenter of the Year award. Martin also serves as a Co-founder & CTO at ClariFit Inc., a consulting firm specializing in Oracle solutions. Martin’s career has seen him hold a range of positions within award winning companies and his experience in the technology industry has been focused on developing database-centric web applications using the Oracle technology stack. Martin is the author of the highly recognized blog www.TalkApex.com, and he has co-authored several APEX books including Expert Oracle Application Express, a collaboration of some of the most renowned APEX developers in the industry. He has presented at numerous international conferences such as ODTUG, APEXposed, and COUG. Martin holds a Computer Engineering degree from Queen’s University in Ontario, Canada.

I appreciate all the support.

- Martin

How My Family Solves Computer Problems

2011-07-13T07:00:00.000-06:00

In my talk, How to be Create II, at ODTUG Kscope 11 I discussed what sometimes happens when users try to fix problems themselves. This is a video clip from my presentation on how my family tries to resolve computer problems.

And yes it is a true story. I got those emails from my family...

Thanks to Chris Hritzuk for recording this.

How to be Creative II - Presentation Slides

2011-07-12T07:00:00.000-06:00

You can download the slides of the How to be Creative II presentation that I gave at ODTUG Kscope 11 from the following link: http://goo.gl/2MP9l Please note that the slides have been modified for handout purposes (i.e. not the exact same that I showed in the presentation).

The slides should also be updated soon on the ODTUG Kscope Scheduler: http://caat.odtug.com/ODTUG_registration_menu.html

APEX Region Errors - Part 3

2011-07-11T07:00:00.001-06:00

Disclaimer: This is an advanced post that discusses and modifies some of the inner workings of APEX.

In APEX Region Errors - Part 2 I discussed how to add triggers on the APEX Activity Log tables to store information in custom error tables when a user encounters an APEX region error.

Instead of storing the information in custom error tables you can leverage the APEX Feedback tool and trigger an automatic feedback entry. This may be a preferred option as you don't need to create custom tables and the feedback tool provides a lot of information.

Before continuing it's important that you know how to use the new APEX Feedback tool. If you don't know about the APEX Feedback tool I suggest that you read about how to create the feedback link (http://dgielis.blogspot.com/2010/03/apex-40-feedback-link.html) and how to access the feedback tool (http://dgielis.blogspot.com/2010/03/apex-40-looking-at-feedback-through.html). Try implementing it in a dummy application to see what you can do with it.

The following triggers will enter a feedback entry each time a region error occurs:

Disclaimer (again): Modifying anything in the APEX schema will put your APEX instance in an unsupported state. Please proceed with caution. I take no responsibility for any negative outcomes from this.


-- NOTE: Do this as the APEX_040000 user or a privlidged user such as SYSTEM
CREATE OR REPLACE TRIGGER apex_040000.trg_apex_activity_log1_air
  AFTER INSERT
  ON apex_040000.wwv_flow_activity_log1$
  FOR EACH ROW
  WHEN (new.SQLERRM IS NOT NULL)
DECLARE
BEGIN 
  -- Log as feedback
  apex_util.submit_feedback (
    p_comment         => 'AUTO MSG: Region Error', -- Put a comment here that can be used to easily identify auto generated feedback messages
    p_type            => 3, -- Bug. See API documentation for different values
    p_application_id  => :new.flow_id,
    p_page_id         => :new.step_id,
    p_email           => null,
    p_label_01        => 'Error Message', -- You can add up to 8 label/attributes. See API documentation for more information
    p_attribute_01    => :new.sqlerrm,
    p_label_02        => 'Component Type',
    p_attribute_02    => :new.sqlerrm_component_type,
    p_label_03        => 'Component Name',
    p_attribute_03    => :new.sqlerrm_component_name
    );
   
   -- Could use APEX_MAIL to send a notification to a list of developers to take a look at problem
   -- This is entirely optional. Modify as required
   apex_mail.send(
    p_to => 'someone@yourorg.com', 
    p_from => 'someone@yourorg.com',
    p_body => 'Region Error Occured. Please Check Feedback',
    p_body_html => '',
    p_subj => 'APEX Region Error');
    
END;
/

And... (differences are highlighted)


-- NOTE: Do this as the APEX_040000 user or a privlidged user such as SYSTEM
CREATE OR REPLACE TRIGGER apex_040000.trg_apex_activity_log2_air
  AFTER INSERT
  ON apex_040000.wwv_flow_activity_log2$
  FOR EACH ROW
  WHEN (new.SQLERRM IS NOT NULL)
DECLARE
BEGIN 
  -- Log as feedback
  apex_util.submit_feedback (
    p_comment         => 'AUTO MSG: Region Error', -- Put a comment here that can be used to easily identify auto generated feedback messages
    p_type            => 3, -- Bug. See API documentation for different values
    p_application_id  => :new.flow_id,
    p_page_id         => :new.step_id,
    p_email           => null,
    p_label_01        => 'Error Message', -- You can add up to 8 label/attributes. See API documentation for more information
    p_attribute_01    => :new.sqlerrm,
    p_label_02        => 'Component Type',
    p_attribute_02    => :new.sqlerrm_component_type,
    p_label_03        => 'Component Name',
    p_attribute_03    => :new.sqlerrm_component_name
    );
   
   -- Could use APEX_MAIL to send a notification to a list of developers to take a look at problem
   -- This is entirely optional. Modify as required
   apex_mail.send(
    p_to => 'someone@yourorg.com', 
    p_from => 'someone@yourorg.com',
    p_body => 'Region Error Occured. Please Check Feedback',
    p_body_html => '',
    p_subj => 'APEX Region Error');
    
END;
/

When a region error occurs you can now view the information in Team Development > Feedback.

If you click the Edit button you can get a lot of detailed information about was happening when the user encountered the error including all of the values in session state.

APEX 4 + HTML 5 = Awesome: Presentation Slides

2011-07-07T07:00:00.000-06:00

You can download the slides of the APEX 4 + HTML 5 = Awesome presentation that I gave last week at ODTUG Kscope 11 from the following link: http://goo.gl/vWW3Z Please note that the slides have been modified for handout purposes (i.e. not the exact same that I showed in the presentation).

The slides should also be updated soon on the ODTUG Kscope Scheduler: http://caat.odtug.com/ODTUG_registration_menu.html

I'll post the How to be Creative II slides in the next few days.

ODTUG KScope 11 - Recap

2011-07-06T07:20:00.002-06:00

I had an amazing time in Long Beach CA last week for ODTUG Kscope 11. This was my fourth time attending the Kscope conference and I must say that the ODTUG committee, along with YCC, made it the best one yet!

Saturday
It all started for me on Saturday. Once I arrived in Long Beach I went to register right away. Along with registration (which went flawlessly thanks to Lauren) I had my picture taken with Crystal in the new social media lounge. Lori, from YCC, did an excellent job promoting social media at Kscope 11 and I think it was a huge success.

Sunday
Sunday was the symposium day. Not surprisingly I attended the APEX track which were presentations given by the Oracle APEX team on the upcoming 4.1 features in APEX. Mike Hichwa gave us a glimpse of some of the long term plans for APEX. There's some really cool things coming up that he discussed including an APEX App Store.

After all the presentations were done we had the Welcome Reception in the Grand Ballroom. It was really nice to catch up with everyone and meet a lot of new people.

Monday
Following the Welcome Keynote, the individual sessions started on Monday. It was really tough to chose which talk to go to. So many times throughout the conference I wanted to be in two or three places at the same time.

I really enjoyed Dimitri Gielis's presentation on APEX charts. He also mentioned that we may* (in the future) be able to have HTML 5 charts instead of flash charts. This would be very good for applications that run on iOS devices.

On Monday there was the APEX Open Mic night. This is something I was really looking forward to because you get to see a lot of live demos of APEX being used in organizations. Wayne Linton, a fellow Calgarian, kicked things off with a demo of his APEX application for managing roles in the database.

Following the Open Mic night James and Tom from RedGate took some of us out to dinner at Parker's Lighthouse. Great food and good times with those that were there.

Tuesday
Tuesday morning I hid away in my hotel room as I was doing some last minute prep and practice for my presentation. I gave my first talk, APEX 4 + HTML 5 = Awesome, right before lunch to a packed room. It went really well and was a lot of fun.

In the afternoon I went to Roel Hartman's XFiles presentation. It is a cool application for storing and managing files in the database using XML DB. You can even download the application on SourceForge: http://sourceforge.net/projects/xace/

The final session of the night that I attended was Dimitri's APEX in Big Projects with Many Developers. Dimitri showed how he, and the team at APEX Evangelists, develop their applications. It was really interesting to see the list of tools they use and why they use them. It also encouraged me to start tracking time I spend on non-billable items such as writing blog posts, plugins, and answering questions.

Following the presentation everyone went to the Grand Ballroom for Happy Hour where we had some drinks and appetizers.

In the evening I attended the Oracle ACE dinner put on by Lillian Buziak. It was my first ACE dinner and I was very excited to be there and meet some of the other ACEs. After dinner I had a drink with some of the usual suspects (you know who you are) at the hotel bar and then headed off to bed.

Wednesday
After Tuesday's long day and great dinner it was tough to get up on Wednesday morning, but I schleped through it. The first presentation that I attended was by John Scott on APEX 4 plug-ins. The room was packed and, as usual, John did an excellent job highlighting how plugins work and showed off a few that he built. It was really cool to see that John integrated one of the HTML 5 items that I had demoed in Tuesday's presentation into one of his plug-ins.

After the morning sessions I took the rest of the afternoon off to deal with a critical client request and catch up on some rest (I didn't get a lot of sleep over the past few days so it eventually caught up to me).

Wednesday night was the big social event aboard the Queen Mary. The ODTUG committee went all out; it was SPECTACULAR. There was food everywhere, open bar (I was on best behavior since I had to present the next morning), jazz band, dueling piano, poker room, boat tours, and a cover band on the deck. The evening finished with some fireworks. I don't know how they're going to be able to top this event next year but I'm sure they'll find a way :-).

Thursday
Those that woke up on Thursday morning were pretty tired from the long week and the previous night's soiree. I gave my second presentation, called How to be Creative II, at 8:30 AM. A lot of people shockingly showed up and things actually went really well given that everyone was a bit groggy. I threw in a few extra jokes to help keep the audience awake. Dan McGhan was also giving a talk on Dynamic Actions at the same time. Unfortunately I wasn't able to attend (since I was presenting) but I heard it went really well.

I attended part of Raj Mattamal's presentation on Collections. Raj is a very "dynamic" speaker and always puts on an interesting talk. I also caught part of Shakeeb Rahman's talk where he converted an APEX application to mimic Apple's home page (he called it iToons). It was Shakeeb's first solo talk and he did an excellent job.

I also had a bit of a surprise on Thursday. At the closing ceremony I won the Best Speaker award for the APEX track and the overall Best Speaker of the Year award! I'm very honored to win the awards.

Summary
For those of you who didn't get to attend you really missed out on a stellar conference. The good news is that you can already register for Kscope 12 at www.kscope12.com which is going to be in San Antonio, Texas. Hope to see everyone there.

- Martin

ODTUG Kscope 11

2011-06-23T09:04:00.001-06:00

I'll be speaking at ODTUG Kscope 11 next week in Long Beach, California. I've attended this conference for the past three years and it's always been an amazing time. Some of the world's best Oracle and APEX developers will be there. One of the best things I found is that everyone is extremely friendly and very approachable.

Here are the talks that I'm giving this year:

APEX 4 + HTML 5 = Awesome
Tuesday June 28, 11:15 AM-12:15 PM, Room: 202C

HTML 5 consists of many new features which will make developers’ jobs a lot easier and improve end-user experience. HTML 5 is currently under development but many browsers already support some of its features. APEX developers should be aware of these enhancements. This presentation will highlight some of the HTML 5 features and how to integrate them with your APEX application to produce stunning results.

How to Be Creative II
Thursday June 30, 8:30 AM-9:30 AM, Room: 202B

This presentation will demonstrate how to leverage the APEX dictionary, along with existing plug-ins, to enhance your APEX applications. It will include how to automate monitoring of your application, improve testing capabilities, improve development time, and provide some tweaks to enhance end-user experience.

If things work out I'll be giving away copies of the two books I co-authored (Beginning Oracle
Application Express 4 and Expert Oracle Application Express) at each of my presentations (if this sounds like a bribe it is, especially for the Thursday morning presentation following the Wednesday night party :-). Hope to see you there!

Beginning Oracle Application Express 4 - Author Podcast

2011-06-13T07:00:00.000-06:00

I recently recorded a podcast, along with some of the other authors, about writing Beginning Oracle Application Express 4. Here's some more information about the podcast:

A conversation with Patrick Cimolini, Martin D'Souza, and Doug Gault; Authors of Beginning Application Express 4.

David Peake, Principal Product Manager for APEX, chats with some of the authors of Beginning Application Express 4. They will discuss various aspects about the book including the main reason for the book, who the book is focused towards, and some behind the scene information about writing the book.

Highlight Words in Standard Reports

2011-06-08T06:22:00.000-06:00

After creating the ClariFit Report Instant Search plug-in (demo) I received a lot of questions about how to enable column highlighting as shown in the following screen shot:

Column highlighting is not part of the plug-in. It is a built-in feature in APEX that has been around for a very long time. They're two ways to enable column highlighting in standard reports. The first is through the create report wizard. When you enable searching, as shown below, the columns that are selected to be searchable will automatically have column highlighting enabled on then.

To manually highlight a column edit the column. Scroll down to the Column Formatting region shown below. In the Highlight Words field you can enter a comma delimited list of words to search for or reference APEX items using the &ITEM_NAME. syntax. Note: for more on APEX variable syntax read this post.

ClariFit Report Instant Search Plug-in - Update 1.1

2011-06-07T11:12:00.003-06:00

After releasing the ClariFit Report Instant Search plug-in I received a lot of feedback about not being able to search for special characters such as colons (:). Certain special characters did not work since they conflicted with APEX's reserved characters.

Amongst some other small changes the special character issue has been resolved in the most recent copy of the plug-in. When fixing this issue I made sure that no additional AJAX requests were made so you will not see any performance issues upgrading to the new version. I will write a blog post on how I did this soon (though it may be irrelevant with the upcoming 4.1 release).

You can download the updated copy here. The demo has also been updated.

Malicious Code in APEX Plugins - Feedback

2011-04-13T05:24:00.002-06:00

My previous post about Malicious Code in APEX Plugins identified the possibility of harmful code in plugins (if you haven't read it please read the post before continuing). Several people had some excellent feedback which they included in the comments section. This post summarizes their comments and provides my thoughts on them.

Oracle Maintaining Plugin Repository

The idea is for Oracle to host something similar to Apple's App Store so that all code must pass a set of standards etc. I don't work for Oracle so I can't comment on this too much and I think it's a good idea. That being said the current community plugin repository, apex-plugin.com, has an approval process before plugins are released.

Wrapped PL/SQL and Licensed Plugins

Some plugins have wrapped PL/SQL source code and obfuscated/minimized JavaScript (JS) code. Plugin developers may need to wrap their PL/SQL code since their plugins are licensed. They may also minimize their JS files for performance issues. When you use these plugins the company that developed it can help determine its legitimacy (more on this in the next section). Just because it's wrapped doesn't mean you should not install it. They're other ways to validate that it is safe to use.

Trusted Developers and Organizations

They're some companies and developers within the APEX community that are well known and trusted. These companies specialize in APEX and would never write malicious code. For example I would never hesitate to install a plugin from organizations such as (but not limited to) ClariFit, Apex Evangelists, APEX Freelancer, Skill Builders, and Sumneva.

Scalability and Upgrades

Scott Spendolini made a great comment about the scalability of plugins and upgrades. I think this has to be examined on a case by case basis. If you're using a plugin on a small application that doesn't get a lot of hits then it may be a moot point. If your application gets millions of hits a day and you use a poorly optimized plugin then maybe you need to modify it to fit your needs. When looking at the source code you may not only be looking for malicious code but also techniques to improve performance for your specific needs. If the plugin has wrapped PL/SQL you can try to contact the developer/company to address your specific needs.

Like all software, plugins may need to be upgraded as APEX evolves (and it's 3rd party add-ons like jQuery). If the plugin is open source you can easily modify the code or email the developer with a change request. I've had several people email me about bugs and feature enhancements for plugins and was able to implement them in future versions.

Malicious Code in APEX Plugins

2011-04-06T07:00:00.001-06:00

The following post could be taken in the wrong way which may discourage some people from using plugins developed by the APEX community. Please read the ENTIRE post before you make any conclusions.

They're some good comments for this post. I've summarized them and included my thoughts in the following post: Malicious Code in APEX Plugins - Feedback

One of the best features of APEX plugins is the ability to share them with the community. Some sites, such as apex-plugin.com, host over 70 plugins. Most of these plugins are open source and free to use in production applications. These plugins have saved organizations lots of time developing redundant code.

There is the possibility for someone to develop a malicious plugin which could compromise your entire database or access to your application. For example I could easily create a dynamic action plugin that could send me a list of all your tables each time the plug-in is run. That being said I would never do that, but someone with bad intentions could.

What does this mean for you? Before integrating a plugin into your application (even in a development environment) you should look at both the PL/SQL and JavaScript code to see what they do. In most cases this extra step will take a few minutes but can save you a lot of headaches (and potentially your job). This is one of the main reasons why I don't compress JavaScript files in plugins that I post on apex-plugin.com. I want to ensure that other developers can easily review my code.

I am not trying to fear monger (there's enough of that going on in society as is) developers about the threat of using plugins. I just want to make sure that developers know the risks involved and what they should do to protect their database and business when integrating 3rd party plugins.

My First Book: Beginning Oracle Application Express 4

2011-03-31T08:00:00.001-06:00

I'm pleased to announce that Beginning Oracle Application Express 4 is finally published! I co-authored this book along with Doug Gault, Karen Cannell, Patrick Cimolini, and Timothy St. Hilaire. Co-authoring this book was a very interesting experience which was well worth the time and effort.

This book is aimed at beginner to intermediate APEX developers. It contains a lot of step by step instructions and detailed explanations to help you get a solid foundation for APEX development. You can purchase it from Amazon.com. If you'd prefer a soft copy the publisher, Apress, also offers the eBook.

Table of contents

Part I: Introducing APEX
Chapter 1: An Introduction to APEX 4.0
Chapter 2 - APEX 4.0: A Developer's Overview

Part II: Beginning APEX 4.0 Programming
Chapter 3 - Identifying the Problem & Designing the Solution
Chapter 4 - SQL Workshop
Chapter 5 - Creating the Base Application & Navigational Components
Chapter 6 - Forms & Reports
Chapter 7 - Charts & Maps
Chapter 8- Programmatic Elements
Chapter 9 - Security
Chapter 10 - Deploying Applications

Part III: Programming Websheets
Chapter 11 - Understanding Websheets
Chapter 12 - A WebSheets Example

Part IV: Advanced Topics
Chapter 13 - Extended Developer Tools
Chapter 14 - Managing Workspaces
Chapter 15 - Team Development

APEX Report Instant Search Plug-in

2011-03-25T06:51:00.001-06:00

Back in December I wrote a post about Instant Search in APEX. One of the requests I received was to make a dynamic action plug-in for this feature. I've finally created the Report Instant Search dynamic action plug-in (demo).

Compared to the original concept this plugin:
- Reduces the number of AJAX requests (more on this below)
- Includes a modified version of the APEX refresh report logic so it will not interfere with other reports
- Escapes special characters
- Supports multiple instances on the same page

Steps to create report and use plugin
- Create Report
- Report Type: SQL Report (standard report)
- Title: Instant Search
- Query: select * from emp
  - Note it is imperative that your report have a quick response time. If you have a report that takes a long time to run the user experience won't be very good.
- Report Attributes
  - Enable Search: Yes
  - Select Columns for Search: Select both ENAME and JOB
    - Note: this is what will highlight the selected text

Download and install the Report Instant Search plugin. Once installed create a dynamic action for the report with the following configuration:

- Create Dynamic Action
- Type: Advanced
- Name: Instant Search
- When
  - Event: Key Release
  - Selection Type: Item
  - Item: P1_REPORT_SEARCH
    - This is the search item that was automatically created as part of the standard report
  - Condition: No Condition
- True Action
- Action:
  - ClariFit Report Instant Search
  - Fire on Page Load: uncheck
- Affected Elements
  - Select Type: Region
  - Region: Instant Search

When you refresh your page you can start typing in the search field and the report should get updated. An online demo is available here: http://apex.oracle.com/pls/apex/f?p=45587:600.

Detailed Explanation

From the original blog post each time a key is pressed an AJAX request is sent to the database to obtain a refreshed version of the report. For example if you are looking for "Smith" and start typing in "smi" each letter sends a request to the database as shown in the following diagram:

To improve performance a timer was added which will only submit requests a minimum of 200 ms (this is configurable) apart from each other. If you type "smi" really quickly, only one ajax request will be made. This is shown in the following diagram:

Extra validations were added which ignore "non-change" key strokes. For instance if you clicked on the search field and use the arrow keys to move to the first character, each arrow key would trigger a report refresh. To prevent this from happening the value of the value of the search field is stored before the timer is invoked. Once the timer has expired it checks to see if the search value has changed. If no change was made then nothing happens. If a change is detected an ajax request is made.

VirtualBox - High CPU Problem

2011-03-21T08:00:00.000-06:00

I've been using Oracle's VirtualBox for several months now. I'm really impressed with this product and it has helped me a lot to rapidly prototype some APEX concepts.

One thing that bothered me when I started using it was that no matter what my virtual machine was doing, VirtualBox was using a lot of the CPU on my laptop. This didn't matter if the virtual machine was idle or doing any processing. After some digging around I found this very helpful post on the Oracle forums: http://forums.oracle.com/forums/thread.jspa?messageID=9305831. As weird as it may sound the suggestion is to create and start a second, dummy, virtual machine. I was pleasantly surprised to see that it worked. The configuration that I used was: DOS as an OS. 4 MB of memory and a 4 MB hard drive. Disable all the extra stuff (network, audio, usb, etc).

Here are the screen shots of my CPU before and after I started the second, dummy, virtual machine:

Before:

After:

After starting the second virtual machine the CPU spiked for a few seconds then dropped significantly. Hopefully this issue will be fixed in a future version of VirtualBox but until then this is a very simple work around.

demo.talkapex.com

2011-02-01T07:00:00.000-07:00

As part of this blog I've posted a lot of demos on apex.oracle.com. One thing that's always bothered me is that the demos have never been linked together or searchable.

I finally create a new demo site: demo.talkapex.com. Going forward all demos will be hosted on this site. In some cases I may move the old demos to the new site.

Console Wrapper (previously JS Logger)

2011-01-30T17:52:00.001-07:00

I've moved the Console Wrapper (previous called JS Logger) code to be hosted as a Google project here: http://code.google.com/p/js-console-wrapper/

I decided to create a Google project since maintaining code via a blog is not the best or easiest thing to do. I will continue to blog about any major updates but all examples, documentation, issues, and code will be maintained on the Google project page.

Previous references to Console Wrapper
- http://www.talkapex.com/2010/08/javascript-console-logger.html
- http://www.talkapex.com/2011/01/js-logger-101-logparams.html

JS Logger 1.0.1 - logParams

2011-01-17T08:00:00.002-07:00

Note: This now has a new name, Console Wrapper, and has been moved to a Google project. Please go to http://www.talkapex.com/2011/01/console-wrapper-previously-js-logger.html for more information.

I've updated my JavaScript Console Logger package to include a new function called logParams. logParams will automatically log all the parameters in your function. This can save a lot of time since you don't need to manually list all the parameters and it will detect any "extra" parameters.

Here's an example of the code:


function myFn(px, py, pz){
  $.console.logParams();
  
  //do something as part of this function
  
}//myFn;

//Not required if using in APEX. 
//If called from APEX it will detect if you are in debug mode
$.console.setLevel('log'); 

//Call myFn with the correct amount of parameters
myFn(1,2,3);

//Call myFn with more than "expected" number of parameters
myFn(1,2,3,'hello', 'goodbye');

Which results in:

Note: by default the parameters groups are collapsed by I've expanded them for this demo

Download Javascript Console Logger $logger.1.0.1. For more information about this javascript library please read my original post: http://www.talkapex.com/2010/08/javascript-console-logger.html

Thanks to Dan McGhan for helping come up with this idea.

100th Post

2011-01-17T07:00:00.000-07:00

Two and a half years after starting this blog I'm excited to be writing my 100^th post! Through this blog I've had the opportunity to meet a lot of people, learn a lot of new things, and given opportunities I may not have otherwise had.

I thought it would be interesting to post some stats about this blog for those that were curious. I wrote my first post on July 31st, 2008 but only started to capture analytics starting in March 2009.

- March 2009
- ~1500 visits / month
- ~2400 page views / month

- Current monthly avg (Dec 2010)
- ~3500 visits / month
- ~5300 page views / month

- Overall (since March 2009)
- ~53,000 visits
- ~85,000 page views
- ~150 countries

Thanks for following along and providing feedback.

- Martin

Variables in APEX

2011-01-05T08:00:00.000-07:00

When I first started using APEX I was unsure about which method to use when referencing variables (values in session state). The purpose of this post is to outline the different ways to reference APEX variables and provide an examples for each case.

They're four ways to reference variables in APEX:

:Bind_variables

&Substitution_strings.

V / NV functions

#Hash#

Bind Variables
Bind variables can be used in any block of SQL or PL/SQL code inside APEX. For example if creating a report to display all the employees in a selected department the query would be:


SELECT ename
  FROM emp
 WHERE deptno = :p1_deptno

Where P1_DEPTNO is a page item that you created.

Substitution Strings
Substitution strings use the &variable. notation. They can be used anywhere in your APEX application such as a HTML region or even a template. You can also use them in inline SQL and PL/SQL code but it is not recommended for security reasons. For more information on this security risk read the following Oracle white paper on SQL Injection

A simple example of using a substitution string is in a HTML region which displays a welcome message. The region source would be:


Hello &APP_USER.

Welcome to the demo application.

V / NV Functions
If you want to reference APEX variables in compiled code, such as views, packages, and procedures, you can't use bind variables. The easiest way to reference the variables is to use the V (for strings) and NV (for numbers) functions. For example:


CREATE OR REPLACE PROCEDURE log_apex_info
AS
BEGIN
  INSERT INTO tapex_log ( username, apex_page_id, access_date)
       VALUES (V ('APP_USER'), NV ('APP_PAGE_ID'), SYSDATE);
END;

#Hash#
The hash notation of #variable# is used in multiple places. When creating column links in a report you can use the hash notation to represent column values. The following figure highlights how column values are referenced in a report link. #EMPNO# is used to reference the EMPNO column and #JOB# is used to pass the job as a parameter to Page 2.

The hash notation is also used in templates (Shared Components > Templates). When modifying a template they're special variables available depending on the type of template. These variables can be found at the bottom of the screen in the Substitution Strings section.

For more information about referencing variables in APEX read the Referencing Session State section in the builder guide.

Missing ID in the No Template Region Template

2011-01-04T08:00:00.001-07:00

When you create or edit a region in APEX you can select a template. Region templates define the location of buttons in a region, how the region looks, etc. There is a region template called "No Template" which is the first option in the template list. When you select No Template as the region template APEX will only display the content of the region an nothing else (no header, buttons, etc).

There is a problem with using No Template if you use Dynamic Actions or have any JavaScript code which references the region. The No Template region template does not contain an ID attribute which prevents dynamic actions from binding to the region.

The following steps will create a region that simulates the "No Template" region and includes an ID so it will work with dynamic actions.

- Go to Shared Components
- Click on Templates
- Click the Create button
- Template Type: Region
- Select From Scratch and click Next
- Name: No Template w. ID
  Theme: Select your default theme (in most cases you'll only have one option)
  Template Class: Reports Region
  Click the Create button
- You should be brought back to the Templates page. To filter the list of regions select Region as the Type and click the Go button
- Click on No Template w. ID to modify it
- Put the following HTML in the Definition section



  #BODY#

- It should look like
- Click the Apply Changes button

If you modify your region that used No Template and change it to the new template that you created, No Template w. ID, it will look the same for the end users with the big difference being that dynamic actions will work when applied against the region.

The following query identifies regions that use the No Template region template which you may want to modify to use the custom No Template w. ID:


SELECT page_id, page_name, region_name, template
  FROM apex_application_page_regions
 WHERE application_id = :app_id
   AND template = 'No Template'

Note: I think Patrick Wolf mentioned that the No Template region template will include an ID in APEX 4.1.

Instant Search in APEX

2010-12-21T23:38:00.004-07:00

Note: A plug-in was created for this. Please got to: http://www.talkapex.com/2011/03/apex-report-instant-search-plug-in.html for more information.

I was asked if APEX supports instant searches like Google has. The goal was to have a search box and as the user started typing the report would filter based on the current search string. My first thought was to leverage Interactive Reports (IR) but after some research IRs were a bit too much for what was required. Instead I decided to start with a standard report and enable the search feature during the create wizard. To create the standard report page:

- Create Page
- Page Type: Report
- Report Type: Classic Report
- Page Name: Instant Search
- Query: select * from emp
- Report Attributes
- Enable Search: Yes
- Select Columns for Search: Select both ENAME and JOB as shown in the the following figure:

If you run the page and enter a search string you'll need to click the Go button, which will submit the page, to filter the results in the report. To enable "Instant Search" create a Dynamic Action as follows:

- Create Dynamic Action
- Type: Advanced
- Name: Instant Search
- When
- Event: Key Release
- Selection Type: Item
- Item: P3400_REPORT_SEARCH This is the search item that was automatically created as part of the report search.
- Condition: No Condition
- True Action
- Action: Refresh
- Fire on Page Load: uncheck
- Affected Elements
- Select Type: Region
- Region: Instant Search
- Click the Create button

If you run the page and start typing in the search field nothing appears to happen. In fact it's actually refreshing the report each time you alter the value in the search field. The problem is that it's not submitting the search value back to the database so the results are always the same. To resolve this issue you could create another dynamic action to set the search value's session state. This approach significantly slows things down because it makes two requests: one for setting the search value's session state and the second to refresh the report.

As a work around I've tweaked the region refresh javascript to set the search field session value as part of each region refresh as shown below. A demo of this is here: http://apex.oracle.com/pls/apex/f?p=16406:1100:0 If you start typing in the search field the report will automatically be filtered as shown in the following figure:

I've included the altered JavaScript function below which you'll need to include in your page. I don't plan to create a dynamic action plugin to encapsulate this logic since I'm hoping this work around won't be necessary in APEX 4.1. If you do plan to use this in a production environment I suggest creating a plugin to make it more flexible and avoid any conflicts with APEX.


//This is a copy from the APEX JS function obtained from apex_4_0.js
//from apex_4_0.js: Copyright © 2003, 2008, Oracle. All rights reserved.
function $a_report(pId,pMin,pMax,pFetched,pSort,pRefreshMode){
 // Trigger the before refresh event, and pass the current report Id convenience.
 // Event is triggered on table element with the ID equal to 'report_' + pID + '_catch'.
 // This element is not exposed in any templates and output by our engine, so is safe to
 // use.
 // Event handlers can be bound to this element in conjunction with the jQuery 'live'
 // bind type, or can be bound to higher element (such as the main region ID) and use
 // the regular bind type. The latter works because the event bubbles and is how this
 // is handled within the dynamic action framework.
 //
    apex.jQuery('#report_' + pId + '_catch').trigger('apexbeforerefresh', pId);

  var l_URL = 'p='+$v('pFlowId')+':'+$v('pFlowStepId')+':'+$v('pInstance')+':FLOW_PPR_OUTPUT_R'+pId+'_';
  
 // if refresh mode is passed, deal with that first
 if (pRefreshMode) {
        if (pRefreshMode === 'current'){
            // do nothing to url here
        } else if (pRefreshMode === 'reset') {
            l_URL += 'reset_R_' + pId;
        }
 } else {
     if (!!pSort) {
         l_URL += pSort+'::RP&fsp_region_id='+pId;
     } else {
         l_URL += 'pg_R_'+pId+':NO&pg_max_rows='+pMax+'&pg_min_row='+pMin+'&pg_rows_fetched='+pFetched;
     }
    }

  //This is the modified code
  //Change P3400_REPORT_SEARCH with your item name
  l_URL += ':NO::P3400_REPORT_SEARCH:' + $('#P3400_REPORT_SEARCH').val();
  
 var lRequest = new apex.ajax.url(l_URL,
   function(pResponse){
    var l_s = pResponse.readyState;
    var l_Id = lRequest.report_id;
    if(l_s == 1){
    }else if(l_s == 2||l_s == 3){
    }else if(l_s == 4){
                // This looks a little bit complicated and it is! To avoid screen flicker
                // when the HTML code is inserted into the DOM and JavaScript code modifies the
                // code afterwards (which takes some time), we are injecting the HTML code in
                // a temporary hidden area and do all our modifications and after that we
                // are replacing the existing report_xxx_catch with the new version.
                var lTemp = $u_js_temp_drop();
                apex.jQuery('#report_'+l_Id+'_catch').attr('id', 'report_'+l_Id+'_catch_old');
                apex.jQuery(lTemp).html(pResponse.responseText);
                apex.jQuery('#report_'+l_Id+'_catch_old').replaceWith(apex.jQuery('#report_'+l_Id+'_catch'));
                apex.jQuery(lTemp).empty();

             // Trigger the after refresh event, and pass the current report Id convenience.
             // Event is triggered on table element with the ID equal to 'report_' + pID + '_catch'.
             // This element is not exposed in any templates and output by our engine, so is safe to
             // use.
             // Event handlers can be bound to this element in conjunction with the jQuery 'live'
             // bind type, or can be bound to higher element (such as the main region ID) and use
             // the regular bind type. The latter works because the event bubbles and is how this
             // is handled within the dynamic action framework.
             //
    apex.jQuery('#report_' + pId + '_catch').trigger('apexafterrefresh', pId);

    }else{return false;}
   }
 );
   lRequest.report_id = pId;
   lRequest._get();
   return;
}

Column Groups in APEX 4.0 Interactive Reports

2010-12-13T19:02:00.001-07:00

A long time ago I wrote about adding Column Groups to Interactive Reports (IR) in APEX: http://www.talkapex.com/2009/03/column-groups-in-apex-interactive.html I created a new dynamic action plugin to do column grouping in APEX which can be downloaded here. Please read the help text in the plugin for full instructions on how to use it. Once integrated it should look like:

I also added additional JS debugging information. If you run your application in debug mode and look at the console you'll notice the following log information:

The additional logging is using the console logger wrapper that wrote: http://www.talkapex.com/2010/08/javascript-console-logger.html If you're writing your own plugins I suggest using it to instrument your code. It helped me debug some of my JavaScript issues while developing this plugin.

APEX 4.0: New Columns in APEX_WORKSPACE_ACTIVITY_LOG

2010-12-08T08:00:00.001-07:00

Over a year ago I wrote about how to permanently store some of the log information from APEX: http://www.talkapex.com/2009/05/apex-logs-storing-log-data.html APEX 4.0 has some additional columns in APEX_WORKSPACE_ACTIVITY_LOG which you may want to store as well. The following query identifies the new columns:

Note: This query assumes you still have an old instance of APEX 3.2 and a new instance of APEX 4.0 on your database

SELECT column_name
  FROM all_tab_columns
 WHERE owner = 'APEX_040000'
   AND table_name = 'APEX_WORKSPACE_ACTIVITY_LOG'
MINUS
SELECT column_name
  FROM all_tab_columns
 WHERE owner = 'APEX_030200'
   AND table_name = 'APEX_WORKSPACE_ACTIVITY_LOG'
ORDER BY column_name

In case you don't have an old 3.2 instance on your database the new columns are:

CONTENT_LENGTH
INTERACTIVE_REPORT_ID
IR_SAVED_REPORT_ID
IR_SEARCH
WS_APPLICATION_ID
WS_DATAGRID_ID
WS_PAGE_ID

If you permanently store the log information you may want to update your tables and scripts to store the new information.

APEX: How to Trigger Dynamic Action From Button

2010-12-07T20:32:00.000-07:00

Someone recently asked me how to trigger a dynamic action from a button. At first I thought this was trivial question until I tried to do it and found it's not as straight forward as I expected.

The following steps cover how to create a dynamic action on a button:

Modify Button Template:

You'll need to modify the button template to allow for the button attributes to be applied to the button. This will allow us to use an ID to identify a button. To apply the button attributes go to Shared Components > Templates > Select the default button template (for most instances called "Button"). Add #BUTTON_ATTRIBUTES# to the button tag in the Template section. For example from:

<button value="#LABEL#" onclick="#LINK#" class="button-gray" type="button">

To:

<button value="#LABEL#" onclick="#LINK#" class="button-gray" type="button" #BUTTON_ATTRIBUTES#>

Create Button
On the page you're working on create a Region Button:

Button Name: TEST BUTTON
Label: Test Button
Button Style: Template Based Button
Button Template: Button (or what ever button template you modified)
Button Attributes: id="test_button"
Make sure the id is unique

Action: Redirect to URL
URL Target: javascript:return;

Create Dynamic Action
Create a Dynamic Action. When you get to the "When" section:

Event: Click
Selection Type: DOM Object
DOM Object: test_button
The DOM object represents the ID that you defined while creating the button.
Condition: No Condition

Create your True and/or False actions accordingly.

Now when you run the page and click the button it should execute your dynamic action.

APEX Hash Tag on Twitter: #orclapex

2010-10-25T16:33:00.000-06:00

The old Oracle APEX hash tag on twitter was #apex. If you search for #apex on Twitter you get a lot of non Oracle APEX related topics as apex is a common name.

Today I proposed a new hash tag for Oracle APEX related content on Twitter. Thanks to Scott Spendolini for coming up with the final name and Dan McGhan for providing feedback.

When posting on Oracle APEX related content on Twitter please us #orclapex

APEX Reports: No Limit Downloads

2010-10-21T09:00:00.000-06:00

When configuring a standard report in APEX you can define the maximum number of rows that the query will return. This setting is called Max Row Count and can be configured in the Report Attributes tab. If you leave it blank it will default to 500 rows.

It is recommended that you set the Max Row Count with a low value. Using a small number will help improve performance since it reduces the amount rows APEX must count in order to display the pagination information. On a business perspective, it may not make sense to return thousands of rows for a user to view online.

What if the user wants to retrieve all the data when they download the report? From the end users perspective this makes sense since they may want to extract all the data to do some custom analysis.

My first thought was to use a page item for the Maximum Row Count. I quickly discovered that it only takes a numeric value. As a work around you can control the rows returned directly from the SQL statement. Here's an example of this:

- Create a large table:


CREATE TABLE large_emp
AS
      SELECT *
        FROM emp
  CONNECT BY LEVEL <= 5;

- Create standard report
Create a standard report using the following query. Set the Max Row Count to 999999999 (i.e. some very large number)


SELECT ename,
       job,
       sal,
       comm
  FROM large_emp
 WHERE ROWNUM <= CASE WHEN :request LIKE ('FLOW_EXCEL_OUTPUT%') THEN ROWNUM ELSE 500 END;

You are manually defining the Max Row Count by adding in ROWNUM predicate.

When users views the report in APEX it will only display a maximum of 500 rows. When they download the report the csv file will contain all the rows.

A caveat with this approach is that if your report has more than 500 rows you won't see the "... of more than 500" rows message as part of the pagination. Instead users will see "rows ... of 500" in the pagination. Users may be misled to think that the report only has 500 rows of data.

When I reviewed this technique I was concerned about performance issues when viewing the report in APEX. Here are the TKPROF outputs of the original query (with Max Row Count = 500) and the alternate query with the ROWNUM predicate (Max Row Count = 999999999):


-- Both outputs are for VIEWING the report (not downloading it)
--
-- Original Query
-- Max Row Count = 500
-- Run for displaying rows 1~15
SELECT ename, job, sal, comm
  FROM large_emp
 order by 3 -- Automatically added by APEX

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch      501      0.65       1.42         14       3417          3         501
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      504      0.65       1.42         14       3418          3         501

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 40     (recursive depth: 2)

Rows     Row Source Operation
-------  ---------------------------------------------------
    501  SORT ORDER BY (cr=3417 pr=14 pw=2053 time=1417580 us)
 579194   TABLE ACCESS FULL LARGE_EMP (cr=3417 pr=0 pw=0 time=1158549 us)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  direct path write temp                         70        0.00          0.00
  direct path read temp                           2        0.00          0.00


-- *************************************
  
-- New Query
-- Max Row Count = 9999999999
-- Run for displaying rows 1~15
SELECT ename, job, sal, comm
  FROM large_emp
 WHERE ROWNUM <= CASE WHEN :request LIKE ('FLOW_EXCEL_OUTPUT%') THEN ROWNUM ELSE 500 END
 order by 3 -- Automatically added by APEX

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.00       0.00          0          0          1           0
Fetch      501      0.38       0.53          0       3417          0         500
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      505      0.38       0.54          0       3417          1         500

Misses in library cache during parse: 1
Parsing user id: 40     (recursive depth: 2)
  
Rows     Row Source Operation
-------  ---------------------------------------------------
    500  SORT ORDER BY (cr=3417 pr=0 pw=0 time=535125 us)
    500   COUNT  (cr=3417 pr=0 pw=0 time=3376 us)
    500    FILTER  (cr=3417 pr=0 pw=0 time=2298 us)
 579194     TABLE ACCESS FULL LARGE_EMP (cr=3417 pr=0 pw=0 time=5212959 us)

I'm not a performance guru and I can't comment too much on these outputs. If you think this will have negative effects on performance please add your feedback as a comment on this post.

Oracle XE and APEX: Where is my Trace File?

2010-10-20T09:00:00.001-06:00

In APEX you trace your session by adding &p_trace=YES at the end of your URL. For example: http://fcapex40:8080/apex/f?p=101:1:2603384364125271::NO&p_trace=YES Please refer to the APEX documentation for more information regarding traces in APEX.

The documentation states that the trace file is located in the following directory:


SQL> -- Run as SYSTEM
SQL>  SHOW PARAMETERS user_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
user_dump_dest                       string      /usr/lib/oracle/xe/app/oracle/
                                                 admin/XE/udump

This isn't the case if you're using the Embedded PL/SQL Gateway, which is how APEX is run on Oracle XE. Thanks to Jari for answering my question on the APEX forum about this: http://forums.oracle.com/forums/thread.jspa?forumID=137&threadID=1771547

For APEX installations that use the PL/SQL Embedded Gateway (i.e. Oracle XE) the trace file is located in the following directory:


SQL> SHOW PARAMETER background_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
background_dump_dest                 string      /usr/lib/oracle/xe/app/oracle/
                                                 admin/XE/bdump

To find the trace file that was created for your session:


$ cd /usr/lib/oracle/xe/app/oracle/admin/XE/bdump
$ grep -l "APP_SESSION" *.trc
#example 
$ grep -l "2603384364125271" *.trc

APEX Region Errors - Part 2

2010-10-19T07:50:00.001-06:00

Disclaimer: This is an advanced post that discusses and modifies some of the inner workings of APEX.

In my previous post on APEX Region Errors I wrote about logging region errors in APEX. In this post I'll cover how to automatically be notified when a region error occurs and store some additional log information.

It's important that you actually read my previous posts on APEX Region Errors and Custom Error Messages in APEX. The rest of this post will assume that you've read both of these posts.

Before we continue we need to review how the activity log works. APEX_WORKSPACE_ACTIVITY_LOG is a view which references APEX_040000. WWV_FLOW_ACTIVITY_LOG. WWV_FLOW_ACTIVITY_LOG is a view which joins APEX_040000.WWV_FLOW_ACTIVITY_LOG1$ and APEX_040000.WWV_FLOW_ACTIVITY_LOG2$. The first paragraph of APEX Logs: Storing Log Data provides a brief description of why the logs are stored in two tables.

In order to have advanced log information and notify an administrator when a region error occurs we will apply a trigger on each of the log tables.

Disclaimer (again): Modifying anything in the APEX schema will put your APEX instance in an unsupported state. Please proceed with caution. I take no responsibility for any negative outcomes about this. Joel Kallman wrote about The Perils of Modifying the Application Express Metadata in regards to Oracle support.

- Install Logger and compile the sp_log_error_page procedure.
Install logger and sp_log_error_page in the schema that your APEX application will run against.
Logger: http://logger.samplecode.oracle.com
sp_log_error_page: http://www.talkapex.com/2010/09/custom-error-messages-in-apex.html

- Apply trigger to log tables
Apply triggers to both of the activity log tables so that we can be notified right away when a page or region level error occurs.

As SYSTEM compile the following triggers:


CREATE OR REPLACE TRIGGER giffy.trg_apex_activity_log1_air -- Where "giffy" is the schema that I am running my APEX app in. Change accordingly
  AFTER INSERT
  ON apex_040000.wwv_flow_activity_log1$
  FOR EACH ROW
  WHEN (new.SQLERRM IS NOT NULL)
DECLARE
  v_count                       PLS_INTEGER;
BEGIN
  -- Check if the application belongs to this schema
  SELECT COUNT (application_id)
    INTO v_count
    FROM apex_applications
   WHERE application_id = :new.flow_id
     AND owner = 'GIFFY'; -- Schema that I am running in

  IF v_count > 0 THEN
    sp_log_error_page (p_scope_prefix => 'apex.region_error',
                       p_application_id => :new.flow_id,
                       p_page_id   => :new.step_id,
                       p_oracle_err_msg => :new.SQLERRM,
                       p_apex_err_msg => '{component_type: ' || :new.sqlerrm_component_type || ', component_name: ' || :new.sqlerrm_component_name || '}',
                       p_email     => NULL);
  END IF;
END;

And... (differences are highlighted)


CREATE OR REPLACE TRIGGER giffy.trg_apex_activity_log2_air -- Where "giffy" is the schema that I am running my APEX app in. Change accordingly
  AFTER INSERT
  ON apex_040000.wwv_flow_activity_log2$
  FOR EACH ROW
  WHEN (new.SQLERRM IS NOT NULL)
DECLARE
  v_count                       PLS_INTEGER;
BEGIN
  -- Check if the application belongs to this schema
  SELECT COUNT (application_id)
    INTO v_count
    FROM apex_applications
   WHERE application_id = :new.flow_id
     AND owner = 'GIFFY'; -- Schema that I am running in

  IF v_count > 0 THEN
    sp_log_error_page (p_scope_prefix => 'apex.region_error',
                       p_application_id => :new.flow_id,
                       p_page_id   => :new.step_id,
                       p_oracle_err_msg => :new.SQLERRM,
                       p_apex_err_msg => '{component_type: ' || :new.sqlerrm_component_type || ', component_name: ' || :new.sqlerrm_component_name || '}',
                       p_email     => NULL);
  END IF;
END;

Now when an page or region level error occurs you will have advanced logging information and you have the option to be notified by email. The log information will be stored in your schema in the LOGGER_LOGS table

APEX Region Errors - Part 3

APEX Region Errors - Part 1

2010-10-18T09:00:00.002-06:00

A while ago I wrote about how to handle and customize the default unhandled exception error page in APEX: http://www.talkapex.com/2010/09/custom-error-messages-in-apex.html The method described in the post catches page level errors and can notify administrators immediately when an undhandled exception occurs.

Region level errors are not caught using the method above. An example of a region error is when you have a query that has divisor is equal to zero error. To simulate this error create a standard report region with the following query:


SELECT *
  FROM emp
 WHERE 1 / 0 = 0 -- Causes an ORA-01476: divisor is equal to zero

This query will return a "ORA-01476: divisor is equal to zero" error message in APEX.

Starting with APEX 4.0 region level errors are logged in the APEX Activity Log. The following query identifies both page and region level errors:


SELECT apex_user,
       application_id,
       application_name,
       page_id,
       page_name,
       view_date,
       apex_session_id,
       error_message,
       error_on_component_type,
       error_on_component_name -- Region/Process that caused the issue
  FROM apex_workspace_activity_log
 WHERE error_message IS NOT NULL

I strongly recommend that you frequently scan the activity logs for errors. Part 2 of will describe a way to be automatically notified of region level errors.

They're a few caveats that you should be aware of:

- As mentioned, the activity log identifies both region and page level errors. APEX_WORKSPACE_ACTIVITY_LOG.ERROR_ON_COMPONENT_NAME identifies the region or process that the error occured in.

- When multiple region level errors occur only the first error is logged in the activity log.

- From my limited testing, errors in Interactive Reports are not logged. Their may be other types of regions that don't log errors as well.

Given the caveats listed above querying the activity logs help identify some, but not all, errors that happen in your application.

APEX Region Errors - Part 2

APEX Page Locks

2010-09-29T07:21:00.000-06:00

When developing APEX applications in a team environment you usually don't want multiple developers working on the same page at the same time. This can cause a lot of confusion and unexpected effects with the application.

APEX has a great feature called Page Locks which enables developers to lock a page so that they're the only one that can work on it at a given time. Other developers can view the page, but can not make any changes while the page is locked.

To lock a page, click on the lock icon located in the top right corner of the page as displayed in the following picture:

You will need to enter a comment and then click the Lock Checked button. For the comment it is recommended that you enter what case/bug number you're working on and what impact it may have on the page. The history of these comments, both for locking and unlocking the page, is logged. Before you invest a lot of time with detailed comments it's important to note the following points that may be relevant within your environment.

Page locks are not retained when an application is copied or exported. This makes sense as you don't want page locks to propagate in an export file.

Like page locks, the lock history is not retained when an application is copied or exported. If you tend to move the development copy of your application between workspaces this may be an issue. This may effect the level of detail for lock comments that you require the developers on your team to use.

If you want to backup the page lock comment history you can access them from the following table:


  SELECT *
    FROM apex_040000.wwv_flow_lock_page_log
   WHERE lock_flow = :app_id
ORDER BY lock_page, action_date

Demo APEX Right Out of the Box

2010-09-16T08:00:00.000-06:00

If you want to quickly try out the new features after installing APEX 4.0 you don't need to do anything! APEX now comes with a built in demo.

To access this demo, log into a workspace and click the "Learn more..." button.

On the right hand side you'll see a list of features to demo.

If you're curious, the tables that the demo is using are: APEX_040000.WWV_DEMO_DEPT and APEX_040000.WWV_DEMO_EMP.

APEX IR: Subscriptions with Bind Variables and VPD

2010-09-15T09:00:00.000-06:00

Starting in APEX 4.0, Interactive Reports (IR) now have the ability for users to automatically get reports emailed to them. This is a great feature to allow end users to subscribe to data extracts rather than developers writing custom code.

To enable subscriptions select the Subscription option in the IR Report Attributes page:

Users can subscribe to email notifications by selecting the Subscription option in the Actions menu

Before implementing subscriptions they're some things that you should be aware of.

If your query uses bind variables, they will not be bound in the emailed reports. For example, supposed you had a select list of departments, P1_DEPTNO. If you then had an IR that listed all the employees in the department it would look like:


SELECT *
  FROM emp
 WHERE deptno = :p1_deptno

When a user is viewing the report in the application they'll get some employees for each department. If they subscribe to this IR then they'll get no rows returned as no value is bound for :P1_DEPTNO.

If you use the VPD feature in Oracle (Shared Components > Security Attributes > Virtual Private Database) it doesn't appear to be fired before the query is run as part of the subscription. I tested this by calling a log procedure in the VPD section. When the report is generated for an email subscription no log was registered in my log table.

I wouldn't classify either of these cases as bugs, however it's important for you to know what subscriptions can and can't handle before leveraging the subscriptions feature in a production application.

APEXposed 2010

2010-09-14T09:00:00.001-06:00

I'll be giving a few presentations this year at APEXposed 2010 in Dallas, Texas.

This will be my first APEXposed 2010 event and I'm really excited to go since they're some excellent presenters this year. If you're new to APEX this will be an excellent opportunity to learn from some of the best in the APEX community.

If you haven't already done so I encourage you to register soon to take advantage of the early bird rates.

I'll be doing 2 presentations and participating in a workshop. I've included the abstracts for them below:

APEX Plug-ins: One of the most anticipated new features in APEX 4.0 is Plug-Ins which allows developers to declaratively extend existing functionality in APEX. This presentation will go over the different types of Plug-Ins and how to create them. Primary focus will be on Item, Process, and Region type Plug-Ins.

Creating your first Plug-In can be intimidating, especially for developers new APEX developers. Some tips to help with obstacles that developers may encounter will be discussed.

Team Development: Managing a development project can be a cumbersome activity without the right tools. Beginning with 4.0, APEX now includes a software development management tool that integrates with your APEX applications.

This presentation will go over these tools and how they can help manage the development life cycle of your APEX application.

Authentication Workshop: I'll be participating in this workshop. Here is the abstract

Who are you, really? This workshop will demonstrate many methods of answering that question, from built-in authentication schemes to custom single sign-on. LDAP, RSA, home-grown. We will provide methods to solve many common authentication requirements.

ODTUG: How to be Creative

2010-09-12T23:43:00.001-06:00

At ODTUG Kaleidoscope I promised that I would post my slides from my presentation. Here is a copy of the How to be Creative presentation.

The demos can be found here:

Apex Dictionary: http://www.talkapex.com/2010/07/missing-views-from-apexdictionary.html
and http://www.talkapex.com/2008/11/how-to-list-apex-dictionary-views-using.html

Tooltips: http://www.talkapex.com/2009/09/tooltip-help-in-apex-alternative-to.html

Standardized Help: http://www.talkapex.com/2009/10/standardizing-help-text-in-apex.html

APEX Logs: http://www.talkapex.com/2009/03/custom-authentication-status.html

Custom Error Messages: http://www.talkapex.com/2010/09/custom-error-messages-in-apex.html

Custom Error Messages in APEX

2010-09-12T23:32:00.003-06:00

As developers we try to prevent unhandled exceptions from occurring for end users. They can occur in any program or language, and APEX is no exception (pardon the pun).

When an unhandled exception happens, users are presented with an error message which is similar to the following:

This error message isn't very user friendly and most users won't know what an "ORA-..." message means. The other issue with this screen is that it does not provide any feedback to developers. If the user does not report this issue developers won't know that it is happening.

As part of my ODTUG presentation I demonstrated how to alter the default error page to provide a user friendly error message and provide an instant notification to developers that an error has occurred. This post will describe how to do implement a user friendly error handling method in APEX 4.0.

Special thanks to Roel Hartman and Learco Brizzi for providing the ideas behind this.

- Install Logger
Logger is an open source package written by Tyler Muth. It's an excellent tool to quickly allow developers to instrument their code. Though it is not required, this demo references it. A copy of logger is available here: http://logger.samplecode.oracle.com/.

- Install Simple Modal Plug-in
Install the Simple Modal plugin into your APEX application: http://www.apex-plugin.com/oracle-apex-plugins/dynamic-action-plugin/simple-modal.html. When you download this plugin, the zip file contains 2 plugins. One to show the modal window and one to close the modal window. It is recommended that you install both if you plan to use it in other applications. Only the "Simple Modal - Show" plug is required for this demo.

- Create Error Procedure
Compile this procedure in your schema. It will log all the page items, application items, and all other not-null page items.


/**
 * Logs unhandled error message to Database
 * Logs:
 * - APEX and Oracle error messages
 * - All application level items
 * - All page items for p_page_id
 * - Items on other pages that are not null
 * 
 * Logs are stored in logger_logs
 * Requires: https://logger.samplecode.oracle.com/
 *
 * @param p_scope_prefix Scope prefix used in logger
 * @param p_application_id 
 * @param p_page_id Page that error occured on
 * @param p_oracle_error_msg Oracle error message
 * @param p_apex_error_msg APEX error message
 * @param p_email Email to be notified of error. If null, then no notification email sent.
 * @author Martin Giffy D''Souza http://www.talkapex.com
 */

CREATE OR REPLACE PROCEDURE sp_log_error_page (p_scope_prefix IN VARCHAR2,
                                               p_application_id IN apex_applications.application_id%TYPE DEFAULT v ('APP_ID'),
                                               p_page_id      IN apex_application_pages.page_id%TYPE,
                                               p_oracle_err_msg IN VARCHAR2 DEFAULT NULL,
                                               p_apex_err_msg IN VARCHAR2 DEFAULT NULL,
                                               p_email        IN VARCHAR2 DEFAULT NULL)
AS
  v_db_name                     VARCHAR2 (30);
  v_schema                      VARCHAR2 (30);
  v_scope                       VARCHAR2 (255);
BEGIN
  -- Set scope for logger
  v_scope := p_scope_prefix;

  -- Add Uniqe Identifier to scope
  v_scope := LOWER (v_scope || 'unhandeled_exception{session_id: ' || v ('APP_SESSION') || ', guid: ' || SYS_GUID () || '}');

  -- Log the initial error to be kept permanently
  logger.log_error ('Unhandeled Exception', v_scope, 'Oracle Error: ' || p_oracle_err_msg || CHR (10) || CHR (10) || 'APEX Error Page Message: ' || p_apex_err_msg);

  -- Log the information to help Dev team in production
  FOR x IN (SELECT 'APP_USER' item_name, v ('APP_USER') item_val FROM DUAL
            UNION
            SELECT 'APP_SESSION' item_name, v ('APP_SESSION') FROM DUAL
            UNION
            -- Include all the items from the current page
            SELECT item_name, v (item_name) item_val
              FROM apex_application_page_items
             WHERE application_id = p_application_id
               AND page_id = p_page_id
            UNION
            -- Include all the non-null page items
            SELECT item_name, item_val
              FROM (SELECT item_name, v (item_name) item_val
                      FROM apex_application_page_items
                     WHERE application_id = p_application_id
                       AND page_id != p_page_id)
             WHERE item_val IS NOT NULL
            UNION
            -- Include all Application Items
            SELECT item_name, v (item_name)
              FROM apex_application_items
             WHERE application_id = p_application_id)
  LOOP
    logger.log_information (x.item_name || ': ' || x.item_val, v_scope);
  END LOOP;

  -- Email Notification
  IF p_email IS NOT NULL THEN
    -- Send Mail

    apex_mail.send (p_to        => p_email,
                    p_from      => 'martin@talkapex.com', -- CHANGE THIS!
                    p_body      => 'An unhandled exception happend in an application. Please search logger logs for: ' || v_scope,
                    p_subj      => 'Unhandled Exception in: ' || p_application_id);
  END IF;
END sp_log_error_page;

- Create Error Page
This page will display a user friendly message to the user. For the purposes of this demo Page 200 will be created to handle error messages.

Create Page
Create Page 200

Create a HTML region
Region Name: Unknown Error
Source: An unhandled error occurred. A notification has been sent to the system administrator.

Create a Region Button:
Button Name: Back
Action: Redirect to URL
Execute Validations: No
URL Target: javascript:window.history.go(-1);

Add the following hidden items:
P200_PAGE_ID
P200_ESCAPE
P200_ORA_MSG
P200_APEX_MSG

Create Computation:
Item: P200_ORA_MSG
Point: Before Header
Type: PL/SQL Expression
Computation: REPLACE(:p200_ora_msg,:p200_escape,':');

Create Dynamic Action:
(select Advanced)
Name: Show Error Message Modal
Event: Page Load
Action: Simple Modal - Show
- Esc Close: No
- Change Opacity and Background Color as desired
Select Type: Region
- Region: Unknown Error

Create Page Process:
Type: PL/SQL
Name: Log Error
Point: On Load - Before Header
Source:


DECLARE
BEGIN
  sp_log_error_page (p_scope_prefix => 'apex.demo.', -- Enter what ever you want to help identify your apex errors in the log tables
                     p_application_id => :app_id,
                     p_page_id   => :p200_page_id,
                     p_oracle_err_msg => :p200_ora_msg,
                     p_apex_err_msg => :p200_apex_msg,
                     p_email     => '' -- Enter your email address here
                                      );
END;

- Change Error Template
Go to: Shared Components > Templates
Select the default Page Template (for my demo mine was: One Level Tabs - Right Sidebar (optional / table-based)
Error Page Template:

- End Result
When you have an unhandled exception the end users should see a message like:

You can view all the log information by running the following query:


SELECT *
  FROM logger_logs
 WHERE scope = 'apex.demo.unhandeled_exception{session_id: 652754467566839, guid: 901e0663a0896b35e040007f0100049a}'; -- Replace this scope with the scope that is sent in the email

APEX IR: Column Help

2010-09-09T08:00:00.000-06:00

I was working with an Interactive Report (IR) in APEX 4.0 and noticed that when the column header is clicked there's a Column Information button:

When clicked it displays the column help:

At first I thought this was a new APEX 4.0 feature and then I tested it on a 3.2 instance and noticed that the column information button was present as well.

Either way, it's a really nice feature to have since you can provide column information to users.

The following query helps to identify which IR columns have the default "No help available for this page item." message. You may want to change this since it says "... page item." rather than "column"


SELECT ap.page_id,
       ap.page_name,
       irc.column_alias,
       irc.help_text
  FROM apex_application_page_ir_col irc, apex_application_pages ap
 WHERE irc.application_id = :app_id
   AND irc.application_id = ap.application_id
   AND irc.page_id = ap.page_id
   AND irc.help_text = 'No help available for this page item.' -- Default help text message

APEX Spring Cleaning

2010-08-31T08:55:00.000-06:00

Each time you do a major upgrade APEX creates a new schema. It does not remove the older schemas, which allows you to roll back to previous versions in case something happens.

I was upgrading some older instances of APEX and realized that I still had some of these older schemas lying around and decide that it was time to do some spring cleaning (I realize that isn't exactly spring time, unless you live in Australia).

From the APEX installation guide (http://download.oracle.com/docs/cd/E17556_01/doc/install.40/e15513/otn_install.htm#CBHBABCC) here's how to identify and remove older versions of APEX.

Please read the documentation and understand what exactly you're doing before you do this!

- Identify old APEX schemas


SELECT username
   FROM dba_users 
 WHERE (username LIKE 'FLOWS_%' OR USERNAME LIKE 'APEX_%') 
   AND USERNAME NOT IN (
        SELECT 'FLOWS_FILES' 
          FROM DUAL 
         UNION 
        SELECT 'APEX_PUBLIC_USER' FROM DUAL 
         UNION
        SELECT SCHEMA s 
           FROM dba_registry
         WHERE comp_id = 'APEX')

- Remove old schemas
Connect as SYS using the SYSDBA role then run:


DROP USER FLOWS_030000 CASCADE; -- Where "FLOWS_030000" is the username from the previous query

JavaScript Console Logger

2010-08-30T08:00:00.001-06:00

Note: This now has a new name, Console Wrapper, and has been moved to a Google project. Please go to http://www.talkapex.com/2011/01/console-wrapper-previously-js-logger.html for more information.

If you've been developing APEX applications for while, or any web applications for that matter, you'll eventually leverage the browser console. If you've used Firebug, then you've had the console accessible to you.

In case you're not sure what console is, it allows you to display debug information in a nice console window within your browsers. Most (i.e. all browsers but IE) are console enabled. The most common use of console is the console.log command:


console.log('hello world');

Console has a lot of great features. One drawback with it is that you have to remove calls to console before putting your code into production since not all browsers support console.

Removing instrumentation code before going into production can be annoying, especially if you need to debug it later on. To resolve this issue I've created a console wrapper. This allows you to leave your debugging calls in production code. Here are some features:

Works in all browsers. If run in IE nothing will happen, but no error message.

Allows you to set Levels. By default no messages will appear.

Support for jQuery chaining (see examples).

Will automatically set level to "log" if run in APEX and APEX is in Debug mode.

A copy of the console wrapper is available here: Download $logger_1.0.0.

The only change that you'll need to make is call $.console instead of console. The download file includes a HTML file with demos. I'd recommend looking at the .js file as well for inline documentation.

Here's an example along with its output:


$.console.setLevel('log');
$.console.log('Current Level', $.console.getLevel());
$.console.group('Available Levels');
$.each($.console.levels, function(i, val){
   $.console.log(i);
});
$.console.groupEnd();
$.console.log(($.console.isApexDebug()) ? 'In APEX debug mode' : 'Not in APEX debug mode');
$.console.group('Demo Chaining');
//Notice how you can write this out all in 1 line!
$('.red').log('Before Red').css({color:'red'}).log('After Red');
$.console.groupEnd();
$.console.info('Turning off console');
$.console.setLevel('off');

For more information about console please read the following articles:

- Console APIs: http://getfirebug.com/wiki/index.php/Console_API
- Firebug console example: http://getfirebug.com/logging
- Console tutorial: http://www.tuttoaster.com/learning-javascript-and-dom-with-console/

APEX Plugin: Simple Modal

2010-08-20T09:00:00.000-06:00

I just published another plugin called Simple Modal: http://www.apex-plugin.com/oracle-apex-plugins/dynamic-action-plugin/simple-modal.html

This plugin allows you to use any region(s) (or DOM object) in your APEX application as a modal window.

When developing this plugin I learned a few more things that may be useful when developing plugins:

- Scope Creep: When developing a plugin you can make it do a lot of things. This may lead you to try and include extra unnecessary functionality. Try to remember the goal you're trying to achieve, or more importantly, what you're developers will try to achieve with the plugin.

- Instrumentation: I'm a big fan of Tom Kyte and really like the emphasis he puts on Code Instrumentation. You may want to add some debug information in your JavaScript code to help you understand what is going on. As part of this plugin I included a logger package which is essentially a wrapper for console but will work in all browsers. I'll write about this in another post and include the final copy for general use.

APEX 4.0 Interactive Reports - Customize Wait Display

2010-08-18T22:37:00.000-06:00

Over a year ago I wrote about how to customize the APEX IR wait logo (http://www.talkapex.com/2009/04/apex-interactive-reports-customize-wait_28.html). If you read that post you'll notice it's quite lengthy and can be intimidating if you're new to JavaScript.

With APEX 4.0 this is a lot easier to do since they're plugins to declaratively add this functionality. This post will go over how to customize the APEX IR Wait logo in APEX 4.0. You can try a demo here: http://apex.oracle.com/pls/apex/f?p=20195:3200

- Create an IR report region


    SELECT e.*, SUM (e.sal) OVER () test
      FROM emp e
CONNECT BY LEVEL <= 5

- Install Plugin

- Download the Simple Modal plugin: http://www.apex-plugin.com/oracle-apex-plugins/dynamic-action-plugin/simple-modal.html
- Shared Components / Plug-ins / Import
- They're 2 plugins included in the zip file. Import both of them (Show and Close)

- Create Show Dynamic Action

- RClick on the IR region and click "Create Dynamic Action:
- Advanced
- Name: Show IR Wait
- Next
- Event: Before Refresh
- Selection Type: DOM Object
- DOM Object: apexir_WORKSHEET_REGION
- Note: We're using the DOM object and not the region since we can port this example to Page 0 and it will apply to all your IRs
- Next
- Action: Select Simple Modal - Show
- You can modify some of the plugin attributes here if you'd like
- Next
- Selection Type: DOM Object
- DOM Object: apexir_LOADER
- Create

- Create Close Dynamic Action

- RClick on the IR region and click "Create Dynamic Action:
- Advanced
- Name: Close IR Wait
- Event: After Refresh
- Selection Type: DOM Object
- DOM Object: apexir_WORKSHEET_REGION
- Next
- Action: Select Simple Modal - Close
- Create

Now when you run the IR it'll make the screen modal while it's reloading the data. If you want to run on all IRs then you can add this dynamic action to Page 0.

If you run the demo in a console-enabled browser (Firefox, Chrome, Safari) you'll notice that the plugin includes some additional debug information. I'll be posting the logging JavaScript package that was used in the plugin soon.

APEX Plugin: Debug Mode and Console

2010-08-18T09:00:00.000-06:00

When running an APEX application in debug mode, it will provide debug information in the console regarding any plugins that get executed. Note: Console is supported by most browsers

Here's an example of the output:

APEX Plugin: Javascript Attributes

2010-08-17T21:04:00.000-06:00

Recently I was developing an APEX dynamic action plugin and ran into a bit of an issue. One of the custom attributes that I defined was a yes/no attribute which returned "true" or "false" respectively.

When I was testing my plugin it always behaved as though the user had selected yes/true even though they selected no/false. After some debugging I saw that the plugin values in Javascript were strings. Clearly strings are not booleans and they behave differently when evaluated.

To resolve this problem I just changed my if statement:


//From
if (this.action.attribute01) 
  //do something
  
//To
if (this.action.attribute01.toLowerCase() == 'true')
  //do something

If you're new to JavaScript, here's how JavaScript handles strings when doing comparisons:


x = null;
console.log(x, x ? true : false);
x = '';
console.log(x, x ? true : false);
x = 'false';
console.log(x, x ? true : false);
x = 'true';
console.log(x, x ? true : false);

Here are the results:

As you can see a string returns true if it contains some data.

Validation Messages

2010-07-28T23:15:00.003-06:00

When you have an apostrophe (') in your last name you soon realize that a lot of sites don't handle them properly when entering your information. Normally I get a simple error or my last name will be converted to "D" or "Souza". Today I tried to register for a demo for a fairly popular application and received the following error message:

The error message says "Last Name contains errors". Someone clearly didn't do their job when creating this registration form. Besides the fact that I couldn't register with my last name, stating that my last name "contains errors" doesn't really make sense.

I know this post isn't specific to APEX but this is just a reminder when defining validations to use meaningful error messages.

ODTUG: Enhanced APEX Security

2010-07-26T23:18:00.001-06:00

At ODTUG Kaleidoscope I promised that I would post my slides from my presentation. Here is a copy of the Enhanced APEX Security presentation.

The two demo's can be found here:
http://www.talkapex.com/2009/05/enhancing-apex-security.html
http://www.talkapex.com/2010/07/poor-mans-vpd-in-apex-and-oracle-xe.html

I hope to post the slides from the other presentation, How to be Creative, sometime next week.

Poor Man's VPD in APEX and Oracle XE

2010-07-26T23:03:00.001-06:00

At this year's ODTUG Kaleidoscope I gave a presentation called "Enhancing APEX Security". A copy of the presentation can be downloaded here.

As part of the presentation I discussed how to create a "Poor Man's" VPD using Oracle XE. The main concept was to simulate basic VPD on a non Enterprise Edition (EE) (VPD is only available on EE). This post will cover how to do this. Please note, since this is for demonstration purposes I have kept things very simple and it is by no means a complete solution.

Before you can review the code, we need to discuss some of the basic architecture and technology that will be used. I strongly encourage you to do some additional research on these topics if you plan to use this method in production.

Schema Setup

Assuming you don't have Oracle EE, you'll need a way to secure your existing schema. Lets say you had a schema called "DEMO". You'll need to create a new schema called "DEMO_PUB". The DEMO_PUB schema will not contain any objects. Instead, it will have synonyms which point to views and packages in the DEMO schema. Note, the DEMO_PUB schema will not have any access to the DEMO tables. All DML statements will be made via packages and procedures. The views from the DEMO schema will be "secure views" which will restrict access to the data. On the flip-side the DEMO schema will only grant SELECT and EXECUTE to views and packages respectively, to the DEMO_PUB schema.

You APEX applications should use the DEMO_PUB so that security logic is stored in the database rather than the front-end. This should help prevent developers from displaying data that end users don't have access to.

The following diagram, taken from my presentation, highlights the overall schema structure.

Contexts

For people unfamiliar with Contexts, the easiest way to describe them is a globally accessible container of name/value pairs. The container is only accessible if you have the correct key. Oracle has some great reference material on this (search for VPD or FGAC) so I won't cover this any further Here's a diagram to illustrate Contexts.

The key to "Poor Man's" VPD is to leverage context values in your views to restrict the data. To demonstrate this in pseudo code, if you wanted to restrict access on the EMP table to only employees in your department you'd write a view like this:


CREATE OR REPLACE VIEW vemp AS
SELECT * FROM emp
WHERE deptno = some_value_from_a_context

The Code

Hopefully you understood the small bit of background information I wrote before. Here's how to implement a very simple "VPD" enabled application


-- Create Context
-- ctx_vpd is our context name
-- pkg_vpd is the only place where we can modify values in this context
--  This provides a lot of security since single access point
CREATE OR REPLACE CONTEXT ctx_vpd USING pkg_vpd ACCESSED GLOBALLY;

Create pkg_vpd spec


-- VPD Demo Package from www.talkapex.com
-- @author Martin Giffy D'Souza - martin@talkapex.com
CREATE OR REPLACE PACKAGE pkg_vpd
AS
   -- 
   -- Login function for APEX to login users
   -- Spec must be as is, as required by APEX for custom authentication schemes
   -- For demo purposes we'll use ENAME = ENAME from EMP
   -- @param p_username username
   -- @param p_password password
   -- @return TRUE or FALSE
   -- 
  FUNCTION f_login (p_username IN VARCHAR2, p_password IN VARCHAR2)
    RETURN BOOLEAN;

  -- 
  -- Set context identifier
  -- This will register our "key" which will be required each time we want to access a Context (name/value) pair
  -- @param p_session_key in APEX use :APP_USER || ':' || :APP_SESSION
  -- 
  PROCEDURE sp_set_context_identifier (p_session_key IN VARCHAR2);
 
  -- 
  -- Set context value
  -- i.e. Sets a value for the name/value pair
  -- 
  PROCEDURE sp_set_context_value (p_name IN VARCHAR2, p_value IN VARCHAR2);
  
  -- 
  -- Get Context Value
  -- i.e. Get value for name/value pair
  --
  FUNCTION f_get_context_value (p_name IN VARCHAR2)
    RETURN VARCHAR2;
    
    --
  -- To be run in Post Authentication Process in APEX
  -- Sets some of the name/value pairs required for VPD
  --
  PROCEDURE sp_apex_post_auth;  
    
END pkg_vpd;
/

Create pkg_vpd body


-- VPD Demo Package from www.talkapex.com
-- @author Martin Giffy D'Souza - martin@talkapex.com

CREATE OR REPLACE PACKAGE BODY pkg_vpd
AS
  --
  -- Login function for APEX to login users
  -- Spec must be as is, as required by APEX for custom authentication schemes
  -- For demo purposes we'll use ENAME = ENAME from EMP
  -- @param p_username username
  -- @param p_password password
  -- @return TRUE or FALSE
  --
  FUNCTION f_login (p_username IN VARCHAR2, p_password IN VARCHAR2)
    RETURN BOOLEAN
  AS
    v_count   PLS_INTEGER;
  BEGIN
    SELECT COUNT (e.ename)
      INTO v_count
      FROM emp e -- Notice how I'm not reference the view (vemp) it would return no rows at this point
     WHERE LOWER (e.ename) = LOWER (p_username)
       AND LOWER (p_username) = LOWER (p_password);

    IF v_count = 1 THEN
      RETURN TRUE;
    END IF;

    RETURN FALSE;
  END f_login;

  --
  -- Set context identifier
  -- This will register our "key" which will be required each time we want to access a Context (name/value) pair
  -- @param p_session_key in APEX use :APP_USER || ':' || :APP_SESSION
  --
  PROCEDURE sp_set_context_identifier (p_session_key IN VARCHAR2)
  AS
  BEGIN
    dbms_session.set_identifier (client_id => p_session_key);
  END sp_set_context_identifier;

  --
  -- Set context value
  -- i.e. Sets a value for the name/value pair
  --
  PROCEDURE sp_set_context_value (p_name IN VARCHAR2, p_value IN VARCHAR2)
  AS
  BEGIN
    dbms_session.set_context ('CTX_VPD',
                              p_name,
                              p_value,
                              USER,
                              SYS_CONTEXT ('USERENV', 'CLIENT_IDENTIFIER'));
  END;

  --
  -- Get Context Value
  -- i.e. Get value for name/value pair
  --
  FUNCTION f_get_context_value (p_name IN VARCHAR2)
    RETURN VARCHAR2
  AS
  BEGIN
    RETURN SYS_CONTEXT ('CTX_VPD', p_name);
  END f_get_context_value;

  --
  -- To be run in Post Authentication Process in APEX
  -- Sets some of the name/value pairs required for VPD
  --
  PROCEDURE sp_apex_post_auth
  AS
    v_empno    emp.empno%TYPE;
    v_deptno   emp.deptno%TYPE;
  BEGIN
    -- Get User Information
    SELECT empno, deptno
      INTO v_empno, v_deptno
      FROM emp
     WHERE LOWER (ename) = LOWER (v ('app_user'));

    -- Set Context Identifier
    pkg_vpd.sp_set_context_identifier (p_session_key => v ('app_user') || ':' || v ('app_session'));
    -- Set Name/Value pairs
    pkg_vpd.sp_set_context_value (p_name => 'EMPNO', p_value => v_empno);
    pkg_vpd.sp_set_context_value (p_name => 'DEPTNO', p_value => v_deptno);
    -- This demo won't highlight the Last Access date, but can be very useful to kill sessions in the back end that have not been access for a given period of time.
    pkg_vpd.sp_set_context_value (p_name => 'LAST_ACCESS', p_value => TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
  END sp_apex_post_auth;
END pkg_vpd;
/

Create view which leverages the context


CREATE OR REPLACE FORCE VIEW vemp
AS
  SELECT e.empno,
         e.ename,
         e.job,
         e.mgr,
         e.hiredate,
         e.sal,
         e.comm,
         e.deptno
    FROM emp e
   WHERE e.deptno = pkg_vpd.f_get_context_value ('DEPTNO'); -- Can only view employees in same department
/

Create Custom Authentication Scheme

Shared Components / Authentication Schemes
Create
From Scratch
Name: VPD Demo
Page Sentry Function:
Session Verification Function:
Invalid Session Target: Page in This Application - 101 Login
Pre-Authentication Process:
Credentials Verification Method: Use my custom function to authenticate: return pkg_vpd.f_login
Post-Authentication Process: pkg_vpd.sp_apex_post_auth;
Cookies:
Logout URL: wwv_flow_custom_auth_std.logout?p_this_flow=&APP_ID.&p_next_flow_page_sess=&APP_ID.:1

Change current Authentication Scheme to "VPD Demo". This will vary between APEX 3.x and APEX 4.0

Create a Region Report with:


SELECT *
FROM vemp

Now login to your application with "KING/KING". Notice how you only see 3 rows in the report? Now logout and login with "MARTIN/MARTIN". You should now see 6 rows returned. As you can see, none of security was handled in the front end.

APEX Plugin: Syntax Highlighter

2010-07-16T09:00:00.002-06:00

I finally got around to developing my first APEX Plugin!

To summarize this plugin is a code syntax highlighter based on http://alexgorbatchev.com/SyntaxHighlighter/.
. This is the same syntax highlighter for code samples on this site. You can download it here: http://www.apex-plugin.com/oracle-apex-plugins/item-plugin/syntax-highlighter.html.

Since this was my first APEX plugin that I created I thought I'd give some pointers to help others writing their first plugin:

- Decide on a feature you want to add. This can be harder said than done. If you're new to JavaScript etc, then you may want to create an example in a html file so that you can reference it later on.

- Keep it simple. Chose a simple feature to add. If you decide to add something very complex you may just get lost.

- Put your PL/SQL code in a package/function so that you can develop using a 3rd party tool such as Toad or SQL Developer. This will allow you to quickly debug your code. Once it's working you can extract it and put it inline with your plugin

- Look at other examples. View other plugins and see how they were built etc.

How do they do it?

2010-07-14T09:00:00.000-06:00

Have you ever seen something in the APEX Developer application and wonder how the APEX team built it? Since APEX is built in APEX you can actually see the code for the development environment. The way to do this is to install APEX in APEX. When you download APEX you also get all the development/builder application files:

Simply install each of the applications into a workspace:

Note: Since some of the builder files are extremely large it does take a while to install and you may get a timeout error.

Missing Views from the APEX_DICTIONARY

2010-07-13T09:00:00.001-06:00

A long time ago I wrote about how you can list all the apex views by querying APEX_DICTIONARY. They're some views that are still public but are not listed in the Apex Dictionary. The following query lists these views


SELECT SYN.SYNONYM_NAME apex_view_name
  FROM all_synonyms syn, all_views av, apex_dictionary ad
 WHERE syn.synonym_name LIKE 'APEX\_%' ESCAPE '\'
   AND syn.owner = 'PUBLIC'
   AND SYN.TABLE_NAME = AV.VIEW_NAME
   AND SYN.SYNONYM_NAME = AD.APEX_VIEW_NAME(+)
   AND ad.column_id IS NULL
ORDER BY syn.synonym_name

APEX Report Download Logger

2010-07-06T09:00:00.001-06:00

This solution was demoed at the ODTUG APEX Open Mic night

I developed a reporting application in APEX. The goal of the application is to help turn "data into information". Users should be able to make business decisions based on the reports and charts rather than exporting the report data and using spreadsheets to analyze it.

I've been a bit concerned that the application isn't achieving it's initial goal and that the reports are just used as data dumps. To validate this hypothesis I needed a way to track how many times a report was downloaded and compare it to the number of times it was viewed.

I can find out how many times a report/page was displayed by looking at the APEX logs:


SELECT *
  FROM apex_workspace_activity_log
 WHERE application_id = :app_id
   AND page_id = :app_page_id

Note: this approach is not 100% accurate since it only logs the page and not the individual regions, however for this purpose it should do. I have tinkered with "Region Logging" and may write about it soon.

I still needed a way to track report downloads. Here's how I did it. A working demo can be found here: http://apex.oracle.com/pls/apex/f?p=20195:3120

- Create Download Log table:


CREATE TABLE TAPEX_WORKSPLACE_REPORT_DL_LOG
(
  WORKSPACE                  VARCHAR2 (255 BYTE) NOT NULL,
  WORKSPACE_ID               NUMBER NOT NULL,
  APPLICATION_ID             NUMBER NOT NULL,
  APPLICATION_NAME           VARCHAR2 (255 BYTE) NOT NULL,
  APPLICATION_SCHEMA_OWNER   VARCHAR2 (30 BYTE) NOT NULL,
  PAGE_ID                    NUMBER NOT NULL,
  PAGE_NAME                  VARCHAR2 (255 BYTE) NOT NULL,
  REGION_ID                  NUMBER NOT NULL,
  REGION_NAME                VARCHAR2 (255 BYTE) NOT NULL,
  VIEW_DATE                  DATE NOT NULL,
  APEX_USER                  VARCHAR2 (255 BYTE) NOT NULL,
  APEX_SESSION_ID            NUMBER NOT NULL
);

- Create procedure to store APEX download record


/**
 * Insert Download Report Record
 * @param p_region_id Report Region ID
 */

CREATE OR REPLACE PROCEDURE sp_log_apex_dl_report (
  p_region_id IN apex_application_page_regions.region_id%TYPE)
AS
BEGIN
  -- Insert into report download log table
  INSERT INTO tapex_worksplace_report_dl_log (WORKSPACE,
                                              WORKSPACE_ID,
                                              APPLICATION_ID,
                                              APPLICATION_NAME,
                                              APPLICATION_SCHEMA_OWNER,
                                              PAGE_ID,
                                              PAGE_NAME,
                                              REGION_ID,
                                              REGION_NAME,
                                              VIEW_DATE,
                                              APEX_USER,
                                              APEX_SESSION_ID)
    SELECT aapr.workspace,
           AA.WORKSPACE_ID,
           aapr.application_id,
           aapr.application_name,
           aa.owner,
           aapr.page_id,
           AAPr.PAGE_NAME,
           aapr.region_id,
           aapr.region_name,
           SYSDATE,
           v ('APP_USER'),
           v ('APP_SESSION')
      FROM APEX_APPLICATION_PAGE_regionS aapr, APEX_APPLICATIONS aa
     WHERE aapr.application_id = v ('APP_ID')
       AND AAPr.PAGE_ID = v ('APP_PAGE_ID')
       AND aapr.region_id = p_region_id
       AND aapr.application_id = aa.application_id;
END sp_log_apex_dl_report;

- Trigger the above procedure every time a report is downloaded:

Since Standard Reports (STD) and Interactive Reports (IR) download functionalities are handled differently I can't use an Applicatiobn Process to trigger the logging function. Instead, I'm going to leverage the VPD section in APEX. For those of you new to APEX, the Virtual Private Database (VPD) section in APEX is a section in APEX where you can put a block of PL/SQL code. This code gets run right at the beginning of the page, after the APP_USER is defined. The label of VPD is a bit misleading since the code doesn't have to do any VPD tasks. Initially I had planned to handle IRs and STD Reports using a different method but thanks to the guys at Purdue Pharma for reminding me that I can leverage the VPD section in APEX.

- Shared Components
- Security Attributes
- Virtual Private Database


DECLARE
  v_region_id   APEX_APPLICATION_PAGE_REGIONS.REGION_ID%TYPE;
BEGIN
  -- If CSV/HTMLD is for IR (does not factor in email IRs). FLOW_EXCEL_OUTPUT is for Standard Report
  IF :request IN ('CSV', 'HTMLD')
  OR  :request LIKE ('FLOW_EXCEL_OUTPUT%') THEN
    -- Check for Standard Report
    IF :request LIKE ('FLOW_EXCEL_OUTPUT%') THEN
      v_region_id := REGEXP_SUBSTR (:request, '[[:digit:]]+');
    ELSE
      -- Interactive Report
      SELECT region_id
        INTO v_region_id
        FROM APEX_APPLICATION_PAGE_ir
       WHERE application_id = :app_id
         AND page_id = :app_page_id;
    END IF;

    sp_log_apex_dl_report (p_region_id => v_region_id);
  END IF;
END;

- Comparison report:

Here's the query that I used to compare the downloads to the report views


SELECT AR.APPLICATION_ID,
       ar.application_name,
       ar.page_id,
       ar.page_name,
       ar.region_id,
       ar.region_name,
       tdl.downloads,
       al.page_views
  FROM APEX_APPLICATION_PAGE_REGIONS ar,
       (SELECT tdl.application_id,
               tdl.page_id,
               tdl.region_id,
               COUNT (tdl.application_id) downloads
          FROM TAPEX_WORKSPLACE_REPORT_DL_LOG tdl
        GROUP BY tdl.application_id, tdl.page_id, tdl.region_id) tdl,
       (SELECT al.application_id, al.page_id, COUNT (al.application_id) page_views
          FROM apex_workspace_activity_log al
        GROUP BY al.application_id, al.page_id) al
 WHERE AR.APPLICATION_ID = :app_id
   AND ar.source_type_code IN ('SQL_QUERY', 'DYNAMIC_QUERY')
   -- Downloads
   AND ar.region_id = tdl.region_id(+)
   -- Page Views
   AND ar.application_id = al.application_id(+)
   AND ar.page_id = al.page_id(+)

How to Find Current APEX Version

2010-06-18T09:00:00.000-06:00

Last week my DBA asked me what version of APEX we were running. Instinctively I loaded up the APEX development page and looked in the bottom right hand corner:

This worked, however he needed to dynamically obtain the current version for a script that he was writing. After some digging around he sent me the following query:


SELECT * 
FROM apex_release

Note: this isn't earth shattering but more for my reference

Conditional Validations in APEX

2010-06-17T09:00:00.000-06:00

There may be some instances when you have validations on your APEX page that you only want to run if all the other validations have passed. A good example of this is if you have 2 fields, FROM_DATE and TO_DATE and have the following validations:
- V1: Ensure FROM_DATE is not null and is a valid date
- V2: Ensure TO_DATE is not null and is a valid date
- V3: Ensure FROM_DATE < TO_DATE

In this example you only want the last validation to run if the first 2 pass (i.e. both FROM_DATE and TO_DATE are valid dates). Currently there's no declarative way of doing this. i.e. validations don't support declarative dependencies.

A simple trick to get around this issue is to put a condition in some validations to only run if all the other validations pass. This may not work in all situations, however it should help you in some.

In the example about, modify the 3rd validation (V3) and set the condition to the following:
Condition Type: PL/SQL Expression
Expression 1: apex_application.g_inline_validation_error_cnt = 0

Now V3 will only run if all previous validations pass.

Special columns for APEX Standard Reports

2010-06-16T09:00:00.001-06:00

I was testing APEX 4.0 on http://apex.oracle.com and I noticed that on Standard Reports you can add 2 types of "special" columns: Derived Column and Column Link. At first I thought this was a new APEX 4.0 feature, but then tested on a copy of 3.x noticed it was there as well! I apologize if this is old news to people but I found it to be a pretty neat feature.

The interesting thing about these columns is that you can add Derived Column and Column Link columns and it does not modify your existing SQL.

The only difference I can tell about these 2 column types is that Derived Columns don't contain the following attributes: List of Values, Tabular Form Attributes, and Column Link

How to only Display Column when Downloading

2010-06-15T09:00:00.003-06:00

Sometimes you may have a column that contains inline HTML. A simple example of this is if you have a column called "color" and you want to display the color in the report. Your query would look like this:


SELECT '"' || color || '' color
FROM my_colors

Note: That they're ways to get around this simple example using Report Templates and Column Formatting. I'm just using it for demo purposes

If you were to download this report the "color" column would contain all the html (i.e. span tags etc). This may confuse users since they expected to see "red, green, blue, etc..." in their download file, but instead see the colors wrapped in a lot of html.

A workaround that I've used is to create 2 columns: color_html and color


SELECT '"' || color || '' color_html, 
            color color
  FROM my_colors

And modify each report column's attributes:

Standard Reports

color_html
Column Definition:
Include In Export: No

color
Conditional Display:
PL/SQL Function Body Returning a Boolean: return apex_application.g_excel_format

Interactive Reports

color_html
Conditional Display:
Request is NOT Contained within Expression 1: CSV,HTMLD

color
Conditional Display:
Request is Contained within Expression 1: CSV,HTMLD

Now when a user downloads the report they'll get the non-html version of the column. This solution works in APEX 4.0 and APEX 3.x

Interactive Reports with over 100 Columns

2010-06-14T09:00:00.000-06:00

I ran into an issue last week where I had an Interactive Report (IR) with over 100 columns. All 100 plus columns displayed for end users, however I was only able to modify the first 100 columns. For example, the 101st column did not display in the report attributes screen (see screen shot below)

After some digging around I figured out how to modify columns that weren't displayed on the Report Attributes screen. This demo requires Firefox and Firebug. You should also be able to do this in Google Chrome. This demo works for APEX 3.x and APEX 4.0.

Build Large IR:


SELECT LEVEL c001, LEVEL c002, LEVEL c003, LEVEL c004, LEVEL c005, LEVEL c006, LEVEL c007,
       LEVEL c008, LEVEL c009, LEVEL c010, LEVEL c011, LEVEL c012, LEVEL c013, LEVEL c014, 
       LEVEL c015, LEVEL c016, LEVEL c017, LEVEL c018, LEVEL c019, LEVEL c020, LEVEL c021, 
       LEVEL c022, LEVEL c023, LEVEL c024, LEVEL c025, LEVEL c026, LEVEL c027, LEVEL c028, 
       LEVEL c029, LEVEL c030, LEVEL c031, LEVEL c032, LEVEL c033, LEVEL c034, LEVEL c035, 
       LEVEL c036, LEVEL c037, LEVEL c038, LEVEL c039, LEVEL c040, LEVEL c041, LEVEL c042, 
       LEVEL c043, LEVEL c044, LEVEL c045, LEVEL c046, LEVEL c047, LEVEL c048, LEVEL c049, 
       LEVEL c050, LEVEL c051, LEVEL c052, LEVEL c053, LEVEL c054, LEVEL c055, LEVEL c056, 
       LEVEL c057, LEVEL c058, LEVEL c059, LEVEL c060, LEVEL c061, LEVEL c062, LEVEL c063,
       LEVEL c064, LEVEL c065, LEVEL c066, LEVEL c067, LEVEL c068, LEVEL c069, LEVEL c070,
       LEVEL c071, LEVEL c072, LEVEL c073, LEVEL c074, LEVEL c075, LEVEL c076, LEVEL c077, 
       LEVEL c078, LEVEL c079, LEVEL c080, LEVEL c081, LEVEL c082, LEVEL c083, LEVEL c084, 
       LEVEL c085, LEVEL c086, LEVEL c087, LEVEL c088, LEVEL c089, LEVEL c090, LEVEL c091, 
       LEVEL c092, LEVEL c093, LEVEL c094, LEVEL c095, LEVEL c096, LEVEL c097, LEVEL c098, 
       LEVEL c099, LEVEL c100, LEVEL c101, LEVEL c102, LEVEL c103, LEVEL c104, LEVEL c105, 
       LEVEL c106, LEVEL c107, LEVEL c108
  FROM DUAL
CONNECT BY LEVEL <= 10

Get the COLUMN_ID

This is the column that you want to modify that isn't currently displayed on the Reports Attributes page


SELECT column_id,
       column_alias,
       display_order,
       report_label
  FROM APEX_APPLICATION_PAGE_IR_COL
 WHERE application_id = :app_id
   AND page_id = :page_id

Run

Go to the Reports Attributes screen (the screen that lists all the IR columns)

Open Firebug and go to the Console Window. Enter the following:


apex_p601_setColumnIDandSubmit('311507131164520006'); // Enter your column ID here

New URL: http://TalkApex.com

2010-06-10T09:00:00.000-06:00

Well it's been a while since I've had any new posts on this blog. I've been extremely busy with "after school" activities (soccer, speed skating, biking, etc...)

I don't have an APEX specific post today but I do have a few items to discuss. The first, in case you haven't noticed yet, is that I've changed the URL of this blog from http://apex-smb.blogspot.com to http://TalkApex.com, The old URL, http://apex-smb.blogspot.com will still work so you don't need to update any old links.

Just because I haven't had time to write about APEX, doesn't mean I've forgotten about this blog. I've been compiling a fairly large list of new things to write about over the past few months. I hope to spend a some time soon to write about these items.

Last, but not least, I'll be giving 2 presentations at ODTUG Kaleidoscope. I submitted my presentation for the CD, however I think that they'll change significantly by the time I present. As such I will post the final slides used for my presentation here after the conference. The presentations I'll be giving are

Enhancing APEX Security: Monday 2:30~3:30

How to be Creative: Using the APEX Dictionary to Create Solutions: Tuesday 4:45~5:45

Hope to see most of you at ODTUG!

- Martin

APEX Logs: Storing Mail Log Data

2010-04-13T09:00:00.000-06:00

A while ago (almost a year ago) I wrote about how to permanently store APEX logs into your own tables (http://apex-smb.blogspot.com/2009/05/apex-logs-storing-log-data.html). I covered the APEX_WORKSPACE_ACCESS_LOG and APEX_WORKSPACE_ACTIVITY_LOG logs. I forgot to include how to permanently store the APEX_MAIL_LOG. You'll only need to do this if you use APEX_MAIL to send email.

1- Create the APEX Mail Log table


-- Mail Log
CREATE TABLE tapex_mail_log
AS  SELECT * FROM apex_mail_log;

2- Update the APEX Mail Log table

Note: You may want to store this in a procedure and run as a nightly scheduled job so you don't forget to update the tables


BEGIN
  -- apex_mail_log is linked to the workspace.
  -- Need to access all the mail sent for each workspace that is linked to this schema

  FOR x IN (SELECT workspace_id FROM apex_workspaces) LOOP
    -- Set the workspace ID
    wwv_flow_api.set_security_group_id (x.workspace_id);

    INSERT INTO tapex_mail_log (mail_to,
                                mail_from,
                                mail_replyto,
                                mail_subj,
                                mail_cc,
                                mail_bcc,
                                mail_send_error,
                                last_updated_on)
      SELECT aml.mail_to,
             aml.mail_from,
             aml.mail_replyto,
             aml.mail_subj,
             aml.mail_cc,
             aml.mail_bcc,
             aml.mail_send_error,
             aml.last_updated_on
        FROM tapex_mail_log x, apex_mail_log aml
       WHERE     NVL (aml.mail_to, -1) = NVL (x.mail_to(+), -1)
             AND NVL (aml.mail_from, -1) = NVL (x.mail_from(+), -1)
             AND NVL (aml.mail_replyto, -1) = NVL (x.mail_replyto(+), -1)
             AND NVL (aml.mail_subj, -1) = NVL (x.mail_subj(+), -1)
             AND NVL (aml.mail_cc, -1) = NVL (x.mail_cc(+), -1)
             AND NVL (aml.mail_bcc, -1) = NVL (x.mail_bcc(+), -1)
             AND NVL (aml.mail_send_error, -1) = NVL (x.mail_send_error(+), -1)
             AND aml.last_updated_on = x.last_updated_on(+)
             AND x.ROWID IS NULL;
  END LOOP;
END;

Restoring jQuery UI Theme from ThemeRoller

2010-03-25T09:00:00.002-06:00

If you ever use jQuery's Theme Roller, you may find it frustrating to alter a custom theme. For example, if you created a theme, downloaded it, then decided to change your colors you may not know how to restore your changes.

Believe it or not there's a pretty easy way to restore a Theme Roller theme. Before you download a theme copy, or bookmark, the URL at the top of the browser. All the changes you have made are stored in the URL! If you forget to copy the URL before downloading, the URL is stored in ..\css\custom-theme\jquery-ui-1.8.custom.css (where 1.8 is the version number).

Here's an example of a theme modification where I changed the header background to red: Red Header.

APEX 4.0 will be using jQuery and some jQuery UI components, so I hope this helps when customizing the look and feel of your applications.

How To Reference Package Variables Outside of PL/SQL

2010-03-24T09:00:00.000-06:00

When developing with PL/SQL you may store public variables in the package specification. This has many uses, none of which I will get into for this post. The only catch in Oracle is that you can not easily reference these values in SQL statements outside of PL/SQL. The following example demonstrates this:

- Create Package Spec with Variable


CREATE OR REPLACE PACKAGE pkg_var
AS
  c_my_var   CONSTANT VARCHAR2 (5) := 'hello';
END pkg_var;

- Reference the variable in a SQL statement in SQL*Plus


SQL> SELECT pkg_var.c_my_var x
  2    FROM DUAL;
SELECT pkg_var.c_my_var x
       *
ERROR at line 1:
ORA-06553: PLS-221: 'C_MY_VAR' is not a procedure or is undefined

This results in an Oracle error.

- Try the same code, but in an block of PL/SQL


SQL> DECLARE
  2    v_x   VARCHAR2 (5);
  3  BEGIN
  4    SELECT pkg_var.c_my_var x
  5      INTO v_x
  6      FROM DUAL;
  7
  8    DBMS_OUTPUT.put_line (v_x);
  9  END;
 10  /
hello

PL/SQL procedure successfully completed.

As you can see this worked.

So how can we refernce package variables in a non-PL/SQL setting? I created the following function to do so. It will handle values that are of type VARCHAR2. I've also removed any spaces from the parameter (pkg_name.var_name) to ensure that no SQL injection will occur.


-- **
-- * Returns Package Variable value
-- * Note: for demo purposes I broke this function into various steps
-- * 
-- * @param p_pkg_var_name fully qualified variable reference. Ex: pkg_x.var_y
-- * @return Varchar2 value
-- * @author Martin Giffy D'Souza: http://apex-smb.blogspot.com
-- **
CREATE OR REPLACE FUNCTION f_get_pkg_val_vc2 (p_pkg_var_name in varchar2)
  RETURN VARCHAR2
AS
  v_string          VARCHAR2 (4000);
  -- Full Variable Name (i.e. pkg.var)
  v_var_full_name   VARCHAR2 (61);                             -- Max of 61 chars since 30 + . + 30
BEGIN
  v_var_full_name := p_pkg_var_name;
  -- Remove any spaces to avoid SQL injections
  v_var_full_name := REGEXP_REPLACE (v_var_full_name, '[[:space:]]', '');

  EXECUTE IMMEDIATE 'begin :v_string := ' || v_var_full_name || '; end;'
              USING OUT v_string;

  RETURN v_string;
END f_get_pkg_val_vc2;

Now when you run in SQL*Plus you get the following:


SQL> SELECT f_get_pkg_val_vc2 ('pkg_var.c_my_var') x
  2    FROM DUAL;

X
-----------------------------------------------------

hello

How to Avoid Bot Spammers in APEX

2010-02-11T09:00:00.005-07:00

If you've ever developed a public web application with a form on it you may notice that you may get bot spammers trying to enter information into your application.

There's a simple trick that a friend of mine, Sean Rabey, at Pump Interactive showed me which will help you reject submissions from bots.

Sean suggested that I use an input field and then hide it with CSS. Humans entering data into the form won't see the field and therefore won't enter anything into it. Bots on the other hand may try to fill out this field and can't detect whether or not it's visible in the browser. If your "special" field has data in it you can reject the submission since you know it's not a human entering the data.

Here's an example of how you can do this in APEX. You can view an example here: http://apex.oracle.com/pls/apex/f?p=20195:2900

- Create a "Dummy" item
Set "HTML Form Element Attributes" to class="hideMe"

- Configure "hideMe" style
Add the following in your application somewhere (or to a CSS file)

- Add validation to catch bot entries
Type: Exists
Validation Expression 1:


SELECT 1
  FROM DUAL
 WHERE :p2900_dummy IS NULL

Presenting at ODTUG Kaleidoscope 2010

2010-02-01T09:00:00.000-07:00

I'll be presenting at ODTUG Kaleidoscope in Washington DC this year. If you haven't already signed up I suggest you do so before March 24th as they have an early bird special.

I've been to the past 2 Kaleidoscope conferences where I've learned a great deal about APEX and met some great people. It doesn't matter if you're a seasoned APEX developer or new to APEX, there's always something to learn and a lot of excellent presentations for all levels.

This year I'll be giving 2 presentations on APEX (http://www.odtugkaleidoscope.com/apex.html#dsouza)

Enhancing APEX Security: APEX has some excellent built-in configurable security features. This presentation will go over some extra functionality you can add to your APEX applications that will make it more secure both in the front-end and back-end. Primary focus will be on "enhanced session state protection" and "poor man's" VPD for Oracle XE.

How to be Creative: Using the APEX Dictionary to Create Solutions: The APEX dictionary is a very useful tool which can help enhance existing features in APEX. This presentation will cover how you can use the dictionary to resolve your problems. It will include some real-life issues and how the APEX dictionary was used to resolve them

Of course these presentations may be slightly altered based on the new features and functionality of APEX 4.0.

I look forward to seeing everyone in Washington.

How to Automatically Remove Items and Values in APEX URLs

2010-01-03T23:25:00.000-07:00

A while ago I wrote about how to pass multi-select lists through the URL: http://apex-smb.blogspot.com/2009/07/apex-how-to-pass-multiselect-list.html. This method works, however it will only support up to 4000 characters as a result of the functions it uses.

What happens when a user selects more than 4000 characters? You could update the processes to handle clobs etc. I took a different approach when faced with this problem. Instead of trying to update the process to handle clobs I asked: Why am I passing the values through the URL? Wouldn't it be easier if I didn't pass values through the URL?

When I first thought about this, I thought I could run a process on each page that would set the appropriate variables to the calling page. But then developers would have to look in the page branch and page processes to see what items were being passed to which pages. This sounded confusing and goes against the APEX framework. I really don't like having developers do extra work when they don't need to or go against standards already put in place.

I took a different approach to this problem. Instead of having a special page process to pass values from one page to another I was able to "intercept" the page branch and modify it before it was executed. I did this by taking the page branch and manually setting the item/value pairs, then removing them from the page branch. Here's an example of the final solution: http://apex.oracle.com/pls/otn/f?p=20195:2800.

Here are some screen shots from the demo application:

Page Branch configuration:

Passing in values via the URL (note that I'd like "X" to be "abc:def", however it's just abc)

Running the page process to manually remove the item/value pairs from the URL:

Before reviewing the code, it's important to note the following assumptions. I made these assumptions to make the code demo easier to read.

- Only 1 branch is defined on the page with no conditions etc.
- The branch only contains at most 1 clear cache
- The branch passes as most 1 item/value pair to the other page

- Create page process: Remove URL Params
- Branch Point: On Submit After Processing

Note: You could easily turn this into an application process to be run throughout your application


-- NOTE: For the purpose of this example I'm assuming the following:
--   Only 1 branch is defined with no conditions etc.
--   The branch only contains at most 1 clear cache
--   The branch passes as most 1 item/value pair to the other page

DECLARE
   v_branch_action   apex_application_page_branches.branch_action%TYPE;
   p_app_id          apex_application_page_branches.application_id%TYPE := :app_id;
   p_page_id         apex_application_page_branches.page_id%TYPE := :app_page_id;
   v_clear_cache     NUMBER;
   v_item_names      VARCHAR2 (4000);
   v_item_values     VARCHAR2 (4000);
BEGIN
   -- Get branch action
   -- The branch action is the URL before it is parsed by APEX
   SELECT branch_action
     INTO v_branch_action
     FROM apex_application_page_branches
    WHERE application_id = p_app_id AND page_id = p_page_id;

   -- Get the clear cache options
   v_clear_cache :=
      SUBSTR (v_branch_action,
              INSTR (v_branch_action, ':', 1, 5) + 1,
                INSTR (v_branch_action, ':', 1, 6)
              - INSTR (v_branch_action, ':', 1, 5)
              - 1
             );

   -- Manually clear the cache if required
   IF v_clear_cache IS NOT NULL
   THEN
      apex_util.clear_page_cache (p_page_id => v_clear_cache);
   END IF;

   -- Get Page Param values
   v_item_names :=
      SUBSTR (v_branch_action,
              INSTR (v_branch_action, ':', 1, 6) + 1,
                INSTR (v_branch_action, ':', 1, 7)
              - INSTR (v_branch_action, ':', 1, 6)
              - 1
             );
   v_item_values :=
      SUBSTR (v_branch_action,
              INSTR (v_branch_action, ':', 1, 7) + 1,
                INSTR (v_branch_action, '&success_msg=', 1, 1)
              - INSTR (v_branch_action, ':', 1, 7)
              - 1
             );

   -- If item/value pairs exist, manually set the values
   IF v_item_names IS NOT NULL
   THEN
      -- See: http://apex-smb.blogspot.com/2009/07/apexapplicationdosubstitutions.html for more info on apex_application.do_substitutions
      apex_util.set_session_state
           (p_name       => v_item_names,
            p_value      => apex_application.do_substitutions (TRIM (v_item_values))
           );
   END IF;

   -- Modify the branch action
   -- Remove the clear cache option if applicable
   IF v_clear_cache IS NOT NULL
   THEN
      v_branch_action :=
            SUBSTR (v_branch_action, 1, INSTR (v_branch_action, ':', 1, 5))
         || SUBSTR (v_branch_action, INSTR (v_branch_action, ':', 1, 6));
   END IF;

   -- Remove the item name/value pairs if applicable
   IF v_item_names IS NOT NULL
   THEN
      -- Remove item Names
      v_branch_action :=
            SUBSTR (v_branch_action, 1, INSTR (v_branch_action, ':', 1, 6))
         || SUBSTR (v_branch_action, INSTR (v_branch_action, ':', 1, 7));
      -- Remove item values
      v_branch_action :=
            SUBSTR (v_branch_action, 1, INSTR (v_branch_action, ':', 1, 7))
         || SUBSTR (v_branch_action,
                    INSTR (v_branch_action, '&success_msg=', 1, 1)
                   );
   END IF;

   -- Set the new branch action
   apex_application.g_branch_action (1) := v_branch_action;
END;

APEX Orphaned Application Files

2009-12-01T09:00:00.000-07:00

If you allow end users to upload files to your APEX application you may have a lot of "orphaned" files in apex_application_files and not even realize it.

Orphaned files are files that exist in APEX_APPLICATION_FILES that are not associated with an application. This can happen for several reasons, the most common are:

Files uploaded in Shared Components that aren't associated to an application

End users uploading files then purposely keep them in APEX_APPLICATION_FILES

End users uploading files and an error occurs

You can easily identify orphaned files using the APEX_APPLICATION_FILES view:


SELECT *
  FROM apex_application_files
 WHERE flow_id = 0 -- flow_id is the same as application_id

All 3 situations listed above will result in the file uploaded with flow_id = 0. The last 2 points, files uploaded from end users, can result in files that you may no longer need. I don't recommend that you keep uploaded files in the APEX_APPLICATION_FILES view. Instead you should move them immediately to a custom table.

The main problem comes from the third point. When a user uploads a file and a validation fails. In this situation the file is uploaded to APEX_APPLICATION_FILES and then the validation fails. Even though the validation failed, the file still resides in APEX_APPLICATION_FILES. The following screen shot demonstrates this issue.

To resolve this issue, I run the following application process which automatically "tags" uploaded files with a flow_id of -1. By doing so you can run a nightly process to delete any files that have a flow_id of -1.

Application Process: AP_TAG_APEX_FILES
Sequence: -100
Point: On Submit: After Page Submission - Before Computations and Validations


-- AP_TAG_APEX_FILES
BEGIN
   FOR x IN (SELECT v (item_name) item_name_value
               FROM apex_application_page_items
              WHERE application_id = :app_id
                AND page_id = :app_page_id
                AND display_as = 'File Browse...')
   LOOP
      UPDATE apex_application_files aaf
         SET aaf.flow_id = -1
       WHERE aaf.NAME = x.item_name_value;
   END LOOP;
END;

Using Decimals in APEX Page Numbers

2009-11-16T09:30:00.000-07:00

When creating an APEX page you can use decimals in the page number. If you logically group your pages by page numbers and run out of page numbers this may be useful. For example if you create pages 1 through 10 then realize you'd like a page right beside page 5, you could create Page 5.1.

I don't personally recommend using decimals in page numbers as you can't use decimals in item names, so P5.1_X won't work. Of course you may find some other use for creating pages with decimals.

If you're concerned about logically grouping pages for your development try initially separating pages by increments of 100 or by using Page Groups. To configure Page Groups go to the main application development page and click on "Page Groups" on the right hand side. Once you've setup the group and assigned pages, go back to the main development page and under the "View" drop down select "Groups".

Standardizing Help Text in APEX

2009-10-27T09:00:00.003-06:00

I had a requirement where the same item name was used in various areas of the application. To maintain consistancy, and to minimize development time, I decided to create a "help framework" to standardize on the help text for these items.

Note: I know APEX comes with a great tool to maintain consistancy for your page items called User Interface Defaults, however it would not work in this case.

The goal was for developers not to have to enter any help text for commonly named page items. Instead, when applicable, the help text would automatically be retreived from a common area so we'd only need to maintain it in one place. The design also had to handle custom help text (i.e. if we needed to override the default help text).

To do this I used Tool Tip Help (see: http://apex-smb.blogspot.com/2009/09/tooltip-help-in-apex-alternative-to.html) and made the following modifications:

- Create a Default Help Page
Page Name: Default Help Page

Create a region called "Default Help Items" and add all the items you want default help text for. For example, I created P10 and added the following items: P10_EMPNO, P10_ENAME. In each of these items I added the default help text for similarly named items.

- Modify the Application Process AP_GET_HELP_TEXT


BEGIN
  FOR x IN (SELECT    ''
                   || item_help_text
                   || '' help_html
              FROM (SELECT a.item_name,
                           NVL (a.item_help_text, dflt_help_text.item_help_text)
                                                                           item_help_text
                      FROM apex_application_page_items a,
                           (SELECT 'P' || :app_page_id || '_' || LTRIM (item_name, 'P10_')
                                                                                item_name,
                                   item_help_text
                              FROM apex_application_page_items
                             WHERE application_id = :app_id
                               AND page_id = 10 -- Enter Default Help Page number here (and modify select statement above)
                               AND item_help_text IS NOT NULL) dflt_help_text
                     WHERE a.application_id = :app_id
                       AND a.page_id = :app_page_id
                       AND a.item_name = dflt_help_text.item_name(+)
                       AND NVL (a.item_help_text, 'null') != '@NO_HELP@')
             WHERE item_help_text IS NOT NULL)
  LOOP
    HTP.p (x.help_html);
  END LOOP;
END;

Now when I run the application, any item that is called PXX_EMPNO or PXX_ENAME will try to use the help text from P10 for the corresponding items. Developers can easily override the default help text by filling in specific help text for an item or by entering in "@NO_HELP@"

Saving Saved Interactive Reports when Updating Application

2009-10-26T08:25:00.002-06:00

Their is now a supported method to preserve saved interactive reports. Please read the following post which explains how to do this in APEX 4.0: http://joelkallman.blogspot.com/2010/07/where-did-my-saved-interactive-reports.html

When updating existing APEX applications that contain Interactive Reports (IR) you may, not knowningly, delete users saved IRs. The only supported way to prevent this from happening is to ensure that your Application ID is the same when you move it from Dev, to Test, to Production. David Peake wrote a full explanation of this issue here: http://dpeake.blogspot.com/2009/01/preserving-user-saved-interactive.html. I suggest you read his post before continuing.

What if you develop a single application that needed to be deployed to multiple clients/instances? I.e. you develop your application in DEV (100) and deploy to PROD (200), PROD (300), and PROD (400). Currently there's no supported way of doing this while maintaining your saved interactive reports.

The following script can be run after updating your production applications to ensure that your saved IRs don't get lost. Please note that this is not supported by Oracle and can put your application in an unsupported state. If you are not an advanced APEX developer I do not suggest using this as it may result in unexpected results.

Besides preserving saved IRs, users who are currently on the system will retain their current IR configurations, otherwise they will be lost. For example a user is working on an IR and applys some filters to it, you then update the application which will cause the interactive_report_id to change. The next time the user refreshes the page they won't see their filters any more (i.e. they'll have the default IR again). Running this script will prevent this from happening.

Note: this must be run as SYSTEM or a user with SYSTEM level access


BEGIN
  FOR x IN (SELECT a.ID, a.name, a.session_id,
                   b.interactive_report_id
              FROM apex_030200.wwv_flow_worksheet_rpts a,  -- This could also be flows...
                   apex_application_page_ir b
             WHERE a.flow_id = :app_id
               AND a.page_id = b.page_id -- Linking is done via the page so please be aware of any IR page changes
               AND b.application_id = a.flow_id
               AND a.worksheet_id != b.interactive_report_id
               and a.status = 'PRIVATE'
               )
  LOOP
    UPDATE apex_030200.wwv_flow_worksheet_rpts
       SET worksheet_id = x.interactive_report_id
     WHERE ID = x.ID;
     
  END LOOP;
END;

Designing APEX Applications: UI Mockups

2009-10-22T08:30:00.000-06:00

When doing any sort of development it's important to use the right tool for the right job. With that in mind, I thought I'd discuss mockups for APEX applications, or better yet any GUI.

One of my biggest pet peeves is when I get a functional or technical spec with modified screen shots of existing applications for the UI mockups. I'm not a graphic artist or a UI expert but I do know that taking a screen shot of a similar application, then spending several hours moving things around in Paint isn't very good. Using MS Paint, or similar applications, would be like developing your application using a typewriter (I've included a picture for those that have never used one :-).
I know some people have developed basic APEX applications and used screen shots for their UI mockups. I think APEX is a great tool for creating demo applications but it shouldn't be used for mockups. People reviewing the design may get carried away with the specifics since it looks like a real application.

What should you use to create application mockups? Personally I like a good old-fashion hand drawn diagram (I've been known to ask colleagues to give me their mockups on scrap paper). Though they are quick they don't work well in documents and they aren't great when doing several design iterations. Wireframe diagrams are an excellent alternative as they're exactly like hand drawn diagrams with pre-defined shapes.

Why use wireframe diagrams?

Quick to develop (the example above took me 2 minutes to create)

Easy to modify

Focus on flow and useability, not specifics

Cheap/Easy to do multiple design iterations

The list could go on, but I'll stop there. I'd like to emphasize on the fact that people will focus on specifics rather than the flow and usability of the application when looking at screen shots. For example: if you use have a screen shot with a drop down list of 100 countries and you only include 2 or 3 in the diagram, readers may wonder where the other 100 or so countries are etc. They aren't focused on the fact that it's a drop down list with countries, rather there's something wrong with the list. With a wireframe diagram it will be extremly clear that it's a drop down list of countries with no real focus on the values inside the select list.

I've you're looking for an application to do wireframe diagrams I'd recommend Balsamiq. I've been really impressed with it so far as it allows me to do UI mockups really quickly (I have no affiliation with Balsamiq, so please don't take this as an advertisement). Of course they're many other tools out there that will do this so I encourage you to find one that is suitable for your organization.

Oracle and APEX Jobs

2009-10-15T03:25:00.006-06:00

A lot of people were affected by the recession and may have lost their jobs. I know they're several companies looking for talented workers. To help out I've decided to start a small Jobs section on my blog (see right hand side). If you're interested in submitting a job, please leave a comment on this post with the following information:

- Job Title
- City the job is in
- Link to position

To keep with the theme of this blog please only submit Oracle (SQL,PL/SQL) and APEX related jobs.

On a side note I may create a small application where people can post jobs specific to APEX.

apex.stackexchange.com

2009-10-08T05:17:00.004-06:00

stackoverflow is a programming Q & A site that Jeff Atwood and Joel Spolsky created a while ago. It is now a very popular programming Q & A site and takes a different approach to asking questions then conventional forums.

They recently launched a site which allows individuals to host their own version of the application that focuses on a specific topic. I decided to register apex.stackexchange.com for Oracle Application Express (APEX) questions.

I realize that the APEX Forum is the main source of questions and answers regarding APEX, but I thought I'd see if anyone was interested in a different approach to handle questions.

If you're interested in trying it out, please go to: http://apex.stackexchange.com. You don't need to register to ask a question, but if you want to you can register using an OpenID account.

APEX Developer Competition 2009 - Top 12

2009-09-25T06:15:00.000-06:00

Yesterday I found out that the application I submitted for the Oracle Application Express Developer Competition 2009 finished in the top 12! You can view all the winners here: http://www.oracle.com/technology/products/database/application_express/html/comp_winners.html

You can read about my application here: http://apex-smb.blogspot.com/2009/09/apex-rules-guidelines.html. There's a link where you can download a copy of it as well.

I put together my application rather quickly in a weekend so it does have some bugs. I'll try to update it soon for those that are using / interested in using it in their organization.

Thank you to all the judges and congrats to everyone that participated!

Enhanced APEX Session Timeouts

2009-09-21T09:00:00.020-06:00

APEX has built in logic to set the lifetime of a session. To configure this option go to Shared Components / Edit Security Attributes / Maximum Session Idle Time in Seconds and set the time in seconds

This essentially terminates the user's session in the database and the next time they submit the page they'll be redirected to the login screen. The user will only know that they are logged out once they submit the page. If you have an Interactive Report (IR), or use Partial Page Refresh (PPR) the users won't know they're logged out. Instead it will look as though the report is still trying to load.

Another situation that may happen is that the user is filling out a long form on your page, their session timesout, then they click "submit". They'll be redirected to the login page and they'll lose all the information that they entered.

What if a user wants to extend their session? i.e. they haven't done anything to the page but would like a warning message before we automatically log them out? Or they are entering a log form and don't want to be logged out? I got this idea from the Air Canada web site when I was booking tickets. I really liked the fact that they let me know that they were to end my session, and gave me the option to extend my session.

The following solution will allow you to use APEX's session timeout and resolve the issues listed above. You can view the demo here: http://apex.oracle.com/pls/otn/f?p=20195:2600

Please note that since the demo page is set to public you can refresh after the session is supposed to have timedout and it will still work. If you set the Idle Session in APEX, this will work for pages that require authentication

Here's a high level overview of what this solution does:

Start a timer (pingApexSession) that will constantly "ping" (therefore refresh) your APEX session every X seconds.

Start a timer (idleTimer) to detect movement on the page.

If the idleTimer times out, give the user the option to extend session

If the user does not extend their session, terminate their session

I haven't put this code into a production application yet. As I mention below, I plan to make a jQuery plugin for this, so if you please send me any feedback that would be useful fur the plugin.

This solution uses jQuery and the following plugins:

Simple Modal

jQuery idleTimer

jApex

jQuery Countdown

- Create Application Process: AP_NULL
- Process Point: On Demand
- Name: AP_NULL
- Type: PL/SQL Anonymous Block
- Process Text: NULL;

- Create Application Process: AP_LOGOUT
- Process Point: On Demand
- Name: AP_LOGOUT
- Type: PL/SQL Anonymous Block
- Process Text:


BEGIN
  apex_custom_auth.LOGOUT (p_this_app                   => :app_id,
                           p_next_app_page_sess         => :app_id || ':1');
END;

- Create Region: "Extend Session" on Page 0
- Title: Extend Session
- Type: HTML Text
- Static ID: P0_REG_EXTEND_SESSION
- Region Attributes: style="display:none"
- Region Source: Your session will timeout in: <span id="timeoutCountdownDisplay" style="font-weight:bold"></span>
You can put whatever message you want. Just make sure the span tags exist for the countdown timer

- Create Button: "Extend Session" on Page 0
- Button Name: EXTEND_SESSION
- Text Label: Extend Session
- Display in Region: Extend Session
- Target is a: URL
- URL Target: javascript:gTimeout.timers.killSession.liveFn();

- Create Region: "Session Timedout" on Page 0
- Title: Session Ended
- Type: HTML Text
- Static ID: P0_REG_SESSION_ENDED
- Region Attributes: style="display:none"
- Region Source: Your session has ended. Please login.

- Create Button: "Login" on Page 0
- Button Name: LOGIN
- Text Label: Login
- Display in Region: Session Ended
- Target is a: Page in this Application
- Page: 1

- Create Region: "JavaScript - Session Timeout" on Page 0
- Title: JavaScript - Session Timeout
- Type: HTML Text
- Template: No Template
- Region Source:

You'll need to upload the JS files beforehand. Please see the list above to obtain the files

Here's the script to put into the region above. I separated them for display purposes.

I probably should have created this as a jQuery plugin. I may convert it later on


var gTimeout = {
  //debug
  debug: false, //Set to True to turn on debugging
  debugFn: function(pMsg) {
    if (gTimeout.debug){
      console.log(pMsg); 
    }
  }, //debug
  modalRegions: {
    //Region that contains the "Extend Session" information
    extendSession: {
      id: 'P0_REG_EXTEND_SESSION',
      backgroundColor: '#CCC',
      opacity: 70,
      openFn: function() {
        gTimeout.debugFn('gTimeout.modalRegions.extendSession.openFn');
        // Start display timeout counter
        $('#timeoutCountdownDisplay').countdown('destroy');
        $('#timeoutCountdownDisplay').countdown({
          until: '+' + (gTimeout.timers.killSession.time / 1000),
          compact: true, 
          format: 'M:S'
        });
        // Load modal box to give user option to extend session
        $('#' + gTimeout.modalRegions.extendSession.id).modal({
          overlayCss: {backgroundColor: this.backgroundColor},
          opacity: this.opacity
        });
        return;
      }, //openFn
      closeFn: function(){
        gTimeout.debugFn('gTimeout.modalRegions.extendSession.closeFn');
        $.modal.close();
        return;
      }//closeFn
    },
    //Region that will be displayed if the user does not extend thier session
    sessionEnded: {
      id: 'P0_REG_SESSION_ENDED',
      backgroundColor: 'black',
      opacity: 70,
      openFn: function() {
        gTimeout.debugFn('gTimeout.modalRegions.sessionEnded.openFn');
        // Close Extend Sessios modal window
        gTimeout.modalRegions.extendSession.closeFn();
        // Open Logout modal window
        $('#' + gTimeout.modalRegions.sessionEnded.id).modal({
          overlayCss: {backgroundColor: this.backgroundColor},
          opacity: this.opacity
        });
        return;
      }// openFn
    }//sessionEnded
  },//modalRegions
  timers: {
    //Ping APEX Session timer will update the database session timer 
    pingApexSession: {
      id: -1,
      time: 5000, //Time to keep database session alive. This should be really close to the APEX idle time
      loadFn: function(){
        gTimeout.debugFn('gTimeout.timers.pingApexSession.loadFn:');
        this.id = setTimeout('gTimeout.timers.pingApexSession.fn();', this.time);
        return;
      },
      unloadFn: function(){
        gTimeout.debugFn('gTimeout.timers.pingApexSession.unloadFn:');
        clearTimeout(this.id); 
        this.id = -1;
        return;
      },//unloadFn
      fn: function(){
        gTimeout.debugFn('gTimeout.timers.pingApexSession.fn: Extending APEX Session');
        jQuery.jApex.ajax({
          appProcess: 'AP_NULL',
          success: function(){},
          async: true
        });
        gTimeout.timers.pingApexSession.loadFn();
        return;
      }//fn
    },//pingApexSessions
    //Kill current session. This is called when the user gets the option to extend their session
    killSession: {
      id: -1,
      time: 5000, // Time to kill the APEX session once launched. Should only be run when extend session popup box is loaded
      loadFn: function(){
        gTimeout.debugFn('gTimeout.timers.killSession.loadFn:');
        this.id = setTimeout('gTimeout.timers.killSession.killFn();', this.time);
        return;
      },
      unloadFn: function(){
        gTimeout.debugFn('gTimeout.timers.killSession.unloadFn: ');
        clearTimeout(this.id); 
        this.id = -1;
        gTimeout.modalRegions.extendSession.closeFn(); // Close extendSession Modal 
        return;
      },
      killFn: function(){
        gTimeout.debugFn('gTimeout.timers.killSession.killFn: Killing APEX Session');
        // Open Logout modal window
        gTimeout.modalRegions.sessionEnded.openFn();
        // Stop ping Apex session
        gTimeout.timers.pingApexSession.unloadFn(); 
        // Logout APEX session
        jQuery.jApex.ajax({
          appProcess: 'AP_LOGOUT',
          success: function(){},
          async: true
        });
        return;
      },
      // Prevents the session from being killed. We should be in a about to kill state now
      liveFn: function(){
        gTimeout.debugFn('gTimeout.timers.killSession.liveFn: ');
        //Check that we're about to be killed
        if (this.id == -1){
          alert('Session is not marked to be killed');
          return;
        }
        
        // Stop the kill timer
        this.unloadFn();
        return;
      }//liveFn
    },//killSession
    // Timer for user movement time
    idle:{
      time: 5000, // Time to load the "Extend Session" popup box
      loadFn: function(){
        gTimeout.debugFn('gTimeout.timers.idle.loadFn:');
        $.idleTimer(this.time);
        $(document).bind("idle.idleTimer", function(){gTimeout.timers.idle.idleFn();});
        // Trigger countdown timer
        return;
      },
      idleFn: function(){
        gTimeout.debugFn('gTimeout.timers.idle.idleFn:');
        // Load modal box to give user option to extend session
        gTimeout.modalRegions.extendSession.openFn();
        // Only load if we're not in a kill state
        if (gTimeout.timers.killSession.id == -1){
          gTimeout.timers.killSession.loadFn();
        }
        return;
      } //idle Fn     
    } // idle
  },//timers
  loadFn: function() {
    gTimeout.timers.pingApexSession.loadFn(); // Keep database sessions alive
    gTimeout.timers.idle.loadFn(); // Turn on user idle timer
    return;
  }//loadFn
  
};//gTimeout

$(document).ready(function(){
  // Set Parameters
  gTimeout.timers.pingApexSession.time = 5 * 1000; // Refresh APEX session every 5 seconds. This should be really close to your apex session timeout values
  gTimeout.timers.idle.time = 10 * 1000; // 10 seconds of inactivity will trigger this window
  gTimeout.timers.killSession.time = 10 * 1000; // Once the warning message pops up, user has 10 seconds to extend their session
  // Configure Modal windows (not required)
  gTimeout.modalRegions.extendSession.backgroundColor = '#CCC';
  
  gTimeout.loadFn();
});

Redirects for APEX Tabs instead of page submits

2009-09-17T09:00:00.000-06:00

With APEX Tabs you may not want the page to be submitted each time the user clicks on a tab. If you look at the link for the tabs they look like: javascript:doSubmit('xxxx'); Where "xxx" is the name of the tab. "doSubmit" will submit the page and could trigger page computations, validations, and processes. If all you want to do is use the tabs as a form of navigation (i.e. when you click on a tab it redirects to another page) then this could cause some problems.

To avoid triggering Page Processing on tabs you can modify all the conditions on your Page Process (this could be very long) or change the link. They're multiple ways to change the links, here's a simple one using jQuery.

Here's a link to the demo: http://apex.oracle.com/pls/otn/f?p=20195:2500. Note, in the demo I don't trigger the javascript to change the link automatically so you can see what they look like before and after changing the links.

- Create an Application Process
Note: You may want to only apply this to certain pages depending on the use case

Name: AP_UNSUBMIT_TABS
Process Point: On Load: After Header


BEGIN
  FOR x IN (SELECT tab_name,
                   tab_page,
                      '' tabinfo
              FROM apex_application_tabs t
             WHERE application_id = :app_id)
  LOOP
    HTP.p (x.tabinfo);
  END LOOP;
END;

- Create a HTML region
Note: You'll need to install the jQuery JS file in Shared Components / Static Files

Tooltip Help in APEX: An alternative to popup help

2009-09-15T09:00:00.002-06:00

The default help functionality for APEX is ok but can cause some problems with users browsers since it uses popup windows to display the help. If you do some digging around regarding web development best practices you'll find a lot of articles discussing why you should avoid popup windows. Instead of using the default help popup windows I prefer to use tooltips to display the help. Besides avoiding the popup window, tooltips generate a good user experience by displaying item help very quickly.

This demo uses a jQuery tooltip plugin: http://bassistance.de/jquery-plugins/jquery-plugin-tooltip/. Please visit the Bassistance website to find out how to configure the look and feel of the tool tips.

Here's a link to the demo: http://apex.oracle.com/pls/otn/f?p=20195:2400

- Create or Update Label Template
Note: You don't need to create a new template. If you want to, just update the existing templates
- Copy the "Optional Label with Help" and rename to "Optional Label with ToolTip".
Note: You can do this for required labels as well

- Change the "Before Label" From:
<label for="#CURRENT_ITEM_NAME#" tabindex="999"><a class="t20OptionalLabelwithHelp" href="javascript:popupFieldHelp('#CURRENT_ITEM_ID#','&SESSION.')" tabindex="999">

To:
I removed the href reference and replaced with #
<label for="#CURRENT_ITEM_NAME#" tabindex="999"><a class="t20OptionalLabelwithHelp" href="#" tabindex="999">

- Create a HTML region
This can be done on P0 to load for each page
Don't forget to upload the jQuery and tooltip JS files into Shared Components / Static Files

- Create an Application Process
Name: AP_GET_HELP_TEXT
Process Point: On Load: After Header


BEGIN
  FOR x IN (SELECT    ''
                   || item_help_text
                   || '' help_html
              FROM apex_application_page_items
             WHERE application_id = :app_id
               AND page_id = :app_page_id
               AND item_help_text IS NOT NULL)
  LOOP
    HTP.p (x.help_html);
  END LOOP;
END;

- Change Item Labels
Change Item labels to "Optional Label with ToolTip". Only do this if you created a new template

Updateable Interactive Report for APEX

2009-09-14T09:00:00.004-06:00

A colleague had a requirement where he had over 10,000 rows of data which had to be updateable. Using Interactive Reports (IR) was the preferred approach as it would allow users to filter the data to modify the rows they wanted to before submitting the page. Tabular forms wouldn't work since the page would be to large. This is the solution that I proposed to make "Updateable IRs".

The following solution will work with IRs and standard reports with pagination. If the users applies filters or paginates to another set of data, the changes they make will remain in the collection. This is using a similar technique that I wrote about for APEX Report with checkboxes (advanced). To summarize this process:

Store the current query data into a collection as well as the md5 checksum.
Build an IR against the collection and use APEX_ITEMs to display input fields.
When a user changes a field, we submit that change to the collection.
Once the user is done with their changes you'll need to process the collection as required. In the last step in this example I have a query that will help identify changed rows.

You can do a lot with this approach but if you don't have an urgent need I'd suggest holding off until APEX 4.0. They're some security issues that would need to be addressed before launching this code in a public application. I didn't include the security updates in this example since I did not want to lose scope of the base functionality. Updating the code to make it secure shouldn't be too difficult.

Here's the link to the demo: http://apex.oracle.com/pls/otn/f?p=20195:2300

- Create IR Report Region
Note: You can use this for regular reports with pagination as well


SELECT e.empno,
    apex_item.text (2,
                    ac.c002,
                    NULL,
                    NULL,
                       'class="updateableIR" seqid="'
                    || ac.seq_id
                    || '" attrNum="2"'
                   ) ename,
    apex_item.text (3,
                    ac.c003,
                    NULL,
                    NULL,
                       'class="updateableIR" seqid="'
                    || ac.seq_id
                    || '" attrNum="3"'
                   ) job,
    apex_item.select_list_from_query
       (4,
        ac.c004,
           'select E.ENAME d, E.EMPNO r from emp e where E.EMPNO != '
        || e.empno,
        'class="updateableIR" seqid="' || ac.seq_id || '" attrNum="4"',
        'YES',
        NULL,
        '- Manager -'
       ) mgr,
    apex_item.text (5,
                    ac.c005,
                    NULL,
                    NULL,
                       'class="updateableIR" seqid="'
                    || ac.seq_id
                    || '" attrNum="5"'
                   ) hiredate,
    apex_item.text (6,
                    ac.c006,
                    NULL,
                    NULL,
                       'class="updateableIR" seqid="'
                    || ac.seq_id
                    || '" attrNum="6"'
                   ) comm,
    apex_item.select_list_from_query
                       (7,
                        ac.c007,
                        'SELECT d.dname d, d.deptno r FROM dept d',
                           'class="updateableIR" seqid="'
                        || ac.seq_id
                        || '" attrNum="7"',
                        'YES',
                        NULL,
                        '- Department -'
                       ) deptno
FROM apex_collections ac, emp e
WHERE ac.collection_name = :p2300_collection_name AND ac.c001 = e.empno

- Create Region Items
- Hidden & Protected: P2300_COLLECTION_NAME

- Create Page Computation
Note: This is just to set the collection name. You can call it whatever you want

Item: P2300_COLLECTION_NAME
Computation Point: Before Header
Computation Type: Static Assignment
Computation: P2300_IR_COLLECTION

- Create a Page Process (PL/SQL)

Name: Load Collection
Process Point: On Lead - Before Header


-- This creates the collection if it isn't created yet.
DECLARE
v_collection_name             apex_collections.collection_name%TYPE
                                                 := :p2300_collection_name;
v_reset_flag                  VARCHAR2 (1) := 'N';
BEGIN
-- Create collection if it does not exist or reset collection required
IF    apex_collection.collection_exists
                                   (p_collection_name            => v_collection_name) =
                                                                     FALSE
  OR v_reset_flag = 'Y'
THEN
 apex_collection.create_collection_from_query
   (p_collection_name            => :p2300_collection_name,
    p_query                      => q'! SELECT empno,
    ename,
    job,
    mgr,
    TO_DATE (hiredate, 'DD-MON-YYYY'),
    comm,
    deptno
FROM emp !',
    p_generate_md5               => 'YES'
   );        -- Generated md5 is important to help identify changed columns
END IF;
END;

- Create HTML Region to store JS code:
Note: This uses jQuery and Tyler Muth's jApex plugin. You'll have to upload the .js files as static files in Shared Components.

- Create Application Process:
Name: AP_UPDATE_COLLECTION_CELL
Process Point: On Demand


-- AP_UPDATE_COLLECTION_CELL
BEGIN
apex_collection.update_member_attribute
                             (p_collection_name            => apex_application.g_x01,
                              p_seq                        => apex_application.g_x02,
                              p_attr_number                => apex_application.g_x03,
                              p_attr_value                 => apex_application.g_x04
                             );
END;

- Query to see which rows were changed


SELECT ac.collection_name, ac.seq_id, ac.c001, ac.c002, ac.c003, ac.c004,
    ac.c005, ac.c006, ac.c007, ac.md5_original,
    CASE
      WHEN apex_collection.get_member_md5
                                       (:p2300_collection_name,
                                        ac.seq_id) =
                                                ac.md5_original
        THEN 'NO'
      ELSE 'YES'
    END row_change
FROM apex_collections ac
WHERE collection_name = :p2300_collection_name

APEX Rules & Guidelines

2009-09-02T09:00:00.005-06:00

After several late nights of nerding I've finally submitted/completed my APEX application for the APEX Developer Competition 2009.

You can demo and download the application here: http://apex.oracle.com/pls/otn/f?p=46865

This purpose of this application is to allow development teams to create an online Rules & Guidelines development document. The template for this application is based on Patrick Cimolini's (Cayman Islands Government) presentation at ODTUG.

The concept of the R&G template is:

Rule: Follow 100% of the time
Guideline: Follow 90% of the time

Mandatory Headings:

Rule/Guideline: Active voice sentence.
Why:Important for Team Buy-In

Optional Headings:

Result: Illustration of desired outcome
How: How the result is achieved.
Notes: State the non-obvious
See Also: Point to related Rules and Guidelines

You can use this template for both APEX development and your PL/SQL standards. I've included the installation files in the demo application so feel free to install at your organization. If you are using it please post any comments/suggestions on this post and I'll try to write some updates.

APEX: $x_disableItem

2009-07-30T09:00:00.002-06:00

I had an issue where I disabled a radio button (after selecting a value) using the APEX JavaScript function ($x_disableItem) but my selected value wasn't being saved.

After some testing I noticed that if I selected a value, disabled the radio, then submitted the page item's value would be null (empty string).

You can view an example of this here: http://apex.oracle.com/pls/otn/f?p=20195:2200

They're several ways to fix this problem. The easiest would be to "undisable" (i.e. re-enable) the item before the page is submitted. I know "undisable" is not a word but to re-enable a page item you need to call the disable function: $x_disableItem('PX',false);

APEX: How to Pass Multiselect List Values in URL

2009-07-28T09:00:00.000-06:00

When passing multiselect list values, or any multi LOV, in the URL you may have some unexpected behaviors. Here's an example: http://apex.oracle.com/pls/otn/f?p=20195:2100

If you take a look at the example you'll notice that the URL doesn't contain all the values that you may have submitted. For example I selected KING (7839), BLAKE (7698), and CLARK (7782). I would expect the URL to contain these values when I pass them via the URL. Instead the URL looks like this:

http://apex.oracle.com/pls/otn/f?p=20195:2100:1674288126968745::NO::P2100_EMPNO_LIST:7839:7698

Notice how only 2 values are passed in? That's because the delimiter used in LOVs is the same that is used in the URL. What can be even more confusing is that I selected 3 values but when I pass them in the URL only 1 is "accepted". This is because the last value in the URL is the "PrinterFriendly" parameter (please see: http://download.oracle.com/docs/cd/E14373_01/appdev.32/e11838/concept.htm#BEIJCIAG)

To fix the issue for all your mutli LOVs you can use a similar technique that I used to resolve the %null% issue. An example of the fix can be found here: http://apex.oracle.com/pls/otn/f?p=20195:2110. If you take a look at the example and select several employees the URL now looks like this:

http://apex.oracle.com/pls/otn/f?p=20195:2110:1674288126968745::NO::P2110_EMPNO_LIST:7839*7698*7782

Notice how the delimiters are *s for the empnos?

1- Create Application Process to replace colon delimiter with *
Note: You aren't limited to using * as your delimiter

Name: AP_REMOVE_URL_DELIM_FROM_ITEMS
Sequence: -10 (helps ensure that it is run before any other process
Point: On Submit: After Page Submission - Before Computations and Validations


BEGIN
  FOR x IN (SELECT item_name,
                   REPLACE (v (item_name), ':', '*') new_item_value
              FROM (SELECT item_name
                      FROM apex_application_page_items aapi
                     WHERE aapi.application_id = :app_id
                       AND aapi.page_id = :app_page_id
                       AND LOWER (display_as) IN ('checkbox', 'shuttle', 'select list', 'multiselect list') -- Limiting to these types. Can remove if you want to handle all types
                       AND ROWNUM > 0) x
             WHERE INSTR (v (x.item_name), ':') > 0) LOOP
    apex_util.set_session_state (x.item_name, x.new_item_value);
  END LOOP;
END;

Note: This will replace the colon delimiter with a *. This may change some of your validations, page processes etc.

2- Create Application Process to replace * with colon delimiter on page load

Name: AP_RESET_URL_DELIM_FROM_ITEMS
Sequence: -10 (helps ensure that it is run before any other process)
Point: On Load: Before Header (page template header)


BEGIN
  FOR x IN
    (SELECT item_name,
            REPLACE (v (item_name), '*', ':') new_item_value
       FROM (SELECT item_name
               FROM apex_application_page_items aapi
              WHERE aapi.application_id = :app_id
                AND aapi.page_id = :app_page_id
                AND LOWER (display_as) IN
                      ('checkbox', 'shuttle', 'select list', 'multiselect list')   -- Limiting to these types. Can remove if you want
                AND ROWNUM > 0) x
      WHERE INSTR (v (x.item_name), '*') > 0) LOOP
    apex_util.set_session_state (x.item_name, x.new_item_value);
  END LOOP;
END;

Oracle: How to update all sequences

2009-07-24T09:00:00.001-06:00

If you ever do data refreshes from production to development or test environments you may run into an issue where your sequences are not up to date. It seems that Oracle exports the sequences first, then the data. If your sequence numbers change during the entire export process you may get errors when using them in your refreshed schema.

To fix this problem you can try to find where your sequences are used and get the MAX(value) to find the next value. Alternatively you can just add a large random number, say 1,000,000, to all your sequences. For most users this will fix the problem and is very easy to do. Here's how:


-- Update all sequences
DECLARE
  v_increase_by                     NUMBER;
  v_bkp_increment_by            NUMBER;
  v_str                         VARCHAR2 (1000);
  v_count                       NUMBER;
BEGIN
  v_increase_by                  := 1000000;

  FOR rec IN (SELECT *
              FROM   user_sequences) LOOP
    -- Backup current incrementation number
    v_bkp_increment_by         := rec.increment_by;
    -- Alter the sequence to increase by a defined amount
    v_str                      := 'alter sequence ' || rec.sequence_name || ' increment by ' || v_increase_by;

    EXECUTE IMMEDIATE v_str;

    -- Increase by that amount
    v_str                      := 'select ' || rec.sequence_name || '.nextval from dual';

    EXECUTE IMMEDIATE v_str
    INTO              v_count;

    -- Reset the increment factor
    v_str                      := 'alter sequence ' || rec.sequence_name || ' increment by ' || v_bkp_increment_by;

    EXECUTE IMMEDIATE v_str;
  END LOOP;
END;
/

Oracle: Advanced Error Messages

2009-07-22T09:00:00.004-06:00

This is not an APEX specific post, however it can be useful for error handling.

A colleague showed me a great way to get more useful debug information. Normally I used SQLERRM and SQLCODE in an exception to display or store error messages. Using DBMS_UTILITY you can get more detailed Oracle error messages. Here's an example:


-- I put this in a package for demo purposes
CREATE OR REPLACE PACKAGE pkg_err_test
AS
  PROCEDURE sp_err_test (
    p_empno IN emp.empno%TYPE
  );
END pkg_err_test;

CREATE OR REPLACE PACKAGE BODY pkg_err_test
AS
  PROCEDURE sp_err_test (
    p_empno IN emp.empno%TYPE
  )
  AS
    v_ename emp.ename%TYPE;
  BEGIN
    SELECT ename
      INTO v_ename
      FROM emp
     WHERE empno = p_empno;

    DBMS_OUTPUT.put_line ('Employee name is: ' || v_ename);
  EXCEPTION
    WHEN OTHERS THEN
      -- Basic Error Message
      DBMS_OUTPUT.put_line ('Old Error Message: ' || SUBSTR (SQLERRM, 1, 255));
      DBMS_OUTPUT.put_line ('Old Err Code: ' || SQLCODE);
      -- Advanced Error Messages
      DBMS_OUTPUT.put_line ('-- New Error Messages --');
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_stack);   -- Error Message
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace);   -- Where it occurred
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_call_stack);   -- Call Stack
  END sp_err_test;
END pkg_err_test;

-- Run the error test with an invalid employee number so an exception will be raised
EXEC pkg_err_test.sp_err_test(p_empno => 123);

DBMS Output:

Old Error Message: ORA-01403: no data found
Old Err Code: 100
-- New Error Messages --
ORA-01403: no data found

ORA-06512: at "GIFFY.PKG_ERR_TEST", line 9

----- PL/SQL Call Stack -----
object line object
handle number name
362D7814 24 package body GIFFY.PKG_ERR_TEST
362D70E0 1 anonymous block

The error message is displayed as well as where the error occurred and the call stack. In large systems this can be very helpful. You should be aware that when called from a package, it does not list the procedure or function (as seen in this example) where the error occurred so you may need to hard code the function or procedure name in your error message.

How to resolve %null% issue in APEX LOVs

2009-07-21T09:00:00.004-06:00

Patrick Wolf mentioned this at ODTUG Kaleidoscope this year.

After you implement your first LOV in an APEX application you'll quickly learn about the %null% problem. APEX substitutes an empty string for Null return value as %null%.

They're several workarounds, like using "-1" as the NULL value. Or modifying your query using "'%' || 'null%'". For example:


SELECT ename,
       empno
  FROM emp
 WHERE empno = DECODE (:p_empno, '%' || 'null%', empno, NULL, empno, :p_empno)

Instead of using workarounds you can convert %null% to NULL (empty string) by creating the following application process:

Application Process: AP_SET_LOV_NULLS
Process Point: On Submit - Before Computations and Validations


BEGIN
  FOR x IN (SELECT *
              FROM (SELECT item_name
                      FROM apex_application_page_items aapi
                     WHERE aapi.application_id = :app_id
                       AND aapi.page_id = :app_page_id
                       AND LOWER (aapi.lov_display_null) = 'yes'
                       AND aapi.lov_definition IS NOT NULL
                       AND aapi.lov_null_value IS NULL
                       AND ROWNUM > 0) x
             WHERE LOWER (v (x.item_name)) = '%' || 'null%') LOOP
    apex_util.set_session_state (x.item_name, NULL);
  END LOOP;
END;

JavaScript: ? = If Else

2009-07-20T09:00:00.000-06:00

This is not directly related to APEX however it will help if you use a lot of JavaScript

When I first started to develop web based applications and used JavaScript I came across some JS code with a ? in it. I didn't know what it was for and Googling "JavaScript ?" didn't help either. Here's a quick summary on how it works

Boolean ? true action : false action

So this:


var x = 2;
if (x > 1)
  window.alert('True');
else
  window.alert('False');

Becomes this:


var x = 2;
x>1 ? window.alert('True') : window.alert('False') ;

APEX_APPLICATION.DO_SUBSTITUTIONS

2009-07-16T09:00:00.000-06:00

I've run into several instances where I needed to store HTML in a table. The problem is sometimes the HTML references APEX Items. For example if your HTML needs to reference a picture, odds are you'll need to reference &APP_IMAGES. (or some image location item). In the past I've done manual REPLACE calls for known items, but it was fairly restrictive.

APEX has a great function (not yet documented to my knowledge) called APEX_APPLICATION.DO_SUBSTITUTIONS. If you pass in a string, it will substitute any APEX values. Here's an example app: http://apex.oracle.com/pls/otn/f?p=20195:2000

To create demo:

1- Create table and insert values


CREATE TABLE tp2000(line_content CLOB NOT NULL);

INSERT INTO tp2000
     VALUES ('Google Canada Picture: ');

INSERT INTO tp2000
     VALUES ('My Current Session: ' || CHR (38) || 'APP_SESSION.');

2- Create Report Region (with substitutions)


SELECT apex_application.do_substitutions (line_content) content_with_subs
  FROM tp2000

3- Create Report Region (without substitutions)


SELECT line_content content_without_subs
  FROM tp2000

APEX: How to Develop in 2 Browser Tabs

2009-07-15T09:00:00.001-06:00

I saw this during a presentation by Anton Nielsen last year. If you've developed with APEX for a while then you've probably wanted to have 2 developer tabs open on your browser at the same time. You'll quickly find out that this doesn't work to well.

There's an easy way around it. Lets say you're developing on an instance running on your laptop. The URL you normally go to looks something like: http://localhost:8080/apex/

Go into your hosts file (C:\WINDOWS\system32\drivers\etc\hosts for Windows users). You should see an entry like this:

127.0.0.1 localhost

Add: 127.0.0.1 giffy01 on a new line (where "giffy01" is any arbitrary name).

Your hosts file should now look like:

127.0.0.1 localhost
127.0.0.1 giffy01

In your favorite web browser,

open the following URLs in 2 different tabs:

http://localhost:8080/apex/
http://giffy01:8080/apex/

You can now have 2 development tabs open at the same time.

APEX: How to Dynamically Render Regions

2009-07-14T07:30:00.000-06:00

Suppose you needed to enable and disable report regions on each page based on a parameter. You could add a condition to each region. If the conditions were all the same, the smart thing to do would be to create a function and have your condition reference the function.

What if your application had 100 pages? Would you remember to apply the condition to each report region in the 100 pages?

This is not a problem that most developers run into, however when you are building large applications something similar may come up. If you can find a way to dynamically control items, regions, processes, etc this can save on development time.

At the ODTUG Kaleidoscope conference Dennis Vanill gave a presentation on how to use Page 0 items to enable and disable APEX objects dynamically. Using this logic, here's an example on how to dynamically disable a region.

Note: Use this when appropriate. For basic conditions stick with using "regular" conditions

A demo is available here: http://apex.oracle.com/pls/otn/f?p=20195:1900

1- Create a page with some report regions


-- Interactive Report:
SELECT *
FROM emp

-- Regular Report
SELECT ename, sal
FROM emp

2- Create Page Process: On Load - Before Header


DECLARE
BEGIN
  IF NVL (:p1900_hide_reports_flag, 'N') = 'Y' THEN
    FOR x IN (SELECT region_id
                FROM apex_application_page_regions
               WHERE application_id = :app_id
                 AND page_id = :app_page_id
                 AND source_type IN ('Report', 'Interactive Report')) LOOP
      FOR i IN 1 .. apex_application.g_plug_id.COUNT LOOP
        IF apex_application.g_plug_id (i) = x.region_id THEN
          apex_application.g_plug_display_condition_type (i) := 'NEVER';
        END IF;
      END LOOP;
    END LOOP;
  END IF;
END;

2- (For Demo purposes only)
I added the following on Page 0 to display in the example application. This shows that no conditions were applied to a region


SELECT region_id,
       region_name,
       source_type,
       condition_type,
       condition_expression1,
       condition_expression2,
       build_option,
       authorization_scheme
  FROM apex_application_page_regions
 WHERE application_id = :app_id
   AND page_id = :app_page_id

You can use the same logic to control computations, items, etc. Take a look at apex_application (desc apex_application) for more options.

APEX: Saving item values for each user

2009-07-02T09:00:00.004-06:00

Someone asked me today if APEX could remember input values for specific page items. For example if you have a page with report parameters could APEX remember the report parameters that the user last used the next time they logged in?

Note: Please read comments below as APEX does support this out of the box on an individual item basis. This solution is to make the option configurable for large applications.

APEX doesn't support this out of the box, however it does have some great features which can enable you to do this. You can use cookies for this but I wanted to make the solution work no matter where the user was accessing the application from.

To make things a bit more difficult, I don't want to remember all item values on a page so I must be able to control which items are "remembered" and which items aren't. I can do this by using a naming convention in my items, however I don't want to rename all my page items (I already have a lot of them). Instead I decided to create a table which will list all the items a user can remember.

You can try the demo here (follow the instructions on the page).


CREATE TABLE tapex_remember_page_item(
application_id NUMBER NOT NULL,
page_id NUMBER NOT NULL, 
item_name VARCHAR2(255) NOT NULL);

-- You don't need to add a UK, however it may be a good idea. 
ALTER TABLE tapex_remember_page_item ADD(
  CONSTRAINT tapex_remember_page_item_uk1
  UNIQUE (application_id, page_id, item_name));
  
-- Since I name all my APEX items in uppercase, just do this as an extra precaution
CREATE OR REPLACE TRIGGER trg_tapex_remember_pg_itm_buir
  BEFORE UPDATE OR INSERT
  ON tapex_remember_page_item
  FOR EACH ROW
BEGIN
  :NEW.item_name := UPPER (:NEW.item_name);
END;
/

INSERT INTO tapex_remember_page_item
            (application_id, page_id, item_name)
     VALUES (20195, 1800, 'P1800_DEPTNO');
            
            
INSERT INTO tapex_remember_page_item
            (application_id, page_id, item_name)
     VALUES (20195, 1800, 'P1800_MIN_SAL');

For this example we'll store the values as APEX Preferences, however you could easily create your own preferences table to manage your data. I think they're several advantages to managing the preferences in your own table, however if you have a small application with a limited number of users then I'd recommend using the APEX_UTIL preference options

Create 2 Application Processes:

AP_GET_PAGE_ITEM_PREFS
On Load: Before Header (page template header)


DECLARE
BEGIN
  FOR x IN (SELECT item_name
              FROM tapex_remember_page_item
             WHERE :app_page_id = page_id
               AND :app_id = application_id) LOOP
    apex_util.set_session_state (p_name      => x.item_name,
                                 p_value     => apex_util.get_preference (p_preference     => x.item_name,
                                                                          p_user           => :app_user
                                                                         )
                                );
  END LOOP;
END;

AP_SET_PAGE_ITEM_PREFS
On Submit: After Page Submission - After Computations and Validations


DECLARE
BEGIN
  FOR x IN (SELECT item_name
              FROM tapex_remember_page_item
             WHERE :app_page_id = page_id
               AND :app_id = application_id) LOOP
    apex_util.set_preference (p_preference => x.item_name, p_value => v (x.item_name), p_user => :app_user);
  END LOOP;
END;

For those of you that are curious APEX Preferences are stored in : apex_030200.wwv_flow_preferences$ where apex_030200 is the schema name for APEX (could also be called flows_xxxxxx)

ODTUG Kaleidoscope 2009 Summary

2009-06-28T08:29:00.006-06:00

I just got back from ODTUG Kaleidoscope 2009. I met a lot of great people and saw many excellent presentations. My biggest regret was not being able to see everyone's presentations! Here are some highlights from the conference:

Sunday APEX Symposium: Saw some excellent presentations on how APEX was used in other organizations.

- Joel Kallman (Oracle) had a great opening presentation going over the history and future of APEX

- Olivier Dupont (iAdvise): Excellent use of APEX at the airport, even without a mouse or browser access!

- My presentation: Mike Kinahan did a great job filling in for Frank Hoogendoorn on short notice, however my camera man wasn't as good (see picture below). If anyone has some pictures of the presentation can you please let me know?

- Dennis Vanill (PAETEC Software): Using page 0 to cut down on common task for many pages.

- Jan Navratil (CampusIT): Demonstrated that APEX can be used in mission critical applications. Use translation as a configuration technique for each client's definitions.

- Paul Davidson (Cornerstone Brands): APEX used in large scale call center and how he cut down call time.

- Shravan Kumar (Apexor): created the term LAOS (Linux, Apache, Oracle, SQL Developer). Cut down long processes into 4 minutes using APEX!

- David Peake (Oracle): Anounced an APEX development contest for some free tickets to Open World.

Presentations:

- Tom Kyte(Oracle) as always had some great presentation and demo. He stressed (again) use the database for as much as you can since Oracle will do things quicker and faster for you.

- Michael Hichwa (Oracle): APEX 4.0 Demo. I'll write a separate post about this soon since it will take a lot of space (it's just that good)
Edit: 1-Jul-2009. Role Hartman wrote an excellent post for APEX 4.0. Please read it for more info: http://roelhartman.blogspot.com/2009/07/buzz-around-apex-40.html

- Dietmar Aust (Opal Consulting): Free PDF and XLS printing options for APEX. Check his blog for full details

- Scott Spendolini (Sumner Technologies): Managing Multiple APEX applications as one. The full sample application is on his web site here: http://sumnertechnologies.com/framework

- Patrick Wolf (Oracle): Had 2 great presentations. I missed his first one, but was able to attend his second presentation. Besides the content he's an excellent speaker so if you ever have a chance to see him present you should go! Patrick was kind enough to show Dennis and I some more APEX 4.0 features and answer a lot of my questions.

- Francis Mignault (Insum): Multi tenant SaaS APEX applications. I develop SaaS applications as well and it was really nice to see a different development approach.

- Patrick Cimolini (Cayman Island Government): Development Rules and Guidelines Document for APEX. If you're looking for a simple yet effective way to create a APEX standards document I strongly suggest you get a copy of his presentation (I'm going to see if he'll allow me to post a copy here).

- John Scott (Apex Evangelists): Unfortunately I wasn't able to see any of his presentations but I heard they were really good, I'll have to get a copy of his slides and white papers later on.

ODTUG Kaleidoscope - Twitter

2009-06-16T09:30:00.000-06:00

I've broken down and created a Twitter account http://twitter.com/martindsouza. I wasn't planning on joining Twitter however it seems like it will be an excellent communication tool for the upcoming ODTUG Kaleidoscope conference.

On that note, our presentation's "Twitter Tag" (I'm new to Twitter so I don't know all the terminology) is: #ODTUG S299. Feel free to post something on Twitter or this blog during the conference.

How to Quickly Append VARCHAR2 to CLOB

2009-06-15T09:00:00.000-06:00

This is not an APEX specific issue, however it could be useful for some of your PL/SQL code

I ran into an issue today where I had to append VARCHAR2s to a CLOB many times in a loop. I first tried appending a VARCHAR2 to a CLOB: CLOB := CLOB || VARCHAR2. I noticed that this was taking a long time to run. In order to speed up the process I tried the following techniques:
- Create a "temp" CLOB (TMP_CLOB := VARCHAR2) and then appended it the clob CLOB := CLOB || CLOB
- Use the CLOB := CLOB || TO_CLOB(VARCHAR2)
- Use DBMS_LOB.append (CLOB, VARCHAR2)

All three options resulted in significant speed increases, however using the "temp" CLOB method resulted in the quickest code. Here is the test that I ran along with the results:


DECLARE
  v_start TIMESTAMP;
  v_end TIMESTAMP;
  v_clob CLOB;
  v_tmp_clob CLOB;
  v_iterations PLS_INTEGER := 100000; -- Used 1,000, 10,000, and 100,000 for testing
BEGIN
  v_start := SYSTIMESTAMP;
  v_clob := NULL;

  FOR i IN 1 .. v_iterations LOOP
    v_clob := v_clob || TO_CHAR (SYSTIMESTAMP) || ', ';
  END LOOP;

  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('CLOB := CLOB || VARCHAR2 method: ' || TO_CHAR (v_end - v_start));
  v_start := SYSTIMESTAMP;
  v_clob := NULL;

  FOR i IN 1 .. v_iterations LOOP
    v_clob := v_clob || TO_CLOB (TO_CHAR (SYSTIMESTAMP) || ', ');
  END LOOP;

  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('CLOB := CLOB || TO_CLOB(VARCHAR2) method: ' || TO_CHAR (v_end - v_start));
  v_start := SYSTIMESTAMP;
  v_clob := NULL;

  FOR i IN 1 .. v_iterations LOOP
    v_tmp_clob := TO_CHAR (SYSTIMESTAMP) || ', ';
    v_clob := v_clob || v_tmp_clob;
  END LOOP;

  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('CLOB := CLOB || TMP_CLOB method: ' || TO_CHAR (v_end - v_start));
  v_start := SYSTIMESTAMP;
  v_clob := NULL;
  v_clob := 'h'; -- need to initialize it;

  FOR i IN 1 .. v_iterations LOOP
    DBMS_LOB.append (v_clob, TO_CHAR (SYSTIMESTAMP) || ', ');
  END LOOP;

  v_end := SYSTIMESTAMP;
  DBMS_OUTPUT.put_line ('DBMS_LOB.append method: ' || TO_CHAR (v_end - v_start));
END;

The results were as follows:

1,000 Iterations
CLOB := CLOB || VARCHAR2 method: +000000000 00:00:00.578000000
CLOB := CLOB || TO_CLOB(VARCHAR2) method: +000000000 00:00:00.063000000
CLOB := CLOB || TMP_CLOB method: +000000000 00:00:00.047000000
DBMS_LOB.append method: +000000000 00:00:00.172000000

10,000 Iterations
CLOB := CLOB || VARCHAR2 method: +000000000 00:00:10.656000000
CLOB := CLOB || TO_CLOB(VARCHAR2) method: +000000000 00:00:00.688000000
CLOB := CLOB || TMP_CLOB method: +000000000 00:00:00.672000000
DBMS_LOB.append method: +000000000 00:00:00.687000000

100,000 Iterations
CLOB := CLOB || VARCHAR2 method: +000000000 00:42:17.453000000
CLOB := CLOB || TO_CLOB(VARCHAR2) method: +000000000 00:00:17.953000000
CLOB := CLOB || TMP_CLOB method: +000000000 00:00:08.140000000
DBMS_LOB.append method: +000000000 00:00:11.110000000

APEX Static Files Not For Secure Content

2009-06-12T09:00:00.003-06:00

Static files and Images (under Shared Components) are a great way to use external files (such as images, css, js, documents, etc). However, they should not be used to store sensitive information as users don't need to be logged in to access these files.

Here's an example: In an APEX application I've uploaded a documented called "top_secret.doc" in the Static Files section. I only want logged in users to be able to download this file. After the user logs in their is a HTML region which contains a link to top_secret.doc. The region source is:


Secure document.

When the user logs in they now see a link on the first page called "Secure document" which references top_secret.doc.

At first glance this seems secure since the user must first login before downloading the document. The hyperlink looks something like this: http://localhost:8080/apex/wwv_flow_file_mgr.get_file?p_security_group_id=1037606673759910&p_flow_id=103&p_fname=top_secret.doc

If you notice there's no reference to the user's APEX session ID. Anybody can use this URL to download the file even if they don't have access to your application.

This is not a bug or an APEX security hole, but something that you should be aware of if you are thinking about storing sensitive information in the static files area.

Quickly Modify APEX Interactive Report Options

2009-06-11T09:00:00.000-06:00

If your APEX application has many Interactive Reports (IR) it can be tedious to configure IR features for each report, and their columns, such as filtering, highlighting etc.

Since APEX resides within the database there's a quick way to manage all of your IRs. Please note this is not supported by Oracle so please be aware of this.

First login to the database as SYS or SYSTEM.

Update Interactive Report options: You can modify more options by looking at the table definition for wwv_flow_worksheets


UPDATE apex_030200.wwv_flow_worksheets -- Where apex_030200 is your current APEX instance
   SET allow_report_saving = 'Y', -- Configure options as required
       show_finder_drop_down = 'N',
       show_display_row_count = 'Y',
       show_search_bar = 'N',
       show_search_textbox = 'Y',
       show_actions_menu = 'Y',
       show_select_columns = 'N',
       show_sort = 'N',
       show_filter = 'Y',
       show_control_break = 'Y',
       show_highlight = 'Y',
       show_computation = 'N',
       show_aggregate = 'N',
       show_chart = 'Y',
       show_flashback = 'N',
       show_reset = 'Y',
       show_download = 'Y',
       show_help = 'N'
 WHERE flow_id = :app_id
   AND page_id = :app_page_id -- Remove this predicate to push changes for all IRs

Update Interactive Report Columns:


UPDATE apex_030200.wwv_flow_worksheet_columns
   SET allow_sorting = 'Y',
       allow_filtering = 'N',
       allow_ctrl_breaks = 'Y',
       allow_aggregations = 'N',
       allow_computations = 'Y',
       allow_charting = 'Y'
 WHERE flow_id = :app_id
   AND page_id = :app_page_id;

Extend APEX Workspace Password

2009-06-09T09:00:00.001-06:00

Every so often when you log into the APEX development interface, it will require you to change your account password. This isn't a bad idea, however if you develop in several workspaces, and have multiple accounts, it may be a bit cumbersome. You can extend the account password lifetime by doing the following:

- Login to the APEX Admin
- Home / Manage Service / Security
- Change the Account Password Lifetime (days) to whatever you need.

Displaying Percentage Bar in APEX Reports

2009-06-01T09:00:00.000-06:00

APEX can create "Percentage Bars" within a report. They're probably a lot of 3rd party tools you can use for fancy percentage bars, however if you want a basic display to the user here's a quick way to do it. Click here for a demo.

1- Create your report
In this report we're using the employees percentage of salary within their department


SELECT e.ename,
       e.job,
       d.dname,
       e.sal,
       ROUND (e.sal / SUM (e.sal) OVER (PARTITION BY e.deptno) * 100, 0) pct_dep_sal, 
       ROUND (e.sal / SUM (e.sal) OVER (PARTITION BY e.deptno) * 100, 0) bar
  FROM emp e,
       dept d
 WHERE e.deptno = d.deptno

2- Add Percentage Bar for the "bar" column
- In the Reports Attributes section, click on the "Bar" column attributes
- Under Number/Date formatting enter the following: PCT_GRAPH:330099:CC0000:100

PCT_GRAPH:<Hex background color>:<Hex foreground color>:<Bar width in pixels>