APEX Process Condition with Multiple Buttons

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

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

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.

Thanks to Jason Long for posting the following comment about the differences between apex_application.g_unrecoverable_error and stop_apex_engine. Be sure to read it carefully because in the right conditions switching between the two calls can have significant impacts.

There appear to be a couple gotchas with simply replacing g_unrecoverable_error with stop_apex_engine.

1. If there is any PL/SQL code immediately following the g_unrecoverable_error it will still be executed. It looks like stop_apex_engine actually exits out of the routine.

2. It looks like stop_apex_engine will roll back any SQL inserts/updates/deletes that occur in the routine, where as g_unrecoverable_error allow them to be committed.

ClariFit From/To Date Picker Plug-in

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

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

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

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

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

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

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

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

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

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

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

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

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

After creating a plugin 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.

Malicious Code in APEX Plugins - Feedback

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

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

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

VirtualBox - High CPU Problem

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

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)

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

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

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

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 five ways to reference variables in APEX:

• :Bind_variables


• &Substitution_strings.


• V / NV functions


• #Hash#


• "Shortcuts"

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.

Shortcuts
Shortcuts can only be used in specific areas within APEX (such as some regions, labels, and templates) and can be rendered both statically or dynamically (using PL/SQL functions). To reference a Shortcut use the "shortcutname" notation (quotes included). The following article covers Shortcuts in more detail, including where you can use them in APEX: http://www.talkapex.com/2014/02/apex-shortcuts.html

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

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.