Dan McGhan's Oracle Blog

ifttt Worked!

2012-02-10T10:57:00.001-05:00

When I first heard about ifttt I thought it sounded really cool. For those of you that don't know, ifttt is an abbreviation for "If This Then That". The site allows you to create tasks that have triggers and actions which work across various channels like twitter and email. Check out their wtf page to learn more.

As cool as it sounded I couldn't think of a reason to use it at first. But after looking over the channels and options for each, I thought of a problem that ifttt could help me solve. People occasionally post questions about SkillBuilders plug-ins in the APEX forum. The problem is that the APEX forum doesn't have a notification system that allows one to search for strings so I may miss those posts.

I noticed that one of the channels in ifttt is for RSS feeds and the forums do provide that ability. Another channel is for email. So I set up a task that monitors the forum looking for "skillbuilders" in the in post and sends me an email when it finds a new one. I did this around a month ago and yesterday I got my first notification - awesome! I'm looking forward to figuring out new ways to use this great technology and I hope you can too.

SkillBuilders Save Before Exit 3.0

2012-01-14T15:30:00.000-05:00

I've just finished the latest update to the SkillBuilders Save Before Exit plug-in. Here's a summary of the changes in version 3.0:

Added setting to highlight items that were modified (thanks to Alex for the idea)
Implemented “fix” for IE issue that caused users to be prompted two times rather than just once (this was removed at some point but has been added back in)
Added modifiedItems method which returns a jQuery object of the APEX items that were modified
Renamed Ignore Change Selector setting to Ignore Modifications Selector. Also renamed changeDetected method to modificationDetected (similar naming convention now used throughout plug-in)

The new highlighting feature is awesome! Check out a demo here.

Be sure to visit our plug-in page to learn more about the plug-in and to download a copy. Enjoy!

What’s in the Forecast for the Oracle Cloud?

2012-01-12T20:43:00.001-05:00

One of the big announcements at Oracle Open World 2011 was the release of the Oracle Cloud. Today we seemed to get a little closer to that release with the announcement of the Preview Availability Phase. I've been added to a queue waiting for an account which is just torturous. It's like seeing the presents under the tree but knowing you can't open them till Christmas. I’m obviously really excited about the Oracle Cloud. I’m curious to see how Oracle’s offering will affect the cloud landscape and compete with the likes of Amazon and Salesforce in that space!

Larry’s introduction of the Oracle Cloud seemed rather all encompassing, offering both application and platform services. On the application services side, you’ll find services like Fusion CRM, Fusion HCM, and Oracle Social Network. On the platform services side you will find offerings like Java and Database services.

For those of you that don’t know me very well, I’m an Oracle database developer and Application Express (APEX) aficionado. Thus, my interests in the Oracle Cloud are focused more on the platform/database services. Since the announcement of these services I've had list of questions and concerns that has continued to grow.

Ultimately, even if I had the answers to these questions now, things could change rapidly making those answers obsolete in an instant. In fact, that probably will happen and I welcome it! Oracle has an amazing team of people working for it and it will be interesting to see how they tackle various issues with innovative solutions.

Even still, if only for reflection later, I'd like to share some of the questions and concerns I'm still pondering. Let's hope we have the answers soon!

Database vs. Schema

From what I can tell thus far, the Database Cloud Service will operate a little like apex.oracle.com currently does where everyone gets their own schema. While a single schema can be very powerful, I'm curious as to whether a full database offering will be made available, and if so, when? Also, will multiple Database Cloud Service instances be able to see each other?

Dev/Test/Prod

Most developers I know like to test deployments against a test system prior to going to production. Will each Database Cloud Service instance include a development and/or test instance which can be used for deployment purposes?

Database Packages

What if I want to schedule a job with DBMS_SCHEDULE or encrypt some data with DBMS_CRYPTO? These, and many other important packages, are locked down by default - arguably for good reason. But I want them! Will use of these packages be allowed?

Upgrade Strategies

If the Database Cloud Service is offered as a multi-tenant, schema based solution, how will database and APEX upgrades be handled? Of course we all know that upgrades always run the risk of breaking applications so I’m interested to know how potential risks will be mitigated.

SSL

I believe that most people should be running their applications and websites on SSL. Will applications using SSL be supported? I have to believe that eventually SSL will be supported, but it's an important question. I hope the answer is yes, sooner rather than later.

Email

On apex.oracle.com we are able to send emails via SMTP. However, that's not a production system. Will the production system include SMTP or a similar solution like Amazon's Simple Email Service? If so what kind of limits will be imposed?

OS/FTP/File System Access

Although the Shared Components in APEX provides us with a convenient means of using external files in our applications, there are many reason one might prefer a standard file system with FTP access. Will this be a supported option?

Pricing

What will the pricing model look like? Of course I saved this one for last. This is a huge, make it or break it type question. I really hope that Oracle enters the cloud space with a pricing model that's geared more toward gaining market share than profits - like Google does with many of its cloud services.

So what's in the forecast for the Oracle Cloud? I can't wait to find out!

New Webinar: Introducing SkillBuilders Super LOV 2.0

2012-01-05T10:30:00.001-05:00

It's almost time to release SkillBuilders Super LOV 2.0! The release will be next Thursday and we'll be holding a webinar to be sure everyone knows how to best leverage the plug-in.

Abstract

Since its introduction just over one year ago, the SkillBuilders Super LOV (list-of-values) plug-in has been our most popular plug-in by far. In short, Super LOV displays multi-column LOVs. Due to its popularity we are more committed than ever to making this plug-in better.

To celebrate Super LOV's first birthday (a little belated), we are releasing version 2.0 which brings one of the most requested features since day one: enterable!

Of course enterable is only one of the many features offered by Super LOV at this point. In fact, Super LOV is so action-packed with features we thought it would be fun to invite you to a webinar to show you how to use them all. So if you are in need of a multi-column LOV solution, or you'd simply like to learn more about installing and leveraging plug-ins in APEX, do not miss this session.

Click here to register for the webinar.

As usual, it’s free to attend – but it’s real training. I'll show you how the basics of what the plug-in offers and how that differs from what's available out of the box with APEX. Then take you through the steps of installation and configuration so that you can add Super LOV to your applications immediately.

The webinar will run on Thursday, January 12th, at 1pm EST. To see other time zones, click the registration link and then on the “Show in my Time Zone” link at the top of the page.

The Importance of Updating Themes

2011-12-21T18:27:00.001-05:00

Upgrading APEX is great because you get lots of new features to take advantage of in your applications. During the upgrade the themes in the theme repository get various updates as well. However, the themes and templates for existing applications are not updated during an APEX upgrade.

This means that only new applications, as well as existing applications that are manually switched to one of the new themes, are able to take advantage of any updates – including bug fixes! This is both good and bad at the same time.

Many organizations spend a lot of time tweaking a certain theme until it meets their standards or expectations. If that work was lost during upgrades, well, let’s just say that a lot of 4 letter words would be heard throughout the office.

But at the same time there’s a bit of an issue with keeping older themes around. The web is evolving quickly and APEX team at Oracle is working hard to keep up with the changes so that you can create the best applications possible for your organization.

Table based layouts have been swapped out for lighter div based layouts. DTDs have been added to prevent browsers from running in quirks mode. IDs have been added to buttons to better work with the Dynamic Action framework. We’re even seeing the introduction of HTML5 elements now!

So while it may take some time and effort to convert to a new theme, I highly recommend you do so. If you’ve never done this before, and/or you’re considering switching from a theme that was modified by someone else, proceed with caution. The previous theme may have customizations that need to be brought over, such as the inclusion of custom CSS and JavaScript. I recommend making a copy of the application prior to switching the theme so the applications can be more easily compared side by side.

Happy theme switching!

Kscope 2012–We Want You!

2011-10-15T13:56:00.001-04:00

I’m pleased to announce that I’ve accepted the invitation from ODTUG to lead the APEX track team for Kscope 2012. Other members of the team include some of the most talented APEX developers out there:

With a team like that I’m sure Kscope 2012 is going to be awesome! But this team alone cannot ensure such awesomeness…

Without a doubt, one of the most important factors in the success of APEX is its community. We encounter obstacles and learn solutions. But rather than hide what we’ve learned, we pour our newfound knowledge back into the community and together we continue to grow.

Do you have something to share? Share it at Kscope! You have until the 28th of October to submit an abstract – that’s just under 2 weeks. If your abstract is accepted then your registration to Kscope is complimentary!

Click here to learn more and submit an abstract. Good luck!

Martin D’Souza is running for President!

2011-10-12T18:33:00.001-04:00

Ok, so he’s not really running for President. But he IS running for a spot on the ODTUG Board of Directors and he could use your vote to help get there!

I’ve known Martin for several years now. One thing that really stands out about him is his unique perspective on just about everything. Generally speaking, the term “unique” cannot be taken for “good”, but with Martin it’s a sure bet. Over the years I’ve come to rely on this fact and have even caught myself wondering “What would Martin do?” a couple of times.

With Martin on the ODTUG Board of Directors, the APEX community would have a great representative in a position that could make a real difference. So what are you waiting for??? Click this link and vote for Martin!

Unfortunately, only ODTUG Members are able to vote I don’t want to make this an advertisement for ODTUG, but if you believe, as I do, that this is an all around good thing, then perhaps now is the time to join. At $99 dollars a year, it’s really not bad considering that you get discounts to Kscope and access to lot’s of good technical information.

Check out Martin’s statements below…

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.

APEX @ OOW 2011

2011-09-23T18:28:00.001-04:00

The Oracle Open World schedule builder is an impressive tool but unfortunately it's not very effective if you want to find APEX sessions. To help people find those sessions, I created a quick an dirty websheet.

Click here to access the websheet.

See you at OOW!

New Browser Security Attributes in APEX 4.1

2011-08-31T19:32:00.001-04:00

A friend recently asked if I could look into an issue he was experiencing with the new SkillBuilders Modal Page plug-in. The modal page would only display the loading image, no other content would ever load. After a little digging I tracked the issue down to some new features.

Oracle Application Express (APEX) 4.1 added two new Browser Security attributes: Cache and Embed in Frames. The attributes can be found by navigating to Shared Components > Security Attributes > Browser Security (region). Clicking on the items’ labels reveals some great documentation. I couldn’t find any other documentation online to link to so I’ve copied the contents here for everyone to see:

Cache

Enabling the cache allows the browser to save the contents of your application's pages in its cache, both in memory and on disk. If a user presses the browser back button, the page will typically be loaded from the cache, not from the server. If the cache is disabled, the browser is instructed to not save page content and will request it anew from the server.
From a security perspective the cache should be disabled, so the browser does not store sensitive data and will always request pages if the URL changes. Otherwise, it may even be possible to go back in the browser history after a logout and see cached content of the former session.
Disabling the browser cache will also prevent subtle back button issues with pages that use partial page refreshes for example pages with Interactive Reports.
If this item is set to "Disabled", Application Express will send the HTTP header cache-control: no-store which instructs the Browser to not cache the page contents on disk or in memory.
Note: This feature requires modern browsers that support the HTTP header response variable "cache-control".

Embed in Frames

Use this attribute to control if the browser is allowed to display your application's pages within a frame:

Deny: The page cannot be displayed in a frame, regardless of the site attempting to do so.
Allow from same origin: The page can only be displayed in a frame on the same origin as the page itself.
Allow: The page can be displayed in any frame.

Displaying pages within frames can be misused with "clickjacking" attacks, when an attacker uses multiple layers to trick a user into clicking on a button or link on another page when they were intending to click on the the top level page. Thus, the attacker is hijacking clicks (and/or keystrokes) meant for their page and routing them to another page.
Note: This feature requires modern browsers that support the HTTP header response variable "X-Frame-Options".

If you’re still reading this well done!

The Embed in Frames attribute was preventing the plug-in from working correctly - it was set to “Deny”. As the plug-in uses iframes, this attribute’s value must be set to either “Allow from same origin” or “Allow”. After upgrading to APEX 4.1, existing applications will be set to “Allow” but new applications will be set to “Deny”. Perhaps “Allow from same origin” would have been a better choice for the new default?

At the end of the day, the new Cache and Embed in Frames security attributes are very powerful in that they can help make your applications more secure with very little investment. Also, the additional benefits of the Cache feature sound great. However, developers should be mindful of the impact these attributes can have on their applications. Always test!

Upcoming Webinar – Introducing SkillBuilders Modal Plug-in

2011-08-24T22:29:00.001-04:00

Our next Application Express plug-in is ready to be introduced to the community. Although our plug-ins are generally well documented, sometimes it’s easier to learn how to use something by seeing it in action – this is your chance!

Abstract

Modal windows are very popular these days – and for good reason. They can help focus users’ attention on new content without disturbing their overall context within an application – and they’re cool too! Our latest plug-in, SkillBuilders Modal Page, was designed to display entire APEX pages as modal dialogs. Rather than just release the plug-in into the wild, we thought we’d take the opportunity to provide some hands on training with it. If you’re in need of a modal page solution, or you’d simply like to learn more about installing and leveraging plug-ins in APEX, do not miss this session.

Click here to register.

As usual, it’s free to attend – but it’s real training. I’ll show you several scenarios where use of the plug-in could be beneficial. Then you’ll learn how to perform the same customizations on your own once you’ve downloaded the plug-in. Everything from installation of the plug-in to event bindings with jQuery will be covered.

The webinar will run on Wednesday, August 31 at 12pm EDT. To see other time zones, click the registration link and then on the “Show in my Time Zone” link at the top of the page.

Webinar today: jQuery in APEX–An Introduction for Database Developers

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

Today I’ll be presenting another webinar for ODTUG: jQuery in APEX – An Introduction for Database Developers. In this presentation I leverage existing skills in SQL and PL/SQL to introduce important concepts in jQuery and JavaScript. I’ve done this presentation a few times now and it’s always well attended – must be the result of a steady stream of Oracle developers getting into APEX. The webinar will run from 3 to 4pm EDT and, as usual, it’s free to attend.

I hope to see you there!

Click here to register.

Abstract

Oracle Application Express developers often have strong backgrounds in SQL and PL/SQL, but JavaScript is another story. However, JavaScript is a very important part of any well-built Web 2.0 application. Luckily JavaScript libraries, such as jQuery which is integrated into APEX, have made the language much easier to use. In this session, learn about jQuery through a series of analogies, nine in total, which will relate important concepts in the library back to what you already know in the database world.

Save Value on Cascade 1.1

2011-06-13T16:53:00.001-04:00

We’ve just released an update to the Save Value on Cascade plug-in. There are two main changes in this release:

Shuttles are now supported. This update actually comes courtesy of Matt Nolan, aka The APEX Freelancer. Many thanks Matt!
Debug code was added. This will help to diagnose any issues should they occur.

You can view/download the updated plug-in, as well as our other plug-ins, by visiting our APEX Plug-in page.

Also, keep an eye out for a Modal dialog plug-in which will be released later this month!

Expert Oracle Application Express–BUY THIS BOOK

2011-04-03T12:34:00.001-04:00

I was delighted when John Scott approached me last year to ask if I’d like to help write a book. But when he told me that all author royalties would be donated, split 50/50, to the families of Carl Backstrom and Scott Spadafore there wasn’t much of a thought process – “Yes!” just flew out of my mouth.

Unfortunately, I never had the opportunity to meet Scott, but a quick look at the APEX forum’s “Top Users in Forum” list speaks volumes about the kind of guy he was – and his name will deservedly remain there for a long time to come. The number of people that Scott was able to help, myself included, is truly impressive and inspirational.

When it came to being helpful and inspirational, Carl was very much the same kind of guy that Scott was and I’m very grateful to have met him. He was incredibly influential in my development career, having helped me along while I learned the basics of client side development. He even introduced me to jQuery!

I find it especially rewarding that I was asked to write a chapter on plug-ins in APEX – a topic that often involves lots JavaScript. To me it’s proof positive that people like Carl and Scott live on in those that they helped and mentored. I will always strive to have the same impact on others as they had on me.

Ultimately, the book was authored by 12 of the best known APEX experts. Each author took on one chapter and tapped into their expertise with the product to provide many unique insights. For a complete listing of authors check out John’s post here: http://jes.blogs.shellprompt.net/2011/03/30/expert-oracle-application-express/

As a self professed plug-in addict, the chapter on plug-ins was a natural fit. I begin the chapter by laying out some of the tools used to build plug-ins, including the various components of APEX plug-in architecture, as well as some lesser known jQuery UI goodies (Carl would be proud)!

In the next part of the chapter, everything from the first part is put to use as I walk readers through the creation of 4 plug-ins – one of each plug-in type. These are NOT “Hello World” plug-ins. These are real plug-ins that demonstrate the kinds of techniques that could be reused to create new and useful plug-ins.

The end of the chapter covers some “best practices” that should help guide one’s plug-in development efforts down a successful path. These best practices cover different areas of plug-in development including Plug-ins in general, JavaScript & CSS, and PL/SQL.

The importance of purchasing this book cannot be overstated. Like many of the other authors that will receive a free copy of the book, I too have purchased one as well. So what are you waiting for? Please follow this link and complete your order today: http://amzn.com/1430235128

My 2011 Kscope interview

2011-03-16T10:00:00.001-04:00

Mike Riley, President of the Oracle Development Tools User Group (ODTUG), recently interviewed me in anticipation of Kscope 2011. Click here to read the interview.

I’m really looking forward to this year’s conference - the APEX related content looks very promising!

Hope to see everyone in Long Beach!

Upcoming Webinar – Taking Advantage of Plug-ins in APEX

2011-02-08T11:02:00.001-05:00

In two weeks I’ll be presenting my first webinar for ODTUG: Taking Advantage of Plug-ins in APEX. The webinar will run on Tuesday, February 22nd from 3 to 4pm. It’s free to attend and should provide lots of good information. I’ve already started downloading and testing every plug-in I can get my hands on – that's 68 plug-ins so far. Can’t wait to share some of my favorites! Click here to register.

Abstract

The Application Express (APEX) plug-in architecture is still relatively new – still celebrating “monthiversaries” in fact. But did you know you already have access to over 60 plug-ins for use in your applications? That’s right, the community is rallying around this exciting new architecture! But here’s the rub: you cannot benefit from what you do not know about.

In this session you will learn everything you need to start taking advantage of plug-ins in APEX. Where to find plug-ins and how to install them will be covered in the first part of the session. The second part of the session, which will consume the majority of the time, will provide an overview of some of the most useful and popular plug-ins. Be prepared to take mental notes because you never know when one or more of these plug-ins will come in handy.

Click here to see other free webinars from ODTUG.

Working together: Save Before Exit and Tabular Forms

2011-01-19T17:55:00.001-05:00

The SkillBuilders Save Before Exit plug-in is designed to make it very easy for developers to help prevent end users from loosing changes to work they’ve done on a page. When a user leaves a page it runs some checks to find out if changes have been made but not saved. If it finds any, it prompts the user before allowing them to go to the next page. If you’re not familiar with the plug-in, you can find it here along with the rest of our plug-in offerings.

Tabular Forms now have a similar technology built in – which is very clever. It’s needed in Tabular Forms because there are some built in controls, such as header sorting links and pagination links, that when used could result in end users losing their work. The built in functionality that helps prevent this works great, it even highlights the cells that have changed (perhaps I’ll add that to the plug-in).

Recently, two separate users of the SkillBuilders Save Before Exit plug-in let me know of some problems they encountered while integrating the plug-in with the built in Tabular Form functionality. After having been a little confused myself at first, I was delighted to learn that both can coexist quite nicely – even compliment each other.

Here are some guidelines to properly integrate the two:

Enable Partial Page Rendering (PPR) for the Tabular Form. This setting can be found under Layout and Pagination in the Report Attributes. PPR allows the tabular form to sort and paginate without causing the entire page to refresh. This prevents the plug-in from doing anything in these cases. The built in functionality will continue to work with PPR enabled.
Make sure that the sorting links, pagination links, as well as the “Add” button of the Tabular Form are NOT selected in the No Warning Selector attribute of the plug-in. Again, since PPR is preventing the page from reloading it is not necessary to include these elements. In fact, including them can even cause an issue that prevents the plug-in from working when it should.

Warning: the default No Warning Selector will include the Add button so make sure to change this. You’ll need to explicitly select the buttons and/or other elements that should allow a user to leave without being warned of unsaved changes.

So how do these two compliment each other? The warning functionality built into Tabular Forms only works when sorting or paginating through the report. But if the user clicks a breadcrumb or closes the browser, then changes will be lost. The plug-in can be used to give the user one last chance to save their changes.

Saving Current Values with Cascading LOVs

2011-01-17T14:56:00.001-05:00

A friend, Monty Latiolais, recently asked an interesting question regarding cascading LOVs:

Say you've got two LOVs...STATES and CITIES. They both default to 'ALL' and 'ALL'. Since CITIES is dependent on STATES, as soon as STATES is changed, CITIES is blanked out. What should happen is that CITIES gets re-evaluated as in the following example... let's say STATES is ALL and CITIES is "Houston". If one then changes STATES to "Texas", CITIES should remain "Houston" as that is a valid value for CITIES.

So basically, is it possible to maintain the selected value of an item if that same value exists in the list of values after refreshing? That’s a great question! Thanks to new events in the APEX framework and Dynamic Actions the solution is far easier than it would have been in the past!

Click here to see the demo but continue reading to learn how it all works…

There are a three main events you need to be concerned with when it comes to cascading selects:

change
apexbeforerefresh
apexafterrefresh

The change event is a standard part of JavaScript and the DOM. This event fires when the user manually changes the value of the select list but can also be triggered programmatically via JavaScript. The apexbeforerefresh and apexafterrefresh events are custom events in the APEX framework. They fire just before and just after AJAX requests refresh something on the page. The events work with many items and regions that utilize this technology.

In this example we have two select lists: parent and child. If you change the value of the child select list then the change event will fire and that’s it. But if you change the value of the parent select list a lot more happens to the child select. Here are some of the highlights:

The current LOV values are cleared out
The apexbeforerefresh event is triggered
An AJAX request brings back new values. This only happens if
1. optimize refresh is set to false
2. optimize refresh is set to true and all parent items are not null
The apexafterrefresh event is triggered
The change event is fired

Now, knowing all of this, how can we utilize the sequence of events to solve the original problem of keeping selected values? The answer lies in creating two dynamic actions.

The first dynamic action will store the current value of the select list so that we can access it later. This will typically happen when the change event fires but it will also happen when the page first loads. Here’s how to create the first Dynamic Action:

Right click the child select list and select Creation Dynamic Action.
Select Advanced.
Click Next >.
Enter a name for the first Dynamic Action.
Click Next >.
Set Event to Change.
Click Next >.
Set Action to Execute JavaScript Code.
Set Code to: $(this.triggeringElement).attr('data-last-value', $(this.triggeringElement).val());
Click Next >.
Click Create.

The second Dynamic Action will take advantage of the apexafterrefresh event and access the previously stored value. That value will then be used to look through the new options and if a match is found it will be selected. Here’s how to create the second Dynamic Action.

Right click the item (again) and select Create Dynamic Action.
Select Advanced.
Click Next >.
Enter a name for the second Dynamic Action.
Click Next >.
Set Event to After Refresh.
Click Next >.
Set Action to Execute JavaScript Code.
Set Code to: $(this.triggeringElement).children('option[value="' + $(this.triggeringElement).attr('data-last-value') + '"]').attr('selected','selected');
Click Next >.
Click Create.

So two lines of actual code, not bad at all!

Plug-in Update: SkillBuilders Super LOV 1.2

2011-01-13T07:53:00.001-05:00

The APEX community has been providing us with lots of great feedback on Super LOV thus far... Keep it coming and we’ll keep making it better!

This release includes the following changes:

Integrated buttons in the main input. The buttons are styled using jQuery UI which is consistent with the rest of the plug-in. Also, the “clear” button switches to a confirmation button to help prevent accidental clearing.
Added “Effects Speed” attribute. Many people have had great things to say about the transition effects implemented in Super LOV but some people complained they were too slow for heavy data entry. Now you can adjust the speed to make it faster or set it to instant which essentially disables the effects altogether.
Added “Default Search Column Number” attribute. Currently this will just default the column that is searched when the modal dialog is opened. This attribute will become even more important with the next release – when the item can be made “enterable”.
Fixed bug that prevented Cascading LOV attribute from working. The item now “cascades” as it should with both parent and child items.

Future plans include:

Making the input “enterable”. This will allow a user to focus in the main input and begin typing. If a single match is retrieved in the LOV, then the hidden item will be updated and the LOV will never open. If multiple matches are found then the LOV will open “pre-filtered” to allow for selection.
Allowing LOV selection to update multiple items/elements. Lot’s of people (especially those from Oracle Forms) have been requesting this functionality so we’ve added it to the list.

To get this plug-in, as well as our other plug-in offerings, visit new “Plug-ins” page at SkillBuilders.com. Future plug-in releases, including this one, must be downloaded from that site. Downloads require free registration, but it’s a small price to pay for some great functionality. Enjoy!

Plug-in Update: SkillBuilders Super LOV 1.1

2010-12-23T11:05:00.001-05:00

It’s been less than a week since we released Super LOV and today we’ve released version 1.1.

Click here to go to the download page (look for the demo link too).

This release includes the following changes:

Improved Dialog sizing and resizing algorithms
Added “Validate Value” attribute (declarative means to protect against malicious users adding values not in the LOV)
Added “Display Null Values as” attribute
Added “Read Only” attribute
Added support for several display attributes (such as width and max width)

Future plans include:

A custom input with integrated buttons for opening the LOV and clearing the selected value
Making the input “enterable”
Adding an attribute to disable or adjust the speed of the effects used by the plug-in

This week we passed 1000 total downloads for the plug-ins we’ve released. If you’ve not yet seen them all, make sure to check out these other plug-ins from SkillBuilders:

Schedule – Makes event scheduling easy
IR Header Float – Keeps Interactive Report headers in view when scrolling
Save Before Exit – Warns users if they try to leave a page without saving their changes

Happy Holidays!

New Plug-in in the Wild! SkillBuilders Super LOV

2010-12-17T07:15:00.001-05:00

The built-in Popup LOV item in APEX can be a powerful tool when the number of possible selections is too high for other item types that use LOVs, such as a select list. However, people often wish to display more information when making a selection than this item type allows. In the past, developers worked around this issue by either concatenating values in the display column or by creating a custom solution.

Custom solutions provided the ultimate flexibility but required a lot of knowledge and were not always easily transferable to other items, pages, or applications. The SkillBuilders Super LOV plug-in is designed to make these issues a thing of the past while providing a rich, web 2.0 experience.

Click here to view more information. You’ll find a link to a demo on that page.

Enjoy!

SkillBuilders Schedule - EA2

2010-12-08T22:26:00.001-05:00

I’m very excited to announce (finally) the second early adopters release of the SkillBuilders Schedule plug-in.

Click here to view and download…

There were a number of bugs fixes and enhancements in this release. Here are the biggest changes:

Client side validations – a number of validations are performed on the client side to help make sure that data is entered as it should be.
Start time linked to end time – a change of the start time will automatically shift the end time the same quantity.
Oracle date format – the previous date format mask was expecting a jQuery date format (and was ignoring it shortly thereafter). Now Oracle date formats are translated for you (and are not ignored – bonus). This limits the number of formats that will work but should be easier to use overall.

This is the second of three planned early adopters releases for this plug-in. As such, bugs are to be expected. If you find any bugs, have feature requests, or would like to provide any other feedback, feel free to email us at support@skillbuilders.com.

The next release, the last of the early adopters, will focus mostly on configuration options to make various customizations declarative. Enjoy!

Putting Children in their Place

2010-11-21T16:10:00.001-05:00

Of course by “children” I am referring to child regions in APEX and by “place” I mean side by side – I’m sure you all assumed as much.

The ability to nest regions by establishing parent/child relationships came as a feature of APEX 4.0. Although seemingly small when compared to some of the other features introduced with that release, it’s a feature that should not be overlooked as it allows for complex layouts without the previous complexity.

When you first start working with nested regions, you may notice that sub regions tend to stack on top of each other rather than side by side. This is simply the default nature of the HTML being used. But ultimately you can control how this works – you need only learn about CSS floats and how to implement them in APEX!

Although I often refer people to w3schools.com for simple tutorials, the best tutorial I’ve found to date on CSS floats can be found here:
http://css.maxdesign.com.au/floatutorial/ (make sure to check out tutorial 8).

Also note this odd behavior as we’ll need to account for it:
http://www.quirksmode.org/css/clearing.html

Ok, now that you’ve armed yourself with a thorough understanding of what’s involved, you can put it to use in APEX by setting some style rules for the region via the Region Attributes. Have a look at a demo here using the new theme 21 – Scarlet.
http://apex.oracle.com/pls/apex/f?p=22073:1

My Future Feature Wish List for APEX

2010-11-17T22:56:00.001-05:00

I started making this list when I saw a few others posting their own wish lists but I never posted it. The recent update to the APEX Statement of Direction inspired me to finally get this out and I was very happy to remove a few thing because they are now targeted for APEX 4.1. So here it is…

IR Enhancements

Run time

Updateable columns – This is one of those “holy grail” type of enhancements - hard to implement but still possible. It had to be said…
Freeze column – I saw this feature while doing an ADF tutorial recently. Hey, if they can do it, so can we.
Drag and drop column reorder – Admit it, that would be cool.

Design time

Required filters – I put this on the top for a reason. Some queries don’t run well without some filters (think EBS). It would be nice if we could say that “this, that, and the other column must be filtered”.
Column level “do not print” option – Some columns are made for the web and the web alone.
Freeze column – Similar to its run time counterpart, only this would allow developers to specify some columns that should always be visible. Although, if overused, it would defeat the point of an interactive report (limit the number?).
Drag and drop column reorder – I know we have this ability via the tree view in the Page Definition screen, but I believe it should be available in the report attribute screen as well (same goes for classic reports)

Tabs Overhaul – I know some work was done with APEX 4 to make tabs easier to work with but it was not enough. The key to simplicity here is getting rid of “tab sets” (or at least hiding them by default).
LOV Protection – When you define a list of values, you expect users to choose from that list. But those with enough knowledge can add to the options in a list of values after the page loads. You can protect yourself with validations but they can be costly and involve more work/maintenance. Martin D’Souza has done quite a bit of work in this area taking a hashing approach rather than a re-query (smart move). It would be great if this was implemented in APEX as a new type of validation, like the Not Null validations, added for any item that uses an LOV.
Validation chaining – I recently found myself writing validations that should only run if other validations have passed. This requires a bit of duplication. It would be great if there was an option in validation conditions that allowed you to select another validation.

So what do you think? Any ideas of your own?

SkillBuilders Save Before Exit–2.0

2010-11-12T15:50:00.001-05:00

I’ve just released an update to the SkillBuilders Save Before Exit plug-in. This was a major release (especially for such a tiny plug-in) with some good new features:

Some users reported false positives with regard to change detection. To prevent this the plug-in now uses true change detection and can even detect changes to the new rich text editors in APEX 4.
Support has been added for custom themes/templates via a newly exposed No Warning Selector. This allows you to use a jQuery selector to specify which elements should allow the end user to leave the page without being warned.
An Ignore Change Selector option has been added which allows you to specify which element should not be checked for changes.
New methods, enableWarning and disableWarning, allow developers to programmatically specify whether change detection should occur via JavaScript.

For those of you using the plug-in and planning to upgrade, make sure to check out the upgrading section of the documentation.

Click here to download. Enjoy!

SkillBuilders IR Header Float–1.1

2010-10-22T06:49:00.001-04:00

I’ve just release an update to the IR Header Float plug-in. The update fixed some alignment/spacing issues that some people experience. That fix also allowed me to simplify the plug-in by removing the adjustment step during installation (which is now automatic).

Click here to view and download the floating awesomeness.

Developing plug-ins in my “spare time” over the last few months has been a blast! Stay tuned for the next one, it’ll make your day.

New Plug-in in the Wild! SkillBuilders IR Header Float

2010-10-06T08:24:00.001-04:00

Have you ever scrolled down a report with lots of rows only to loose site of the headers and thus the context of the information you were viewing? With the amount of data most of us are working with these days, it’s actually rather common. While there are a number of solutions for the problem, they can be surprisingly difficult to implement – until now.

Our latest APEX plug-in, the SkillBuilders IR Header Float, aims to solve the problem for Interactive Reports. It works by floating the headers at the top of the screen if the user scrolls past them. Then, if the user scrolls back up, the headers are placed back at their original positions.

Check out the demo by clicking here.

If you like what you see and would like to put it to use in your own applications, then visit http://www.apex-plugin.com/ to download a copy. Enjoy!

Webinar: JavaScript in APEX – The Lost Lesson for Database Developers

2010-08-11T12:44:00.001-04:00

I’ll be hosting another free webinar next week titled JavaScript in APEX – The Lost Lesson for Database Developers. The webinar will run on Friday, August 20th from 1pm to 2pm EDT (moved from Wednesday the 18th). The presentation is the one I delivered at ODTUG Kaleidoscope 2010. So if you missed the conference or the presentation, now is your chance. Click here to register.

Abstract

Oracle Application Express developers often have strong backgrounds in SQL and PL/SQL, but JavaScript is another story. However, JavaScript is a very important part of any well-built Web 2.0 application. In this session, explore JavaScript through a series of analogies, 9 in total, that will relate important concepts in the language back to what you already know in the database world.

Audience

Anyone interested in Oracle Application Express.

Language

English

Webinar – Oracle Application Express Plug-ins

2010-07-21T09:42:00.001-04:00

I’ll be hosting another free webinar next week titled Oracle Application Express Plug-ins. The webinar will run on Wednesday, July 28th from 1pm to 2pm EDT. Click here to register.

Abstract

If you use Oracle Application Express then you have probably heard of "plug-ins" by now. But you may still have some unanswered questions, such as:

What exactly is a plug-in?
What can plug-ins do for me?
What plug-ins are available?
How do I install and use plug-ins?

These questions and more will be answered during this webinar. This will be a high level introduction to plug-ins during which we will demonstrate how plug-ins can save your organization time and money by easing development efforts. Attendees will receive a copy of the next SkillBuilders plug-in – one week before its general release to the public.

Audience

Anyone interested in Oracle Application Express.

Language

English

Video - JavaScript for PL/SQL Developers

2010-07-19T08:09:00.001-04:00

While at ODTUG this year, I had the pleasure of presenting three different times. The one presentation I did on my own was “JavaScript in APEX – A Lesson for the PL/SQL Developer”. The presentation was inspired by my experiences learning the language and later teaching it to others with similar backgrounds.

I almost called the presentation “JavaScript, the Lost Lesson” because that seems to be the reality for so many APEX developers. When you come from the database world, JavaScript can seem so awkward and difficult. Believe it or not, it’s not that bad! In fact, as a database developer, you are already have a good grasp on some of the most important concepts – you just don’t know it yet... Have a look at the abstract, and if you are interested, watch the recording.

Abstract

Oracle Application Express developers often have strong backgrounds in SQL and PL/SQL, but JavaScript is another story. However, JavaScript is a very important part of any well-built Web 2.0 application. In this session, explore JavaScript through a series of analogies, 9 in total, that will relate important concepts in the language back to what you already know in the database world.

Video

Click here to view this video, along with various other recordings, at SkillBuilders.com.

Look for JavaScript in APEX – A Lesson for the PL/SQL Developer.

New Plug-in in the Wild! SkillBuilders Save Before Exit

2010-07-16T07:54:00.001-04:00

Back in 2008, I blogged about how alert users to save their work before leaving a page. Although that was some time ago, the article is still popular to this day. Even while presenting at Kaleidoscope a couple weeks ago, someone asked if we would be creating a plug-in to make this functionality easier… While uncertain at the time, the answer is pretty clear now: Why yes, yes we will ;)

Click here to view/download the plug-in!

Description

Have you ever filled out a form and accidently left the page without saving your work? It is actually quite easy to do. Thankfully, browsers have features to help prevent this from happening. The problem, however, is that taking advantage of those features can be quite difficult - until now.

The SkillBuilders Save Before Exit dynamic action plug-in allows you to notify your users if they leave a page without saving their work. Simply install the plug-in and add it to your page!

Features at a glance

Activates when a user makes a change to an item on the page.
Warning message displayed if user tries to leave page without clicking a button. Includes: Closing the browser, clicking the back button, clicking a link, etc.

NULL is NULL??? NULL is NULL!!!

2010-07-12T16:47:00.001-04:00

I remember when this tweet popped up back in December of 2009:

APEX Poll: Do you like the existing %null% behavior of select lists? Do you use it in your applications?

The tweet was hinting that one of the oddities that had existed in APEX for quite a while may soon be a thing of the past. If you’re not sure as to what exactly this tweet was referring, have a look at either of these posts:

http://www.inside-oracle-apex.com/display-null-yes-and-null-problem/

http://www.talkapex.com/2009/07/how-to-resolve-null-issue-in-apex-lovs.html

The fact was that if you created an item that used a List of Values (LOV) and you set the Display Null Value option to Yes, but didn’t specify a Null Return Value – in other words you left the field NULL – then a default value of “%null%” was supplied for you. The problem was, as you’ve probably already concluded, that “%null%'” != NULL.

Many people just accepted this as “the way it works” and then figured out a workaround or adopted one of many generic solutions to fix the problem. However, this is no longer necessary as it’s been fixed in APEX 4.0. Now if you use the Display Null Value option with an LOV and don’t specify a Null Return Value, then NULL is what you will get. It’s a beautiful thing – NULL = NULL! You can’t see it, but I’m doing my happy dance ;)

For those of you worried about backward compatibility, the new feature was implemented in such a way that in will only affect things going forward. Also, if you look under “Home > Application Builder > Application 999 > Utilities > Upgrade Application”, you’ll see an option to convert an application in bulk: “Remove %null% in LOV Null Return Value of Page Items”.

Kaleidoscope – A look back and then ahead

2010-07-08T09:41:00.001-04:00

The first time I went to ODTUG Kaleidoscope was 2008 in New Orleans. To meet the many people that I’d communicated with for so long and some cases aspired to, well, that for me was a real treat. But after missing last year’s conference (due to budget cuts), I’d nearly forgotten the feeling.

I’m happy to say this year’s Kaleidoscope did not disappoint! It was great to see some familiar faces and some new ones as well. The coverage of APEX is second to none and allows for an impressive amount of knowledge transfer. Can’t wait to go again next year…

It’s hard not to get excited about the new features released in APEX 4. I was joking with Patrick Wolf about how APEX is getting closer to Forms – but in a good way ;)

Forms has a very rich client side event system that allowed developers to easily create highly interactive applications. It’s as though you can add “triggers” to just about any end user action – even if just to call an anonymous PL/SQL block.

In the web world, a similar client side event system has existed for some time - perhaps even more robust than that of Forms. But to take advantage of it you had to learn some other languages: JavaScript, HTML, and CSS.

With APEX 4.0 that’s no longer “the rule”. Dynamic Actions have put a declarative framework on top of the event system used in web development and the learning curve to create interactive applications is amazingly small. Knowledge of JavaScript is not yet obsolete, but perhaps the APEX team is not quite finished…

And then there’s the new plug-in architecture. The community now has a stake in the future success of APEX like never before. Keep an eye on plug-ins… As the the community migrates APEX systems over to 4.0, the number of plug-ins will grow and they will mature over time. Hopefully when you have a need, there’ll be a plug-in for that!

Have a look at some of the plug-ins being added to http://www.apex-plugin.com/.

I used to write complex blog posts that would walk readers through a series of steps (which often included lots of code) to achieve a task. Many readers wanted the end result, but didn’t want to have do deal with the code – plug-ins are making that possible. And for the others that did want to look at the code, you’re more than welcome to open the plug-in up and take a look “under the hood” so to speak.

In short, lots of changes - all of them great. I’m already looking forward to APEXposed!

New Plug-in in the Wild!

2010-07-07T08:29:00.001-04:00

I’m happy to announce the first early adopters release of the SkillBuilders Schedule item plug-in. If you attended the ODTUG Kaleidoscope conference, then you had two changes to see the plug-in demonstrated live: during the Sunday Symposium and then again in the Plug-in Showcase. Since then I’ve added custom time pickers and simplified jQuery UI skinning (now one small change in the items configuration settings provides an entirely different look and feel).

Click here to view/download the plug-in!

If you didn’t get to attend the conference here’s the introduction:

Event scheduling is a common task that developers repeatedly face when creating applications. The types of events can vary greatly as can their configuration options. Some events are simple, occurring only once while others are more complex and can repeat indefinitely.

Developers may choose to save time by supporting only the types of schedules that the requirements dictate. This often results in “one-off” configurations with custom logic that cannot be reused very easily. The SkillBuilders Schedule item plug-in was designed to greatly simplify and standardize event scheduling in APEX applications.

The SkillBuilders Schedule item plug-in supports single occurrence events as well as those that recur daily, weekly, monthly, and yearly. Its options can easily be customized via a declarative interface to meet specific requirements and its UI has been skinned using jQuery UI themes so customizing the look and feel to match your APEX theme is as simple as changing one word in the item’s configuration settings.

Anyone that has worked with events in the past is aware that it all comes down to dates. To that end, the SkillBuilders Schedule item plug-in comes with a PL/SQL package based API that can be used for various functionality – even getting the dates over which an event will occur.

Look out for more plug-ins coming soon!

Webinar - Oracle Application Express 4.0 is a Game Changer

2010-06-15T14:36:00.001-04:00

I'll be hosting another free webinar next week titled Oracle Application Express is a Game Changer". The webinar will run on Wednesday, June 23rd from 1pm to 2pm EDT. Click here to register.

Abstract:

Every once in a while a new version of a product is released that so radically improves upon the previous version it is referred to as a "game changer". APEX 4.0 is a game changer – and it's closer than you may think! Some of the highlights of the release include:

Improved Interactive Reports – Ever wanted to share a report you customized with others or schedule a report to be emailed to you? Now you can!
Websheets – Websheets are a new style of application that put the controls in the hands of the end user rather than the developer. If spreadsheets are causing you headache but a traditional application seems like overkill then Websheets are the solution you've been looking for!
Dynamic Actions – Dynamic Actions allow developers to create highly interactive, web 2.0 applications declaratively – no knowledge of JavaScript required!
Plug-ins – The new plug-in architecture has made APEX more extensible than ever before by allowing the community to add custom items, regions, processes and dynamic actions. All you have to do is download, install, and use!

In this webinar we will talk about, and in many cases actually demonstrate, these new features along with many more. If you're currently using APEX, or if you've been holding out for any reason, do not miss this webinar!

Audience:

Anyone interested in creating impressive web applications quickly and easily.

Language:

English

New Region Events in APEX 4 – This is exciting!

2010-05-22T13:11:00.001-04:00

With the right tools just about anything is possible. APEX 4 will be introducing all kinds of new tools many of which are well known at this point. Anthony Rayner recently let me know about another new feature not many people know about: new JavaScript events associated with AJAX enabled regions like the interactive and classic reports.

When such a region refreshes, two events will be triggered:

apexbeforerefresh
apexafterrefresh

A developer can bind to these events as follows:

apex.jQuery('#[region id]').bind('apexafterrefresh', function(){
// your code here.
});

jQuery’s “live” method could have been used in some cases to overcome the lack of such events – but it wasn’t always enough. Some developers came up with clever workarounds using timeouts but like most workarounds the solutions were not exactly optimal.

The ways in which these new events can be used may not be immediately obvious, but when developing highly interactive web 2.0 applications such events are necessary. What’s important is that you remember these new events exist so you can employ them when you run into a situation that requires their use.

Here’s an example where someone wanted to display a “refresh timestamp” when an IR region refreshes. I’ve put together a demo that uses the apexafterrefresh to accomplish that task as well as a more complex one: floating headers!

Have a look at the demo here. Don’t forget scroll down to get the full effect.

For those of you that would like to duplicate this functionality you’ll have to wait for APEX 4 (perhaps I’ll turn it into a plugin), but here’s the code:

Upgrading to APEX 4.0 – it’s as easy as 1, 2, 3

2010-05-13T18:54:00.001-04:00

When the APEX development team at Oracle recently moved to the third early adopters release of APEX 4.0, I panicked! I didn’t see the same “warning emails” that were sent with the previous transition and I thought I had lost some code that I had written for some presentations at the upcoming Kaleidoscope event.

Luckily, that was not the case. The second early adopters release was still available so I was able to export my application an import it into the new release.

During the import I stumbled upon a fantastic new feature that I had not yet known about: Upgrade Application (previously known as the Migration Assistant)!

You can see the icon available on the right after an application import.
After clicking the icon you’re taken to an interactive report which lets you know which types of upgrades are available for your application.
From there you can click on the number associated with the upgrade type to learn more about the upgrade and where it will be applied. Simple check the checkbox where you want to perform the upgrade and click Update. That’s it!

As you can see, taking advantage of some of the great new features of APEX 4.0 will be as easy as 1, 2, 3!

Webinar - Oracle Application Express as a Reporting Solution

2010-05-03T08:51:00.001-04:00

I’ll be hosting a free webinar this week, “Oracle Application Express as a Reporting Solution”. The webinar will run on Wednesday, May 5th from 1pm to 2pm EDT. Click here to register.

Abstract:

Many organizations use applications that run on Oracle such as SunGard Banner, FAMIS, and Oracle Applications to name a few. These applications often offer reporting capabilities that are rigid or ineffective when it comes to keeping up with changing business needs and/or business processes viewed as "unusual" or "non-standard". More and more, these organizations are turning to Oracle Application Express (APEX) to fill this reporting void. Because of its small learning curve and impressive capabilities, APEX is the perfect solution to deliver all kinds of reports. In this webinar, learn how to use APEX to build applications used solely for reporting purposes. Topics will include:

Creating both standard and interactive reports
Creating drill down reports
Creating charts
Creating report menus for navigation
Report printing options
Securing access to data and reports

Audience:

Anyone interested in reporting with APEX.

Language:

English

CKEditor and Those Pesky CLOBs

2010-02-11T19:50:00.001-05:00

For a while now APEX has come with a neat little item type… the HTML editor. It is available in two flavors: standard and minimal. Whenever an end user needs the ability to edit some HTML content, whether it’s for a web site, an email, or whatever else, you’re able to utilize one of these two items with minimal effort.

For me, however, there are two problems current HTML editors. The first problem is that they’re little outdated. You may have noticed that I used the name “CKEditor” rather than “FCKEditor” in the title of this blog post. This is because the latest version of the product has been renamed due to complaints about the original name (named after it’s creator but you may see the issue). The second problem with the current HTML editors has to do with how easy it is to exceed the current 32767 size limit for APEX items.

The good news is that APEX 4 already addresses the first problem for sure. While it will be possible to add the old HTML editors, the new version will be available too, along with a handy option that allows you to easily choose between several different themes. The second problem, that of the size constraints, may also be going away in the near future. As I understand it, APEX was never really the “cause” of the problem, it was mod_plsql, the lower level plumbing so to speak. With the release of the new Java based listener, this should no longer be an issue so we may eventually see a resolution – we’ll just have to wait and see…

But for the impatient/hard working folks out there, I have good news: you don’t have to wait for APEX 4 to be released to use the new CKEditor or move CLOBs to and from an APEX page and the DB. And while the steps involved to get this functionality now are not as simple as just using a built in item type, you’ll find you have more control than normal which is always beneficial.

The following sections detail some of the prep involved as well as the techniques I use to deal with the two main processes surrounding CLOBs: moving them from the browser to the database and moving them from the database to the browser. There are many different ways to go about implementing this functionality, this is just one. Here’s a demo if you’d like to check it out.

Prep

Download the new CKEditor files, unzip them, then move them to a file server that you have access to. Only one file need be included to make CKEditor available to your application (example in JavaScript process below). I recommend you do this at the page level rather than at the application level to avoid unnecessary page weight.

Note: When I first did this I got some JavaScript errors and found the cause to be a BOM used in the source files. I used notepad++ to remove them from the relevant files (I can provide more details if others have the same issue).
Have jQuery and jQueryUI available to the application. The code below uses Google’s hosted files. I also use the BGI Frame plugin.
Add an item to the page where you want the editor to appear. The type should be Display as Text (does not save state). Leave all the defaults but remember the name. In the example code below, the name of the item is PXX_CLOB.

CLOB – From Browser to DB

Typically, a user clicks a submit button which submits the page for processing. The APEX engine then executes the various computations, validations, and processes that have been added to the page before branching to another. When working with CLOBs, we need to add a detour before the pages is submitted.

The “extra stop” is actually an AJAX process which moves data from the web page to the database before the page is submitted. A while ago this required quite a bit of code to accomplish, but Carl Backstrom once introduced a generic set of utilities that made the task much easier. As far as I know, they’re still the best means to move a CLOB on and off a page in APEX.

The basic concept is to use a JavaScript object (used to encapsulate the AJAX process) which moves the CLOB from the page to an APEX collection named CLOB_CONTENT. The collection will have one member and the value of the CLOB will be in the column named CLOB001. When the transfer of data from the page to the database is complete, the page is then submitted for processing. This change may necessitate modifications to page processes as the CLOB value will be in the collection rather than in the item itself.

Here’s a look at some sample JavaScript to handle the AJAX process. This includes references to required external files and code related to initializing the page (more on that below). I added additional comments to better explain the code.

The main JavaScript function responsible for page processing is named submitPage. This function can be called by setting the URL redirect for a button to the following (actual parameter will vary depending on the request):

Here is an example DML processes that works with the CLOB in the collection:

CLOB – From DB to Client

If you examined the JavaScript from above you may have noticed that some of the code was related to initializing the page. This code utilizes the same concepts from the previous part, only data is being moved from the APEX collection (CLOB_CONTENT) to the page.

For this reason we must properly populate that collection when the page loads – before the AJAX process fires. I usually go about this one of two ways:

Populate the collection with the value I want to work with – if such a value exists.
Initialize a "NULL" collection (to avoid NO_DATA_FOUND errors).

Tabs – Could they be easier???

2009-12-28T19:41:00.001-05:00

Tabs in APEX are overly complex for the vast majority of applications and users. I believe the problem is related to two things which are somewhat interconnected:

A Lacking UI
Tab Sets

Let me explain…

A Lacking UI

The problem (as I see it):

First of all, it’s obvious that some attention to detail was given to the tabs screen – we even see a basic mock up of our tabs. It just doesn’t go far enough. The UI doesn’t do enough to abstract the underlying data model.

Let’s say for example I start with no tabs and later decide to add one level tabs. If I try to first add tabs at the parent level, which it allows me to do, it will fail with this error: Tab set or new tab set must be specified. So I can’t create a parent without a child? I suppose that could make sense… Ok, so I’ll go down to the first level and create my first tab there. But the first screen asks me to create a Tab Set, and the next screen, a Parent Tab Set and Parent Tab Set Text. But I don’t want two levels! You get the idea…

A (possible) solution:

I’d like to see an application level attribute that specifies whether the application is using no tabs, one level, or two level tabs – could be read only in the Application Definition page. Then the interface through which we work with tabs could be reworked taking that attribute into account.

If the application is currently set to “no tabs” and I go to Tabs in the Shared Components, I’d like to see a big/simple/clean message that lets me know I’m not using tabs and presents me with a button to enable tabs for the application.
If the application is currently set to “one level tabs”, I’d only like to see one level of tabs. Pseudo parent tabs are not relevant to me – they’re just confusing. I’d also like to see two big buttons that allow me to either disable tabs for the application or enable two level tabs for the application.
If the application is set to “two level tabs”, I’d like to see both levels. I’d also like to see two buttons that allow me to either disable tabs for the application or go down to one level tabs for the application.

Of course these buttons that allow me to change the tab settings for the application would take me into a wizard to break down the process, somewhat like they do now but with two big differences: I’d remove tab sets related details altogether (more on that later) and I would add some things to attempt to make it a more complete transition, for example, a selection for a new default page template that uses the tab class I’m going to.

Tab Sets

The problem (as I see it):

Frankly, I don’t really see what they are for; tab sets just seem to add to the confusion surrounding tabs. Why do we need tab sets – standard or parent? As best I can tell, they exist to make the process of linking standard tabs to parent tabs easier. But that alone doesn’t seem sufficient to justify their existence so I must missing something…

Think about the options presented when creating a new page:

Would you like to use tabs for this new page?

No

Yes - Create a new tab set and a new tab within the tab set.

Yes - Use an existing tab set and create a new tab within the existing tab set.

Yes - Use an existing tab set and reuse an existing tab within that tab set.

If my application was set to no tabs, as indicated by the new application level attributed I mentioned earlier, why should I even see this step in the process?

If my application was set to 1 level tabs, why should I care about tab sets? I think this would make more sense:

Would you like to use tabs for this new page?

No

Yes - Create a new tab.

Yes - Reuse an existing tab.

If my application was set to 2 level tabs, I still don’t think I need tab sets. I think we could start with this:

Would you like to use tabs for this new page?

No

Yes

If I select Yes, I should first have to choose the parent tab, then choose to either create a new standard tab or reuse an existing one linked to the selected parent.

A (possible) solution:

Make tab sets a “feature” that is disabled by default and must be enabled for an application. This way new users will not have to be burdened by them and users that like them can still get to them.

What do YOU think??? Let me know if agree, disagree, or have any ideas of your own.

Parlez-vous français??? There’s an APEX Forum for that.

2009-12-10T17:45:00.001-05:00

Louis-Guillaume recently blogged about the release of a new French forum at APEXQuebec.com (click on the Forum tab). A couple of the things I like about the forum… First of all, it’s an APEX application - another great example of what can be done using APEX! Second the forum has various categories to help get you to the help/information you’re looking for a little faster. I don’t speak French, but for those of you that do, enjoy!

APEXposed 2009

2009-11-09T16:16:00.002-05:00

I just arrived in Atlanta for APEXposed. This promises to be an excellent event with a lot of great sessions. It will be nice to see some familiar faces as well...

Free Webinar with Steven Feuerstein

2009-11-02T12:10:00.001-05:00

SkillBuilders will be presenting a “Best of PL/SQL” class with Steven Feuerstein. In anticipation of the event, we will be hosting a preview webinar led by Steven, entitled "Look What You Can Do With PL/SQL!" This will be tomorrow, November 3rd, from 11am to 12pm EDT.

Here’s the description of the webinar:

This webinar provides a whirlwind tour of some of the most exciting features of PL/SQL that you all should know about and use, including DBMS_ERRLOG, FORALL, BULK COLLECT and DBMS_UTILITY.FORMAT_ERROR_BACKTRACE.
This webinar is a preview of Steven's class, "Best of Oracle PL/SQL," which SkillBuilders will present in Needham, MA on January 26-27.

Audience: Oracle developers and DBAs with a working knowledge of PL/SQL and at least a year's worth of experience with the language. All developers, from beginner to expert, will benefit from Steven's ideas and examples.

Oracle ACE Director Steven Feuerstein is an expert on the Oracle PL/SQL language. He is the author or coauthor of 10 books on PL/SQL including Oracle PL/SQL Programming and Oracle PL/SQL Best Practices, is a PL/SQL Evangelist and has been developing software since 1980. His current obsession is improving PL/SQL code quality through automated unit testing. You can find out more about Steven and his PL/SQL-related activities by visiting www.stevenfeuerstein.com and www.ToadWorld.com/SF.

Transposed Report Example

2009-11-02T11:00:00.001-05:00

Sometimes the way data is stored in the database can make it difficult to display in a report. Let’s say, for example, I was creating an “Events Tracking” application. If the application was only being designed to track one type of event (concerts) I could probably get away with one table:

With this single table design, I can create a report in Apex with a simple query using a classic report with the type set to SQL Query:

SELECT artist,
   description,
   venue,
   event_date,
   price
FROM events

But what would happen if the application was being designed to track multiple types of events where each event type could have a variable number of event attributes? In this case I might consider a design that uses a few different tables:

With this design I have a lot of flexibility but when I generate a report on the data things get a little tricky. I want a report similar to the simple query where each column is an event attribute and each row represents one event. Of course, I’d only be displaying one type of event at a time…

In order to accommodate the flexible table design, I need to take advantage of a more complex report type in Apex: SQL Query (PL/SQL function body returning SQL Query). At the end of the day, both reports are based on a SQL Query. But in this one I use PL/SQL to dynamically build the query on the fly.

You can check out the demo here. Follow these steps to recreate the demo in your environment:

Set up the demo tables and data - do this in a development environment.

CREATE TABLE EVENT_TYPES( 
   ID NUMBER NOT NULL, 
   TITLE VARCHAR2(100 BYTE), 
   CONSTRAINT EVENT_TYPES_PK PRIMARY KEY (ID) 
)
/

CREATE TABLE EVENT_TYPE_ATTRIBUTES( 
   ID NUMBER NOT NULL, 
   EVENT_TYPE_ID NUMBER, 
   TITLE VARCHAR2(100 BYTE), 
   DISPLAY_ORDINAL NUMBER, 
   CONSTRAINT EVENT_TYPE_ATTRIBUTES_PK PRIMARY KEY (ID), 
   CONSTRAINT EVNT_TYPE_ATRS_EVNT_TYPES_FK FOREIGN KEY (EVENT_TYPE_ID) 
      REFERENCES EVENT_TYPES (ID) 
)
/

CREATE TABLE EVENTS ( 
   ID NUMBER NOT NULL, 
   EVENT_TYPE_ID NUMBER NOT NULL, 
   EVENT_DATE DATE NOT NULL, 
   DESCRIPTION VARCHAR2(4000) NOT NULL, 
   CONSTRAINT EVENTS_PK PRIMARY KEY (ID), 
   CONSTRAINT EVENTS_EVENT_TYPES_FK FOREIGN KEY (EVENT_TYPE_ID) 
      REFERENCES EVENT_TYPES (ID) 
)
/

CREATE TABLE EVENT_DETAILS( 
   ID NUMBER NOT NULL, 
   EVENT_ID NUMBER NOT NULL, 
   EVENT_TYPE_ATTRIBUTE_ID NUMBER NOT NULL, 
   EVENT_TYPE_ATTRIBUTE_VALUE VARCHAR2(4000), 
   CONSTRAINT EVENT_DETAILS_EVENTS_FK FOREIGN KEY (EVENT_ID) 
      REFERENCES EVENTS (ID), 
   CONSTRAINT EVENT_DTLS_EVENT_TYPE_ATRS_FK FOREIGN KEY (EVENT_TYPE_ATTRIBUTE_ID) 
      REFERENCES EVENT_TYPE_ATTRIBUTES (ID) 
)
/

INSERT INTO event_types (id, title) VALUES (1, 'Football Game');
INSERT INTO event_types (id, title) VALUES (2, 'Concert');
INSERT INTO event_types (id, title) VALUES (3, 'Movie');

INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (1,1,'Home Team');
INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (2,1,'Visiting Team');
INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (3,1,'Line');
INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (4,2,'Band or Singer');
INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (5,2,'Venue');
INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (6,2,'Ticket Cost');
INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (7,3,'Title');
INSERT INTO event_type_attributes (id,event_type_id,title) VALUES (8,3,'Genre');

INSERT INTO events (id,event_type_id,event_date,description) VALUES (1,1,TO_DATE('12-NOV-09 6:00 PM','DD-MON-RR HH:MI PM'),'USF back in the top 25 - can they stay there?');
INSERT INTO events (id,event_type_id,event_date,description) VALUES (2,2,TO_DATE('25-DEC-09 8:00 PM','DD-MON-RR HH:MI PM'),'He''s back for one day only');
INSERT INTO events (id,event_type_id,event_date,description) VALUES (3,1,TO_DATE('28-NOV-09 3:30 PM','DD-MON-RR HH:MI PM'),'A big rivalry this week!');
INSERT INTO events (id,event_type_id,event_date,description) VALUES (4,3,TO_DATE('09-DEC-09 11:00 PM','DD-MON-RR HH:MI PM'),'Very scary movie!');

INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (1,1,1,'USF');
INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (2,1,2,'Rutgers');
INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (3,1,3,'-10');
INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (4,3,1,'Florida Gators');
INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (5,3,2,'FSU');
INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (6,3,3,'???');
INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (7,2,4,'Elvis');
INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (8,2,5,'NY');
INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (9,2,6,'$500');
INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (10,4,7,'Paranormal Activity');
INSERT INTO event_details (id,event_id,event_type_attribute_id,event_type_attribute_value) VALUES (11,4,8,'Horror');

COMMIT;

Create a new report region on a page. Make sure to select the “generic columns” option below the source. Also, replace PXX_EVENT_TYPE_ID with the appropriate item name (created in the next step).

DECLARE

   l_retval        VARCHAR2(32767);
   l_crlf          CHAR(2) := CHR(13)||CHR(10);
   l_event_type_id EVENT_TYPES.ID%TYPE;
   
   CURSOR event_type_attributes_cur (
      p_event_type_id IN EVENT_TYPE_ATTRIBUTES.EVENT_TYPE_ID%TYPE
   )
   IS
      SELECT *
      FROM event_type_attributes
      WHERE event_type_id = p_event_type_id;
      
   l_event_type_attributes_rec EVENT_TYPE_ATTRIBUTES_CUR%ROWTYPE;

BEGIN

   l_event_type_id := nv('PXX_EVENT_TYPE_ID');

   l_retval := 'SELECT evt.event_date AS "Event Date", ' || l_crlf
      || ' evt.description AS "Description", ';
   
   OPEN event_type_attributes_cur(l_event_type_id);
   
   LOOP
      FETCH event_type_attributes_cur INTO l_event_type_attributes_rec;
      EXIT WHEN event_type_attributes_cur%NOTFOUND;
      
      l_retval := l_retval ||
            '   (' || l_crlf
         || '      SELECT event_type_attribute_value' || l_crlf
         || '      FROM event_details' || l_crlf
         || '      WHERE event_id = evt.id' || l_crlf
         || '         AND event_type_attribute_id = ' || l_event_type_attributes_rec.id || l_crlf
         || '   ) AS "' || l_event_type_attributes_rec.title || '",' || l_crlf;
   END LOOP;
   
   CLOSE event_type_attributes_cur;
   
   l_retval := RTRIM(l_retval, ',' || l_crlf);
   
   l_retval := l_retval || l_crlf
      || 'FROM events evt' || l_crlf
      || 'WHERE event_type_id = ' || l_event_type_id;

   RETURN l_retval;

END;

Add an item to select the event type. Choose an item type of Select List with Submit, set the name to PXX_EVENT_TYPE_ID (replace XX with page number). Note that the report will fail to render if the value of this item is not set. There are various ways to “handle” this - I used a page process to set the value to 1 if the item’s value was NULL. Use the following for the LOV:
```
SELECT title AS display,
   id AS return
FROM event_types
ORDER BY display
```
Add a branch back to the same page.

Webinar - jQuery Tabs in Oracle Application Express

2009-09-10T15:36:00.001-04:00

For those of you interested in getting started with jQuery and jQuery UI tabs, I’ll be presenting a webinar on Thursday, September 17th, from 12:00pm to 1:00pm EST. Here’s the description:

Many developers and organizations are looking for ways to take their Application Express (Apex) applications to the next level. On the client side, one of the best tools available for this purpose is jQuery and jQuery UI. In this webinar, we'll show you how to how "install" jQuery into your Apex application. Afterward, we'll demonstrate how to use a jQuery UI widget called Tabs to improve upon a rather cluttered application page.

This webinar is is free and is intended for anyone interested in making their Apex applications better. You can register by clicking here. Hope to see you there!

Forum Guru – I finally made it!

2009-09-10T10:53:00.001-04:00

I finally earned over 2500 points in the Apex forum today, elevating my status to “Guru” – nice ;) I hope to see you in there!

A New Chapter…

2009-09-08T08:41:00.001-04:00

Friday was my last day with the Outback Steakhouse (OSI). OSI was a great company to work for and I will always remember the many years I spent there and the wonderful people I worked with. I left OSI for about the only thing I ever would have - to further pursue my passion (some call it an obsession) with Apex and Oracle in general.

Today marks my first official day with SkillBuilders as a Senior Apex and Oracle Developer and Instructor. This is a very exciting time for me as teaching others what I love doing most is truly a dream job.

jQuery Tabs - Get'em while they're hot!

2009-07-12T18:46:00.001-04:00

So, you've mastered the art of j-quer-fu and now you're looking to take on its UI partner in crime - excellent! Tabs are a great place to start... They can help with real estate issues, reduce clutter, and create a better overall end user experience . Like all tools, however, tabs should not be used just because they're "cool" (even though they are). There are some general best practices that should be followed with their implementation. For a great primer on tabs, check out this article from Smashing Magazine:

http://www.smashingmagazine.com/2009/06/24/module-tabs-in-web-design-best-practices-and-solutions/

To lean about jQuery tabs in general have a look here:

http://www.jqueryui.com/demos/tabs/

Tabs look easy enough to implement but getting things just right in Apex can be a bit tricky the first time. In this post I'm going to explain how to take a busy Apex page with 5 regions and clean it up a bit with tabs. The page contains the following region types: 1 report, 1 calendar, 2 charts, and 1 help.

Here are the before and after demo links:

Before
After

Still interested??? Then let's get started!

Download jQuery UI and include it in your application or page. This is obviously a prerequisite. I posted about Installing jQuery a while back. The concept here is the same but a bit more involved as you need to include jQuery, jQuery UI, and the UI theme. If enough people ask, I'll update that old doc and include some more details ;)
Create a new region template. Many region templates display the region title in them. When we use tabs this may be unwanted as the tab text should suffice. At the same time, however, we will need the region attributes. The following can be used to create a generic region template that gives us what we need; I named it "Basic Div". You can modify the template as needed.
```
<div id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES# class="basic_div">
#BODY#
</div>
```
Prep the regions that you would like to be tabbed:
1. Change the templates to the template you created in step 2.
2. Make sure that all of your regions are in column 1.
3. Give each region a unique ID via the Static ID attribute.
4. Make sure they are sequential - regions that will not be tabbed should come before or after.
Enclose regions you want to group together under one tab with two new regions. By "enclose", I simply mean that you'll put one region before the set of regions that should be tabbed and then one after as well. Most of the time you'll want a one to one ratio of tabs to regions. However, sometimes you'll want to put more than one region under a tab. To do this you need to make sure that the regions are sequential and enclosed with some HTML that will group them. In my example, I grouped two chart regions under one tab by putting this HTML in the source of an HTML region (no template) right before the first chart:
```
<div id="charts_reg">
```
And this in another HTML region (no template) right after the second:
```
</div>
```
Enclose the regions you want to turn into tabbed regions with two new regions. This step is similar to the last but here the code we are adding will enclose all of our regions to be tabbed. Both of these regions should be HTML regions with no template assigned to them. The first of the two regions includes the mark up that jQuery UI will use to create the tabs. If you have regions that are conditionally displayed, you'll want to make this a PL/SQL region and generate you're HTML dynamically:
```
<div id="tabs_start_reg" style="display:none;">
   <ul>
      <li><a href="#employees_reg">Employees</a></li>
      <li><a href="#anniversaries_reg">Anniversaries</a></li>
      <li><a href="#charts_reg">Charts</a></li>
      <li><a href="#help_reg">Help</a></li>
   </ul>
```
And the second:
```
</div>
```

Add the following JavaScript to the HTML header of the page to bring everything to life:

<script type="text/javascript">
//<![CDATA[
   
   $(function(){  
      $tabs = $("#tabs_start_reg");
      $tabs.tabs();
      $tabs.parents('td').eq(0).removeAttr('width');
      $tabs.show();   
   });

//]]>
</script>

HTML image maps - an old trick with a new twist

2009-05-07T21:22:00.001-04:00

HTML image maps have been around for some time but not many people are familiar with them. In fact, until a client recently wanted to use an image as the basis of a dynamic help system, even I didn't have much use for them. But when you do need to work with them, it's actually a lot of fun!

So what's the twist? This article shows how one can use image maps without the anchor tag which is normally used, uses a little jQuery, and is written in the context of Apex - all at the same time. You can check out the demo here. Here are the basic steps to get you going...

Install jQuery.
Get the image. There are a bunch of programs out there, both free and paid, that will help you take screen shots if you don't already have the image you want to work with. For a free tool that comes with Vista, check out the snipping tool.
Create a map for the areas in the image. In an HTML image map, the sections one will be working with are called areas and each area element has a shape attribute. There are three basic shapes: rect (rectangle), circle, and polygon. Choose the appropriate shape for each each area of the image you want to work with - polygons can be used for oddly shaped areas. Then using a tool for working with images, like MS Paint or GIMP, get the coordinates of each. Each shape has its own system for mapping coordinates. We'll turn these coordinates into code in step 5.
Make the image available to your application. Either add the image to the shared components of your application or to the file system of your web server like the "i" directory.

Add the image and image map to your Apex application by adding an HTML region with the following code (modify for your needs):

Add the following JavaScript to the HTML Header of the page:

As you can see, my implementation is very basic - all the HTML is in the one region. However, there is no limit to how you can adapt the code for your own needs. Using a static region ID, you can work with multiple regions. Even Ajax is possible.

Happing coding!

2009/05/12 - Updated to better handle the hovering effect in IE.

Easy CSV Uploads? - Yes we can!

2009-02-03T22:38:00.003-05:00

A few months ago Chet Justice and I did an ApEx presentation at our Oracle users group. He put together a demo of how one could parse a comma separated block of text "in memory". He did this via a pipelined function, which was a really neat trick with which he was able to emulate functionality similar to the ApEx Load utility that allows you to upload CSVs after pasting the text into a textarea.

I recently had to work on a project that required this functionality, only it was based on a file upload rather than input from a textarea. I started by revisiting Chet's example, modified the code to make it more generic and flexible, and then packaged it away. I did have to replace the pipelined function with a temporary table to utilize the bulk load while creating the collection which really improves performance with large files. The result is an easy to use package that allows one to populate ApEx collections from either file uploads or pastes into textareas.

Checkout the demo...

WARNING: THIS IS NOT PRODUCTION CODE. This package is young and although I've not had any issues, I can not guarantee that it is bug free. Please let me know if you encounter any issues. I'll eventually do another post in the future when the package is more mature.

Here's how to get it working...

Compile this type:

  CREATE GLOBAL TEMPORARY TABLE "CSV_TEMP"
  ( "ATTR_01" VARCHAR2(4000 BYTE),
 "ATTR_02" VARCHAR2(4000 BYTE),
 "ATTR_03" VARCHAR2(4000 BYTE),
 "ATTR_04" VARCHAR2(4000 BYTE),
 "ATTR_05" VARCHAR2(4000 BYTE),
 "ATTR_06" VARCHAR2(4000 BYTE),
 "ATTR_07" VARCHAR2(4000 BYTE),
 "ATTR_08" VARCHAR2(4000 BYTE),
 "ATTR_09" VARCHAR2(4000 BYTE),
 "ATTR_10" VARCHAR2(4000 BYTE),
 "ATTR_11" VARCHAR2(4000 BYTE),
 "ATTR_12" VARCHAR2(4000 BYTE),
 "ATTR_13" VARCHAR2(4000 BYTE),
 "ATTR_14" VARCHAR2(4000 BYTE),
 "ATTR_15" VARCHAR2(4000 BYTE),
 "ATTR_16" VARCHAR2(4000 BYTE),
 "ATTR_17" VARCHAR2(4000 BYTE),
 "ATTR_18" VARCHAR2(4000 BYTE),
 "ATTR_19" VARCHAR2(4000 BYTE),
 "ATTR_20" VARCHAR2(4000 BYTE),
 "ATTR_21" VARCHAR2(4000 BYTE),
 "ATTR_22" VARCHAR2(4000 BYTE),
 "ATTR_23" VARCHAR2(4000 BYTE),
 "ATTR_24" VARCHAR2(4000 BYTE),
 "ATTR_25" VARCHAR2(4000 BYTE),
 "ATTR_26" VARCHAR2(4000 BYTE),
 "ATTR_27" VARCHAR2(4000 BYTE),
 "ATTR_28" VARCHAR2(4000 BYTE),
 "ATTR_29" VARCHAR2(4000 BYTE),
 "ATTR_30" VARCHAR2(4000 BYTE),
 "ATTR_31" VARCHAR2(4000 BYTE),
 "ATTR_32" VARCHAR2(4000 BYTE),
 "ATTR_33" VARCHAR2(4000 BYTE),
 "ATTR_34" VARCHAR2(4000 BYTE),
 "ATTR_35" VARCHAR2(4000 BYTE),
 "ATTR_36" VARCHAR2(4000 BYTE),
 "ATTR_37" VARCHAR2(4000 BYTE),
 "ATTR_38" VARCHAR2(4000 BYTE),
 "ATTR_39" VARCHAR2(4000 BYTE),
 "ATTR_40" VARCHAR2(4000 BYTE),
 "ATTR_41" VARCHAR2(4000 BYTE),
 "ATTR_42" VARCHAR2(4000 BYTE),
 "ATTR_43" VARCHAR2(4000 BYTE),
 "ATTR_44" VARCHAR2(4000 BYTE),
 "ATTR_45" VARCHAR2(4000 BYTE),
 "ATTR_46" VARCHAR2(4000 BYTE),
 "ATTR_47" VARCHAR2(4000 BYTE),
 "ATTR_48" VARCHAR2(4000 BYTE),
 "ATTR_49" VARCHAR2(4000 BYTE),
 "ATTR_50" VARCHAR2(4000 BYTE)
  ) ON COMMIT DELETE ROWS;

Compile this package spec:

create or replace
PACKAGE csv AS
/*****************************************************************************/
  /*
     Chet Justice, Daniel McGhan
    
     
        This procedure was designed to create and populate a collection based
        on a CSV CLOB. This was written specifically for use in ApEx and must
        be called from within a valid session.
     
    
     Modification History
     Date        By           Modification
     ----------- ------------ -----------------------------------------------
     
     27-JAN-2008 DMCGHAN      Initial Creation
          
  */
  PROCEDURE create_collection_from_clob (
     p_csv                IN CLOB
   , p_collection_name    IN VARCHAR2
   , p_header_included    IN CHAR := 'N'
   , p_delimiter          IN CHAR := ','
   , p_enclosed_by        IN CHAR := NULL
   , p_replace_collection IN CHAR := 'Y'
  );
 
/*****************************************************************************/

  /*
     Daniel McGhan
    
     
        This procedure was designed to create and populate a collection based
        on a CSV BLOB. This was written specifically for use in ApEx and must
        be called from within a valid session.
     
    
     Modification History
     Date        By           Modification
     ----------- ------------ -----------------------------------------------
     
     27-JAN-2008 DMCGHAN      Initial Creation
          
  */
  PROCEDURE create_collection_from_blob (
     p_csv                IN BLOB
   , p_collection_name    IN VARCHAR2
   , p_header_included    IN CHAR := 'N'
   , p_delimiter          IN CHAR := ','
   , p_enclosed_by        IN CHAR := NULL
   , p_replace_collection IN CHAR := 'Y'
  );
 
/*****************************************************************************/
END csv;

Compile this package body:

create or replace
PACKAGE BODY csv AS
/*****************************************************************************/

  PROCEDURE assert (
     p_condition IN BOOLEAN
   , p_err_code  IN PLS_INTEGER
   , p_err_msg   IN VARCHAR2
  )

  IS
 
  BEGIN
 
     IF (NOT p_condition OR p_condition IS NULL)
     THEN
        raise_application_error(NVL(p_err_code ,-20001), p_err_msg);
     END IF;
    
  END assert;
 
/*****************************************************************************/

  PROCEDURE create_collection_from_clob (
     p_csv                IN CLOB
   , p_collection_name    IN VARCHAR2
   , p_header_included    IN CHAR := 'N'
   , p_delimiter          IN CHAR := ','
   , p_enclosed_by        IN CHAR := NULL
   , p_replace_collection IN CHAR := 'Y'
  )

  AS
 
     TYPE csv_rt IS RECORD (
        attr_01 VARCHAR2(4000)
      , attr_02 VARCHAR2(4000)
      , attr_03 VARCHAR2(4000)
      , attr_04 VARCHAR2(4000)
      , attr_05 VARCHAR2(4000)
      , attr_06 VARCHAR2(4000)
      , attr_07 VARCHAR2(4000)
      , attr_08 VARCHAR2(4000)
      , attr_09 VARCHAR2(4000)
      , attr_10 VARCHAR2(4000)
      , attr_11 VARCHAR2(4000)
      , attr_12 VARCHAR2(4000)
      , attr_13 VARCHAR2(4000)
      , attr_14 VARCHAR2(4000)
      , attr_15 VARCHAR2(4000)
      , attr_16 VARCHAR2(4000)
      , attr_17 VARCHAR2(4000)
      , attr_18 VARCHAR2(4000)
      , attr_19 VARCHAR2(4000)
      , attr_20 VARCHAR2(4000)
      , attr_21 VARCHAR2(4000)
      , attr_22 VARCHAR2(4000)
      , attr_23 VARCHAR2(4000)
      , attr_24 VARCHAR2(4000)
      , attr_25 VARCHAR2(4000)
      , attr_26 VARCHAR2(4000)
      , attr_27 VARCHAR2(4000)
      , attr_28 VARCHAR2(4000)
      , attr_29 VARCHAR2(4000)
      , attr_30 VARCHAR2(4000)
      , attr_31 VARCHAR2(4000)
      , attr_32 VARCHAR2(4000)
      , attr_33 VARCHAR2(4000)
      , attr_34 VARCHAR2(4000)
      , attr_35 VARCHAR2(4000)
      , attr_36 VARCHAR2(4000)
      , attr_37 VARCHAR2(4000)
      , attr_38 VARCHAR2(4000)
      , attr_39 VARCHAR2(4000)
      , attr_40 VARCHAR2(4000)
      , attr_41 VARCHAR2(4000)
      , attr_42 VARCHAR2(4000)
      , attr_43 VARCHAR2(4000)
      , attr_44 VARCHAR2(4000)
      , attr_45 VARCHAR2(4000)
      , attr_46 VARCHAR2(4000)
      , attr_47 VARCHAR2(4000)
      , attr_48 VARCHAR2(4000)
      , attr_49 VARCHAR2(4000)
      , attr_50 VARCHAR2(4000)
     );
 
     l_csv              CLOB := 'X';
     l_csv_length       PLS_INTEGER;
     l_newline_position PLS_INTEGER := 1;
     l_record_length    PLS_INTEGER;
     l_record_count     PLS_INTEGER := 0;
     l_record_text      VARCHAR2(32767);
     l_column_count     PLS_INTEGER := 0;
     l_column_text      VARCHAR2(4000);
     l_record           CSV_RT;
     l_delimiter        CHAR(1);
     l_header_included  CHAR(1);
     l_stage            VARCHAR2(32767);
     
     PROCEDURE populate_attr (
        p_attr_num IN PLS_INTEGER
      , p_attr_val IN VARCHAR2
     )
     IS
     BEGIN
        CASE p_attr_num
           WHEN 1 THEN l_record.attr_01 := p_attr_val;
           WHEN 2 THEN l_record.attr_02 := p_attr_val;
           WHEN 3 THEN l_record.attr_03 := p_attr_val;
           WHEN 4 THEN l_record.attr_04 := p_attr_val;
           WHEN 5 THEN l_record.attr_05 := p_attr_val;
           WHEN 6 THEN l_record.attr_06 := p_attr_val;
           WHEN 7 THEN l_record.attr_07 := p_attr_val;
           WHEN 8 THEN l_record.attr_08 := p_attr_val;
           WHEN 9 THEN l_record.attr_09 := p_attr_val;
           WHEN 10 THEN l_record.attr_10 := p_attr_val;
           WHEN 11 THEN l_record.attr_11 := p_attr_val;
           WHEN 12 THEN l_record.attr_12 := p_attr_val;
           WHEN 13 THEN l_record.attr_13 := p_attr_val;
           WHEN 14 THEN l_record.attr_14 := p_attr_val;
           WHEN 15 THEN l_record.attr_15 := p_attr_val;
           WHEN 16 THEN l_record.attr_16 := p_attr_val;
           WHEN 17 THEN l_record.attr_17 := p_attr_val;
           WHEN 18 THEN l_record.attr_18 := p_attr_val;
           WHEN 19 THEN l_record.attr_19 := p_attr_val;
           WHEN 20 THEN l_record.attr_20 := p_attr_val;
           WHEN 21 THEN l_record.attr_21 := p_attr_val;
           WHEN 22 THEN l_record.attr_22 := p_attr_val;
           WHEN 23 THEN l_record.attr_23 := p_attr_val;
           WHEN 24 THEN l_record.attr_24 := p_attr_val;
           WHEN 25 THEN l_record.attr_25 := p_attr_val;
           WHEN 26 THEN l_record.attr_26 := p_attr_val;
           WHEN 27 THEN l_record.attr_27 := p_attr_val;
           WHEN 28 THEN l_record.attr_28 := p_attr_val;
           WHEN 29 THEN l_record.attr_29 := p_attr_val;
           WHEN 30 THEN l_record.attr_30 := p_attr_val;
           WHEN 31 THEN l_record.attr_31 := p_attr_val;
           WHEN 32 THEN l_record.attr_32 := p_attr_val;
           WHEN 33 THEN l_record.attr_33 := p_attr_val;
           WHEN 34 THEN l_record.attr_34 := p_attr_val;
           WHEN 35 THEN l_record.attr_35 := p_attr_val;
           WHEN 36 THEN l_record.attr_36 := p_attr_val;
           WHEN 37 THEN l_record.attr_37 := p_attr_val;
           WHEN 38 THEN l_record.attr_38 := p_attr_val;
           WHEN 39 THEN l_record.attr_39 := p_attr_val;
           WHEN 40 THEN l_record.attr_40 := p_attr_val;
           WHEN 41 THEN l_record.attr_41 := p_attr_val;
           WHEN 42 THEN l_record.attr_42 := p_attr_val;
           WHEN 43 THEN l_record.attr_43 := p_attr_val;
           WHEN 44 THEN l_record.attr_44 := p_attr_val;
           WHEN 45 THEN l_record.attr_45 := p_attr_val;
           WHEN 46 THEN l_record.attr_46 := p_attr_val;
           WHEN 47 THEN l_record.attr_47 := p_attr_val;
           WHEN 48 THEN l_record.attr_48 := p_attr_val;
           WHEN 49 THEN l_record.attr_49 := p_attr_val;
           WHEN 50 THEN l_record.attr_50 := p_attr_val;
        END CASE;
     END populate_attr;

     PROCEDURE queue_record
     IS
     BEGIN
        l_record_count := l_record_count + 1;
        l_record_text := dbms_lob.substr(
           l_csv
         , dbms_lob.instr(l_csv, CHR(10), l_newline_position) - l_newline_position
         , l_newline_position
        );
        l_record_length := LENGTH(l_record_text);
        l_newline_position := l_newline_position + l_record_length + 1;
     END queue_record;
    
     PROCEDURE process_non_enclosed_record
     IS
        l_char_text CHAR(1);
     BEGIN
        FOR i IN 1 .. l_record_length
        LOOP
           l_char_text := SUBSTR(l_record_text, i, 1);
           IF l_char_text != l_delimiter OR l_record_length = i
           THEN
              l_column_text := l_column_text || l_char_text;
             
              IF l_record_length = i
              THEN
                 populate_attr(l_column_count + 1, l_column_text);
                 INSERT INTO csv_temp VALUES l_record;
                
                 l_column_count := 0;
                 l_column_text := NULL;
                 EXIT;
              END IF;
           ELSE
              l_column_count := l_column_count + 1;
              populate_attr(l_column_count, l_column_text);
             
              l_column_text := NULL;
           END IF;
        END LOOP;
     END process_non_enclosed_record;
    
     PROCEDURE process_enclosed_record
     IS
        l_char_text      CHAR(1);
        l_last_char_text CHAR(1);
        l_next_char_text CHAR(1);
        l_encloser_on    BOOLEAN := FALSE;
     BEGIN
        FOR i IN 1 .. l_record_length
        LOOP
           l_last_char_text := l_char_text;
           l_char_text := SUBSTR(l_record_text, i, 1);
           l_next_char_text := SUBSTR(l_record_text, i + 1, 1);
           l_encloser_on := (
              (l_char_text = p_enclosed_by AND (l_last_char_text = l_delimiter OR i = 1))
              OR (l_char_text != p_enclosed_by AND l_encloser_on)
           );
          
           IF (l_char_text != l_delimiter AND l_char_text != p_enclosed_by)
              OR (l_char_text = l_delimiter AND l_encloser_on)
              OR (l_char_text = p_enclosed_by AND l_next_char_text != l_delimiter AND l_last_char_text != l_delimiter AND i != 1)
              OR l_record_length = i
           THEN
              l_column_text := l_column_text || l_char_text;
             
              IF l_record_length = i
              THEN
                 populate_attr(l_column_count + 1, l_column_text);
                 INSERT INTO csv_temp VALUES l_record;
                
                 l_column_count := 0;
                 l_column_text := NULL;
                 EXIT;
              END IF;
           ELSIF NOT (
                 (l_char_text = p_enclosed_by AND l_next_char_text = l_delimiter)
              OR (l_char_text = p_enclosed_by AND l_last_char_text = l_delimiter)
              OR (i = 1 AND l_char_text != l_delimiter)
           )
           THEN
              l_column_count := l_column_count + 1;
              populate_attr(l_column_count, l_column_text);
             
              l_column_text := NULL;
           END IF;
        END LOOP;
     END;

  BEGIN
 
     l_stage := 'Checking pre-conditions';
     assert(p_csv IS NOT NULL, -20001, 'p_csv must be NOT NULL');
     assert(p_collection_name IS NOT NULL, -20002, 'p_collection_name must be NOT NULL');
     assert(p_header_included IS NOT NULL, -20003, 'p_header_included must be NOT NULL');
     assert(p_header_included IN ('Y','N'), -20004, 'p_header_included must be ''Y'' or ''N''');
     assert(p_delimiter IS NOT NULL, -20005, 'p_delimiter must be NOT NULL');
     assert(p_replace_collection IS NOT NULL, -20006, 'p_replace_collection must be NOT NULL');
     assert(p_replace_collection IN ('Y','N'), -20007, 'p_replace_collection must be ''Y'' or ''N''');
 
     l_stage := 'Setting defaults';
     l_delimiter :=
        CASE
           WHEN p_delimiter IS NULL THEN ','
           ELSE p_delimiter
        END;
    
     l_stage := 'Copying passed in CLOB to local CLOB';
     dbms_lob.copy(l_csv, p_csv, dbms_lob.getlength(p_csv));
    
     l_stage := 'Checking to see if passed in CLOB ends with a CR';
     IF dbms_lob.instr(p_csv, CHR(10), dbms_lob.getlength(p_csv) - 1) = 0
     THEN
        l_stage := 'Appending CR to local CLOB';
        dbms_lob.append(l_csv, CHR(10));
     END IF;
          
     l_stage := 'Getting the length of the local CLOB';
     l_csv_length := dbms_lob.getlength(l_csv);
    
     l_stage := 'Queuing the first record';
     queue_record;
    
     l_stage := 'Checking to see if first record should be part of data';
     IF p_header_included = 'N'
     THEN
        l_stage := 'Processing first record';
        IF p_enclosed_by IS NULL
        THEN
           process_non_enclosed_record;
        ELSE
           process_enclosed_record;
        END IF;
     END IF;
    
     l_stage := 'Processing the rest of the records';
     IF p_enclosed_by IS NULL
     THEN
        WHILE l_newline_position < l_csv_length
        LOOP
           queue_record;
           process_non_enclosed_record;
        END LOOP;
     ELSE
        WHILE l_newline_position < l_csv_length
        LOOP
           queue_record;
           process_enclosed_record;
        END LOOP;
     END IF;

     IF p_replace_collection = 'Y' AND apex_collection.collection_exists(p_collection_name)
     THEN
        l_stage := 'Removing existing collection';
        apex_collection.delete_collection(p_collection_name);
     END IF;
    
     l_stage := 'Creating collection from CSV_TEMP table';
     apex_collection.create_collection_from_query_b(
        p_collection_name => p_collection_name
      , p_query           => 'SELECT * FROM csv_temp'
     );

  END create_collection_from_clob;

/*****************************************************************************/

  PROCEDURE create_collection_from_blob (
     p_csv                IN BLOB
   , p_collection_name    IN VARCHAR2
   , p_header_included    IN CHAR := 'N'
   , p_delimiter          IN CHAR := ','
   , p_enclosed_by        IN CHAR := NULL
   , p_replace_collection IN CHAR := 'Y'
  )

  AS

     l_csv_clob     CLOB := 'X';
     l_dest_offset  INTEGER := 1;
     l_src_offset   INTEGER := 1;
     l_lang_context INTEGER := dbms_lob.default_lang_ctx;
     l_warning      INTEGER;
     l_stage        VARCHAR2(32767);

  BEGIN
 
     l_stage := 'Checking pre-conditions';
     assert(p_csv IS NOT NULL, -20001, 'p_csv must be NOT NULL');
     assert(p_collection_name IS NOT NULL, -20002, 'p_collection_name must be NOT NULL');
     assert(p_header_included IS NOT NULL, -20003, 'p_header_included must be NOT NULL');
     assert(p_header_included IN ('Y','N'), -20004, 'p_header_included must be ''Y'' or ''N''');
     assert(p_delimiter IS NOT NULL, -20005, 'p_delimiter must be NOT NULL');
     assert(p_replace_collection IS NOT NULL, -20006, 'p_replace_collection must be NOT NULL');
     assert(p_replace_collection IN ('Y','N'), -20007, 'p_replace_collection must be ''Y'' or ''N''');

     l_stage := 'Converting BLOB to CLOB';
     dbms_lob.converttoclob (
        dest_lob     => l_csv_clob
      , src_blob     => p_csv
      , amount       => dbms_lob.lobmaxsize
      , dest_offset  => l_dest_offset
      , src_offset   => l_src_offset
      , blob_csid    => dbms_lob.default_csid
      , lang_context => l_lang_context
      , warning      => l_warning
     );

     l_stage := 'Calling create_collection_from_clob to process CLOB';
     create_collection_from_clob(
        p_csv                => l_csv_clob
      , p_collection_name    => p_collection_name
      , p_header_included    => p_header_included
      , p_delimiter          => p_delimiter
      , p_enclosed_by        => p_enclosed_by
      , p_replace_collection => p_replace_collection
     );

  END create_collection_from_blob;

/*****************************************************************************/
END csv;

Create an application page with a File Browse item and adapt the following code to meet your needs.

DECLARE

  l_blob BLOB;

  PROCEDURE cleanup
  IS
  BEGIN
     DELETE FROM apex_application_files
     WHERE name = :PXX_FILE_LOCATION;
  END cleanup;

BEGIN

  SELECT blob_content
  INTO l_blob
  FROM apex_application_files
  WHERE name = :PXX_FILE_LOCATION;
 
  csv.create_collection_from_blob(l_blob, 'CSV_UPLOAD', 'Y');

  cleanup;

EXCEPTION

  WHEN OTHERS
  THEN
     cleanup;
    
     RAISE;

END;

That font is too small!

2009-01-05T20:58:00.001-05:00

I think what God took from the hair on my head he gave to the vision of my eyes. As a consequence I like smaller fonts that display more data on the screen... But end users can hate this! The following solution was made to help the poster of this form thread and the many end users out there that still have hair ;) Here's a little demo...

Install jQuery.
Create an application item called FONT_SIZE_ADJ.
Create an On Demand process called RETURN_NOTHING and enter the following for the body (yes, it's really one line that says 'null;'):
```
NULL;
```
Modify the page template to include the following (I put mine right before the 'td' that held the navigation bar):
```
<td style="white-space:nowrap">
   (-) 
   Save
    / 
   Reset
    (+)
</td>
```

Paste the following in the HTML Header of the page:

The original thread had to do with Interactive Reports so that's what this code works with but it should be easy enough to modify the code to suit your needs. You could even save the user's preference and load it when they login to the application. Also, keep page 0 in mind if you want to use this throughout the entire application.

Update 5-JAN-2009: Updated code to handle the interactive report refreshing better. Still working on something to fix IE.

New Years Resolutions

2009-01-04T22:15:00.001-05:00

Many people do not like making New Years resolutions. I, however, can rarely resist the opportunity to try to better myself - even if the opportunity is really only motivational. This year I have 3 "professional" resolutions and at the behest of Chet Justice, I've decided to share them...

Oracle Database 11g Administrator Certified Associate - I've been PL/SQL certified for a while and always wanted to learn more on the DBA side of Oracle. Certification is a great way to get exposed to a lot of options and features but it takes hard work and experience to get good with them. I'm really hoping that Oracle will offer an ApEx certification eventually. Until then DBA OCA, here I come.
A New Open Source Project (plRecur) - My first open source project, tapiGen, was a bit of a failure. Not that it was particularly bad code or didn't do what it was supposed to... I believe it failed because it required such a big change in the way one codes it was perhaps a little impractical. I've learned a bit from that and have something else in mind this time around. Actually, I've already begun coding ;) plRecur will be a PL/SQL package based API for working with things that recur. Typically this is an event of some kind.

Of course working with recurrence is nothing new. My early attempts with this functionality are embarrassing to look back on. They where highly proprietary and not very flexible. The idea with plRecur is two fold: First to create a PL/SQL package based backend that makes working with recurrence easy and second to create a basic ApEx page region based frontend that can be used to interact with the backend.

The calendaring syntax will be based on DBMS_SCHEDULER's calendaring syntax so experience with that will help. More details on this project will follow soon but here's a brief teaser... The calendar feeds off a call to pl_recur.dates_pipe which is a pipelined function.
Become More Fluent In Spanish - This obviously has nothing to do with Oracle or ApEx, but it's something I need to keep working on!

Wait, Wait! Don't forget to save...

2008-12-30T22:54:00.001-05:00

Ever want to display a warning message to your users if they try to leave a page without saving their changes? Until I took a look at this forum post, I'd never really thought much about how to implement this functionality. But with jQuery and Jon St. John's code to help me get started, it wasn't too hard.

The trick is creating reusable/generic code and the following is a start toward that end. Please let me know of any issues/suggestions so that I can make updates. Also, keep in mind that this should not be implemented on VERY page - just the important ones! ;)

I "retrofitted" this previous example to demonstrate. Try modifying a value in the form and then use one of the links on the left to leave the page - even try closing the page. Notice the buttons still work as they should.

Here's how to get it working...

Install jQuery in ApEx.

Place the following code in the HTML Header of the page.

Popup In Report

2008-12-15T22:17:00.001-05:00

While recently trying to help someone in the ApEx forum, I decided a blog post was in order. If you have a report with limited real estate but need to allow the user to enter comments (requiring a textarea), what is the best strategy to employ? The person that posted the thread wanted to have something in the report that would allow the user to click it triggering a popup window to open allowing the user to edit the comments.

While that's possible, it doesn't involve jQuery which just isn't cool. So I thought of a way I could do it with jQuery ;) Click here to view the example. One of my favorite jQuery plugins is Impromptu. Impromptu allows you to create custom popups with those slick background fades. You can learn more about Impromptu here:

http://trentrichardson.com/Impromptu/index.php

I choose to show how this works with a canned tabular form as I figure that's what most people use. I almost always use custom tabular forms using the APEX_ITEM package but those of you doing that should be able to adjust this procedure to work in your implementation. Also it's important to note that a few things are lacking... The text displayed should indicate if a comment exists or not, the mouseover could indicate that a click would do something, and code could be added to remind the user to save changes if they made some and tried to navigate away from the page without doing so.

Here's how to get it working...

Install jQuery in ApEx.
Download impromptu from the link above. Create a new folder called impromptu in your i/jquery directory (created in step 1) and save impromptu there.
Create a tabular form as you normally would and include the column you would like to hold the text for the popup. FYI, I used the employees table and added a DETAILS column to hold the extra data.
Go to edit the tabular form and change the column that will be holding the data for the popup (details for me) from a text input to a hidden item. It's important to note the order/rows in which the both the ID and the details items are. Just count down starting from the top, in my case they are numbers 2 and 10 respectively. You'll see these referred to later as "f02" and "f07" in jQuery filters.
Add a derived column to the tabular form and put the following code in the HTML Expression:
```
Add/Edit Details
```

When the tabular form renders you shouldn't see the ID or the details columns. But using a tool like firebug, you'll see that ApEx placed them in the last td element in the row along with span we created in the derived column. In fact, there are a few different elements there, all siblings of one another. jQuery has a great way to find and filter siblings. Combined with Impromptu, the following code is all that's necessary to get everything working. Place it in the page's HTML Header after you've replaced &JAVASCRIPT_DIR. with the appropriate path.

A note on Carl

2008-10-28T02:50:00.002-04:00

Carl was a great guy; he liked basketball, Japanese monster movies, and good code. Perhaps the “good code” doesn’t resonate with everyone but it does with programmers and he wrote lots of it. My first interactions with him were like those of so many others – he was helping me in the forums when I was first learning ApEx.

Carl was always encouraging and quickly became a role model to me. After a few years had passed and I learned I’d be attending ODTUG with him this year, I jumped at the opportunity to be his ambassador – just to make sure I’d met him. I’m really glad to have had that opportunity.

As young as Carl was when he died, I feel it’s safe to say that he left quite a mark on this world. It’s people like him, those that selflessly enrich the lives of others that always do. He will be sorely missed but not forgotten. Again, my condolences to his friends and family.

A great man, a great loss

2008-10-27T23:09:00.002-04:00

I just heard the news about Carl Backstrom... His death is a great loss to all that knew him. My condolences to his family.

ApEx @ Upcoming SOUG Meeting

2008-09-13T11:24:00.001-04:00

I just wanted to let everyone know that I, along with Chet Justice, will be presenting at the upcoming Suncoast Oracle Users Group meeting. This months meeting will be a week earlier than normal, Thursday the 18th, due to OOW.

Topics that I'll be covering include Interactive Reports, uploading/downloading files, and using jQuery. Chet plans to cover uploading a CSV in an application and tabular form validation. We'll make sure to have the demo application available after the session.

The meeting goes from 6:30 to 8:00 but there will be food and an opportunity to network from 6 to 6:30. If you are in the area stop on by - even if you're just hungry ;)

tapiGen Update Released

2008-08-29T00:12:00.001-04:00

I've been extremely busy the last couple months. So much so that I have not had the time to add some of the functionality that I know tapiGen needs, such as the ability to process associative arrays. I did, however, add one process that has been saving me all kinds of time: TAPI_GEN.PUT_APEX_FORM_CODE. It is for that reason that I'm releasing this update.

You pass in a table name and an ApEx page id and the procedure outputs four blocks of code which can be used to create page level processes built on tapiGen:

A process to fetch a record and populate page items.
A process to create.
A process to modify.
A process to delete.

The output is created via DBMS_OUTPUT.PUT_LINE so make sure you have output enabled before executing this procedure. The resulting code can be used as is or as a starting point of sorts from which you can make any customizations needed.

Eventually I'd like to be able to create forms declaratively as you're able to do now using ApEx wizards. However, an API to do so has not yet been released so this is the best I can do for now. I hope to get another release out soon with more functionality. If you have any suggestions/requests add them at sourceforge.net or send me an email.

Custom authentication via DB credentials

2008-08-03T15:38:00.003-04:00

We all know that ApEx offers an authentication scheme that will validate a user against a database account. I don't know how it works exactly - it's a black box. But I became interested while trying to help someone in this forum post. In the post, a developer wanted to create a custom authentication scheme that could do both LDAP authentication and DB authentication based on what the end user selected at the login page.

The problem is that to use ApEx's DB authentication scheme it must be the active scheme so you can't do both. Furthermore, Oracle does not offer a means to test DB credentials programmatically. However, with a little searching I found a technique that allows this to be done. With a little modification, I've made it available below. I don't know if this is the best way to achieve this so if anyone knows of a better method, feel free to let me know and I'll update the function.

Using this function, a custom authentication function can be created that uses a little boolean logic (checking the value of an item on the login page) to decide how to authenticate the user - LDAP or DB.

08-AUG-2008 - Updated to use DBMS_ASSERT package as suggested by Patrick Wolf.
10-AUG-2008 - Simplified code a little.

create or replace
FUNCTION is_valid_db_user (
   p_username     IN VARCHAR2
 , p_password     IN VARCHAR2
 , p_host_service IN VARCHAR2
)

   RETURN BOOLEAN
   
IS

   l_db_link_exists  BOOLEAN;
   e_bad_username_pw EXCEPTION;
   PRAGMA exception_init(e_bad_username_pw,-1017);
   
   CURSOR db_link_exists_cur
   IS
      SELECT 'Y'
      FROM user_db_links
      WHERE db_link = 'PASSWORD_TEST';
      
   db_link_exists_rec DB_LINK_EXISTS_CUR%ROWTYPE;
    
   FUNCTION is_valid_username_pw
   
      RETURN BOOLEAN
      
   IS
   
      l_test_result CHAR(1) := 'N';
      
   BEGIN
   
      EXECUTE IMMEDIATE 'SELECT ''Y'' FROM DUAL@password_test' 
      INTO l_test_result;
      
      COMMIT;
      
      EXECUTE IMMEDIATE 'ALTER SESSION CLOSE DATABASE LINK password_test';
      
      RETURN l_test_result = 'Y';
      
   EXCEPTION
   
      WHEN e_bad_username_pw
      THEN
         RETURN FALSE;
         
   END is_valid_username_pw;
   
BEGIN
   
   OPEN db_link_exists_cur;
   FETCH db_link_exists_cur INTO db_link_exists_rec;
   l_db_link_exists := db_link_exists_cur%FOUND;
   CLOSE db_link_exists_cur;

   IF l_db_link_exists
   THEN
      EXECUTE IMMEDIATE 'DROP DATABASE LINK password_test';
   END IF;
   
   EXECUTE IMMEDIATE 'CREATE DATABASE LINK password_test '
      || 'CONNECT TO ' || sys.dbms_assert.enquote_name(dbms_assert.simple_sql_name(p_username)) || ' '
      || 'IDENTIFIED BY ' || sys.dbms_assert.simple_sql_name(p_password) || ' '
      || 'USING ' || sys.dbms_assert.enquote_literal(REPLACE(p_host_service,'''',''''''));
   
   RETURN is_valid_username_pw;
            
END is_valid_db_user;

Deploying Updates to Production Applications

2008-07-21T20:51:00.001-04:00

While at ODTUG in June, I had the pleasure of attending John Scott's ApEx Best Practices session. For me that was a real treat as Steve Feuerstein's PL/SQL best practices is one of my all time favorites. One of things I remember most about the session, and I can only use my memory as I can't seem to find the presentation from the conference, was a tip on deploying updates to applications in production while minimizing down time - down to a few minutes.

The technique was simple enough and I'm a big fan of simple during deployments. The prerequisite was use of Apache over the EPG (Embedded PL/SQL Gateway) although there are some ways around that (more later). The steps involved went something like this (pulling from memory):

Deploy the updated application into production while leaving the current application in place. Yes, this means it will get a new APP_ID and the APP_ALIAS if not changed will get the APP_ID appended to it.
Get the updated application working. Easy enough right! Get it up while the other application breaths its last breaths.
When the updated application is up and running 100%, use an apache rewrite rule to redirect traffic from the old application to the new one.
Then, when the time is right, remove the old application and modify the updated application's APP_ALIAS to what it should be.

It is step 3 that requires Apache. However, if your users access your applications via a centralized link (on a portal) and that link uses the APP_ALIAS and not the APP_ID (as all your links do, right?), then users will be redirected after you've completed step 4 anyway. However, you might want to delay removing the old application and instead just alter its APP_ALIAS (so that you can use it in the updated app) and then set its status to "Unavailable" with a message that includes the correct link. Providing the correct link will help those that saved the link in their browser after it resolved to the APP_ID.

Now, why did I just reiterate all of that? Well for one, I think it's a great technique that may benefit a lot of people. But I remember thinking to myself and even asking the question, "What will happen to Interactive Reports saved in the application being replaced?" I thought I had issues with that and made a mental note to follow up on it later.

The fact is that if your users have saved Interactive Reports in the old application, then you'll need to add one more step to the migration process. Thanks to Brian, I now know the table holding the Interactive Report data is named WWV_FLOW_WORKSHEET_RPTS (in the FLOWS schema). In this table there are two columns that will need to be updated: WORKSHEET_ID and FLOW_ID. I created the following procedure to help with this process (note that if an interactive report was modified as part of the deployment or if the interactive report was moved to another page, a more manual approach will be required. Also, UPDATE will need to be granted on WWV_FLOW_WORKSHEET_RPTS to the executing schema.

create or replace
PROCEDURE transfer_apex_rpts (
   p_old_app_id IN NUMBER
 , p_new_app_id IN NUMBER
)

IS

   l_new_worksheet_id NUMBER;

   FUNCTION worksheet_id (
      p_flow_id IN NUMBER
    , p_page_id IN NUMBER
   )
   
     RETURN NUMBER
     
   IS 
   
      l_worksheet_id NUMBER;
   
   BEGIN
      
      SELECT id
      INTO l_worksheet_id
      FROM flows_030100.wwv_flow_worksheets
      WHERE flow_id = worksheet_id.p_flow_id
         AND page_id = worksheet_id.p_page_id;
         
      RETURN l_worksheet_id;
      
   END;

BEGIN

   IF p_old_app_id IS NULL
   THEN
      raise_application_error('-20001', 'p_old_app_id must be NOT NULL');
   END IF;

   IF p_new_app_id IS NULL
   THEN
      raise_application_error('-20002', 'p_new_app_id must be NOT NULL');
   END IF;
   
   FOR x IN (
      SELECT DISTINCT worksheet_id, page_id
      FROM flows_030100.wwv_flow_worksheet_rpts
      WHERE flow_id = p_old_app_id
   )
   LOOP
      l_new_worksheet_id := worksheet_id(p_new_app_id, x.page_id);
      
      UPDATE flows_030100.wwv_flow_worksheet_rpts
      SET worksheet_id = l_new_worksheet_id
      WHERE worksheet_id = x.worksheet_id;
   END LOOP;
   
   UPDATE flows_030100.wwv_flow_worksheet_rpts
   SET flow_id = p_new_app_id
   WHERE flow_id = p_old_app_id;

   COMMIT;
END;

If nothing else this procedure can provide some insight as to how this can be approached. As always, let me know if you have any questions. This is new to me, so I'm sure the procedure will evolve with time. As it does, I'll update it here.

tapiGen demo app

2008-07-12T16:16:00.001-04:00

I was recently asked if I could put together a demo application to show how the use of tapiGen makes it easier to build some simple pages in ApEx. I thought about it for a while but ultimately decided it was not a very easy task, in fact, it's currently more difficult! No one can really argue against how simple the dev team at Oracle has made building simple forms. The trade off, however, is control.

When I first created tapiGen, I was hoping that Oracle had an API through which I could automate the creation of pages and content, much like they've done when you use the wizards to create pages and regions. The idea was to provide an alternate means thru which a form could be created that would use tapiGen for processing over the Automated Row Fetch and Automatic Row Processing. However, no such API exists and it's unclear if one ever will. (the most I can do in tapiGen is to generate the code which can then be copied and pasted into ApEx processes - I'll do this in version .3).

There are, however, some compelling reasons for using tapiGen in ApEx:

Automatic Row processes are only useful for simple forms that feed off of one table. If you want to work with multiple tales on the same page you'll need to create code to perform the fetches and DML. tapiGen will help keep this "custom" code to a minimum while providing other functionality.
Using the Automatic Row processes requires up to 4 additional page items for the audit columns: PXX_CREATED_BY, PXX_CREATED_ON, PXX_MODIFIED_BY, PXX_MODIFIED_ON. These page items then need to be properly configured so that 1) they are not shown/made editable to the end user and 2) their values are properly set on submittal to update the columns as needed. In JDeveloper this can be automated, so I figured why not in ApEx. With tapiGen audit columns are handled for you so the items are not necessary.
There is only one "success message" displayed for Automatic Row Processing (DML), which can handle three types of operations, such as "Operation Successful". I wanted to display more contextually accurate messages such as "Contact Created", "Contact Modified", and "Contact Deleted". This requires three separate page processes, one for each DML operation, but using tapiGen keeps the amount of code required to a minimum.
Errors are not "handled" using Automatic Row processes, only displayed to the end user. If a row fetch fails I want to know about it. tapiGen provides a consistent method thru which errors are written to a table called ERRORS. The row inserted includes a lot of information such as the application alias, application id, page id, app user, and time during which the error occurred along with other information. This can be very useful when trying to figure out what went wrong. If you want to take it to the next level, a trigger can be placed on the errors table with the necessary code to email the group/person responsible for maintaining the application that includes all of the aforementioned information. Proactive maintenance, oh yeah! ;)

The truth is that table APIs are not ApEx specific. Many corporations that have never heard of ApEx use TAPIs to help ensure that PL/SQL code is written in a consistent and efficient manner that makes maintenance much easier.

Steve Feuerstein first got me into TAPIs in his book PL/SQL Best Practices. In fact he offers a TAPI generator as a part of the Quest CodeGen Utility that I'm still working to catch up to in some ways. But I wanted a TAPI generator that made working with ApEx easier, included some additional functionality for working in web based environments, and didn't require a JRE to work (so it could be used in XE). Also, Steve is not currently/actively maintaining the generator, so new DB features such as the function result cache in 11g will not be taken advantage of, at least not until he decides to do it.

tapiGen is open source and I don't plan to stop updating it (of course I never planed on making it either). The fact is that I use it all the time so I have to keep making it better. For example, the next version will make DML with collections much easier - something I'm really looking forward to! I would love to have others helping to maintain the code although I don't know if that'll ever happen. I'd also love any feed back as to how it could be made better. Hopefully by the time version 1 (production/stable) is released the project will help meet a lot of peoples needs.

Having said all this, I feel like I'm coping out... I'll tell you what, when version .3 is released, provided there's some interest, I'll put out an example application that demonstrates the use of tapiGen in ApEx.

tapiGen Update Released

2008-07-05T21:38:00.001-04:00

I just released a new version of tapiGen available here:

http://sourceforge.net/projects/tapigen

There were a few important changes along with some few trivial ones. Among the more important are:

Added support for function result cache to the RT and RT_FOR_WEB_UPDATE functions. Applications already using these functions in 11g will see a performance benefit although nothing changes for 10g.
Global variables added for audit column names. These variables allow TE packages to be created for tables that do not use the default names: CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE.
Requirement for a table to have primary key column named "ID" has been removed. The column will be looked up and the TE package created accordingly.
MD5 function created in TE packages now uses DBMS_CRYPTO.HASH over DBMS_OBFUSCATION_TOOLKIT.MD5. This allows for hashes of rows that contain over 32767 bytes of data but requires the execute grant on DBMS_OBFUSCATION. This function now takes all columns into account and can handle both CLOBs and BLOBs.
Added DBMS_OUTPUT calls to provide feedback on package creation.

I've already got a few things in mind for version .3:

Overloading the INS, UPD, AND DEL functions to accept associate arrays. This alone will be a great time saver as it can be quite tedious in 10g and before the declare the arrays for each column, set their values, and then pass them in. There's also a performance impact. This restriction has been removed in 11g and in the next version of tapiGen conditional compilation will be used to determine the best method of performing these FOR ALLs.
Use of the supplied ERR package and the ERRORS table will become optional.

If anyone has any questions please feel free to ask. On another note, I'm beginning another open source project called plPDI. This will be a simple package designed to abstract out the use of the vCalendar/iCalendar and vCard standards. More details on that later!

iCalendar event via email

2008-06-20T15:55:00.001-04:00

I recently attended a session by Scott Spendolini at the ODTUG conference. In the session he spoke about ApEx "integration" with Outlook among other things. He touched on both the calendar and contacts. It reminded me of a forum post I did a while ago and inspired me to formalize it with this blog post. In Scott's examples, he demonstrated how to make contacts and events in ApEx load directly into Outlook. He even came up with a good way of letting you know if the contacts information has changed since you last loaded it. These were all great new techniques I'd not thought about before.

In the forum post, I wrote about how to send a calendar request via email. This was a technique that I had used in a work flow scenario as the final step. It was necessary because we use Outlook to schedule meetings and didn't want to have to manually add data to Outlook from something that had been scheduled in an ApEx application so that the time would not appear as "free".

At the time, I thought of this as an ApEx solution but the truth is that it is a PL/SQL solution that just works great in ApEx. The solution requires two ingredients: a function (ical_event) to generate an iCalendar event and a process (send_ical_email) to send the request. The APEX_MAIL.SEND procedure will not work in this case as we need more control over the email contents. In the following examples, just the basics are included to get you started but the code can be modified to suite any additional needs. Additional information on the iCalendar (based on vCalendar) standard can be found here. Also, Google has added some of their own additional fields and information on those can be found here. Lastly, an example of the technique can be found here.

Here's what to do...

Compile the following function (replace "Your company name" and "yoururl.com" as needed):

create or replace
FUNCTION ical_event (
   p_summary         IN VARCHAR2
 , p_organizer_name  IN VARCHAR2
 , p_organizer_email IN VARCHAR2
 , p_start_date      IN DATE
 , p_end_date        IN DATE
 , p_version         IN VARCHAR2 := NULL
 , p_prodid          IN VARCHAR2 := NULL
 , p_calscale        IN VARCHAR2 := NULL
 , p_method          IN VARCHAR2 := NULL
)

   RETURN VARCHAR2 

AS  

   l_retval VARCHAR2(32767);
   l_lf     CHAR(1) := CHR(10);

BEGIN

   l_retval := ''
      || 'BEGIN:VCALENDAR' || l_lf
      || 'VERSION:' || NVL(p_version,'2.0') || l_lf
      || 'PRODID:' || NVL(p_prodid,'-//Your company name//NONSGML ICAL_EVENT//EN') || l_lf
      || 'CALSCALE:' || NVL(p_calscale,'GREGORIAN') || l_lf
      || 'METHOD:' || NVL(p_method,'REQUEST') || l_lf
      || 'BEGIN:VEVENT' || l_lf
      || 'SUMMARY:' || p_summary || l_lf
      || 'ORGANIZER;CN="' || p_organizer_name || '":MAILTO:' || p_organizer_email || l_lf
      || 'DTSTART:' || TO_CHAR(p_start_date,'YYYYMMDD') || 'T' || TO_CHAR(p_start_date,'HH24MISS') || l_lf
      || 'DTEND:' || TO_CHAR(p_end_date,'YYYYMMDD') || 'T' || TO_CHAR(p_end_date,'HH24MISS') || l_lf
      || 'DTSTAMP:' || TO_CHAR(SYSDATE,'YYYYMMDD') || 'T' || TO_CHAR(SYSDATE,'HH24MISS') || l_lf
      || 'UID:' || RAWTOHEX(SYS_GUID()) || '@yoururl.com' || l_lf
      || 'STATUS:NEEDS-ACTION' ||  l_lf
      || 'END:VEVENT' || l_lf
      || 'END:VCALENDAR';
   
   RETURN l_retval;
      
END ical_event;

Create the following procedure (replace "yoururl" ass needed):

create or replace
PROCEDURE send_ical_email (
   p_from      IN VARCHAR2
 , p_to        IN VARCHAR2
 , p_subj      IN VARCHAR2
 , p_body_html IN VARCHAR2
 , p_body_ical IN VARCHAR2
)
 
AS

   l_connection UTL_SMTP.CONNECTION;
   l_mail_serv  VARCHAR2(50) := 'mail.yoururl.com';
   l_mail_port  PLS_INTEGER := '25';
   l_lf         CHAR(1) := CHR(10);
   l_msg_body   VARCHAR2(32767);
 
BEGIN
   
   l_msg_body :=
         'Content-class: urn:content-classes:calendarmessage' || l_lf
      || 'MIME-Version: 1.0' || l_lf
      || 'Content-Type: multipart/alternative;' || l_lf
      || '	boundary="----_=_NextPart"' || l_lf
      || 'Subject: ' || p_subj || l_lf 
      || 'Date: ' || TO_CHAR(SYSDATE,'DAY, DD-MON-RR HH24:MI') || l_lf
      || 'From: <' || p_from || '> ' || l_lf 
      || 'To: ' || p_to || l_lf 
      || '------_=_NextPart' || l_lf
      || 'Content-Type: text/plain;' || l_lf
      || '	charset="iso-8859-1"' || l_lf
      || 'Content-Transfer-Encoding: quoted-printable' || l_lf
      || l_lf
      || 'You must have an HTML enabled client to view this message.' || l_lf
      || l_lf
      || '------_=_NextPart' || l_lf
      || 'Content-Type: text/html;' || l_lf
      || '	charset="iso-8859-1"' || l_lf
      || 'Content-Transfer-Encoding: quoted-printable' || l_lf
      || l_lf
      || p_body_html || l_lf
      || l_lf
      || '------_=_NextPart' || l_lf
      || 'Content-class: urn:content-classes:calendarmessage' || l_lf
      || 'Content-Type: text/calendar;' || l_lf
      || '  method=REQUEST;' || l_lf
      || '  name="meeting.ics"' || l_lf
      || 'Content-Transfer-Encoding: 8bit' || l_lf
      || l_lf
      || p_body_ical || l_lf
      || l_lf
      || '------_=_NextPart--';
            
   l_connection := utl_smtp.open_connection(l_mail_serv, l_mail_port);
   utl_smtp.helo(l_connection, l_mail_serv);
   utl_smtp.mail(l_connection, p_from);
   utl_smtp.rcpt(l_connection, p_to);
   utl_smtp.data(l_connection, l_msg_body);
   utl_smtp.quit(l_connection);
   
END send_ical_email;

Use ApEx to create a submit button along with the necessary items to collect the following:
1. Start Date
2. End Date
3. Organizer Name
4. Organizer Email
5. Attendee Email
6. Email Subject
7. Email Body

Create a page process similar to the following that fires when the submit button is pressed (this will vary depending on step 3):

DECLARE

   l_ical_event VARCHAR2(32767);

BEGIN

   l_ical_event := ical_event(
      p_start_date      => TO_DATE(:PXX_START_DATE || :PXX_START_TIME,'DD-MON-YYYYHH:MIPM')
    , p_end_date        => TO_DATE(:PXX_END_DATE || :PXX_END_TIME,'DD-MON-YYYYHH:MIPM')
    , p_summary         => :PXX_SUBJ
    , p_organizer_name  => :PXX_USER_NAME
    , p_organizer_email => :PXX_USER_EMAIL
   );

   send_ical_email( 
      p_to        => :PXX_TO_ADDRESS
    , p_from      => :PXX_USER_EMAIL
    , p_subj      => :PXX_SUBJ
    , p_body_html => :PXX_BODY_HTML 
    , p_body_ical => l_ical_event
   );
   
END;

That should do it. Submit the page to send the request.

tapiGen is born

2008-06-07T21:07:00.001-04:00

I've just released the first version of tapiGen. tapiGen is a PL/SQL table API generator - it creates packages for your tables. Each package contains functions, procedures, and types that can help enforce best practices, reduce code and maintenance, and improve performance.

The project is on SourceForge at:

http://sourceforge.net/projects/tapigen/

Feel free to let me know what you think!

Replacing Date Pickers

2008-06-07T14:34:00.001-04:00

The default date pickers in ApEx are great. But there were two things I didn't like about them:

They require a server side hit to generate.
You have to click on the number inside the day. The entire day "square" does not work.

I spent some time looking and found a great jQuery plugin that solved both problems. It's called jQuery UI Datepicker and it was created by Mark Grabanski and Keith Wood. If you decide to use their product, although it's not required, you may want to consider making a donation. Information about their product can be found at the following URLs.

To view an example of the traditional ApEx date picker and jQuery UI Datepicker side by side, see the following example.

http://apex.shellprompt.net/pls/apex/f?p=apex_demo:2

Here are some simple instructions on how to get it working in ApEx...

Install jQuery in ApEx.
Download ui.datepicker.css and ui.datepicker.js from here (under where it says "Use jQuery UI Datepicker"). Create a new folder called uidatepicker in your i/jquery directory (created in step 1) and copy both files into that location.
Add a text item that you would like to serve as the date picker. If you are converting an old date picker, open the item and change its type to "TextField".

Go to edit the page and enter the following in the HTML Header area:

<link type="text/css" rel="stylesheet" href="i/jquery/uidatepicker/ui.datepicker.css" />
<script type="text/javascript" src="i/jquery/uidatepicker/ui.datepicker.js"></script>

<script type="text/javascript">
//<![CDATA[
   
   $(document).ready(function(){
      $.datepicker.setDefaults({
         dateFormat: 'dd-M-yy', 
         showOn: 'button',
         buttonImageOnly: true, 
         buttonText: '',
         buttonImage: '/i/asfdcldr.gif', 
         monthNamesShort: ['JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC']
      });
      
      $('#PXX_ITEM_NAME').datepicker();
   });

//]]>
</script>

Note: make sure to replace "PXX_ITEM_NAME" with the correct item name.

That's it! Run the page and check it out.

AJAX Item Help

2008-06-07T13:54:00.001-04:00

As most people know, items in ApEx have a "help" built in. When the end user of your application clicks on an item's label, a new window opens displaying that item's help text. I wanted to see if I could achieve this without a new window. After a little digging I found a great jQuery plugin called clueTip which can be set to use another plugin called hoverIntent. Information about the products can be found at the following URLs.

To view an example of an item that has the standard popup help and one that has "Hover Help" side by side, see the following example.

http://apex.shellprompt.net/pls/apex/f?p=apex_demo:3

Here are some simple instructions on how to get it working in ApEx...

Install jQuery in ApEx.
Download cluetip from the links above. Create a new folder called cluetip in your i/jquery directory (created in step 1) and unzip the contents of the zip file into that location.
Donload hoverIntent from the links above. Save the contents in a file called jquery.hoverIntent.js and save that file in a new folder named hoverintent in the i/jquery directory.

Enter the following in the HTML Header of the page where you would like to use hover help. If you would like to use hover help on multiple pages, the link and script elements can be placed in the page template instead of the individual page.

<link href="i/jquery/cluetip/jquery.cluetip.css" type="text/css" rel="stylesheet" />
<script src="i/jquery/dimensions/jquery.dimensions.js" type="text/javascript"></script>
<script src="i/jquery/hoverintent/jquery.hoverIntent.js" type="text/javascript"></script>
<script src="i/jquery/cluetip/jquery.cluetip.js" type="text/javascript"></script>
 
<script type="text/javascript">
//<![CDATA[
 
   $(document).ready(function(){
      var pageID = $('#pFlowId').val();
 
      $('.hoverHelp').each( function(i) {
            var $item = $(this);
            var get = new htmldb_Get(null,pageID,'APPLICATION_PROCESS=ITEM_HELP',0);
            get.add('TEMP_ITEM', $item.attr('for'));
            $item.attr('rel', get.url());
            $item.attr('relTitle', $item.html());
            return true;
      });
 
      $('.hoverHelp').cluetip({
         arrows: true,
         titleAttribute: 'relTitle',
         hoverIntent: {    
            sensitivity: 2,
            interval: 200,
            timeout: 0
          }
      }); 
   });
 
//]]>
</script>

Create an application item called TEMP_ITEM

Create an application process named ITEM_HELP that fires ondemand with the following code:

DECLARE

   l_item_rec APEX_APPLICATION_PAGE_ITEMS%ROWTYPE;
   
BEGIN

   SELECT *
   INTO l_item_rec
   FROM apex_application_page_items
   WHERE application_id = :APP_ID
   AND item_name = :TEMP_ITEM;
   
   htp.p ('<div style="padding-right: 4px; padding-left: 4px; padding-bottom: 4px; margin: 4px; padding-top: 4px"><span class="instructiontext">' 
      || l_item_rec.item_help_text 
      || '</span></div>');
      
END;

Create two new label templates called "Optional Label with Hover Help" and "Required Label with Hover Help". For the Optional Label with Hover Help, set the "Before Label" value to:
```
<label class="hoverHelp" style="cursor: help" tabindex="999" for="#CURRENT_ITEM_NAME#" rel="" reltitle="">
```
Then set the "After Label" value to:
```
</label>
```
For the Required Label with Hover Help, set the "Before Label" value to:
```
<label class="hoverHelp" style="cursor: help" tabindex="999" for="#CURRENT_ITEM_NAME#" rel="" reltitle="">
<img alt="" src="#IMAGE_PREFIX#requiredicon_status2.gif" />
```
Then set the "After Label" value to:
```
</label>
```
The important attributes in both label elements are rel and relTitle. Both are empty when the page is loaded but are filled with the URL needed for the AJAX call and the label name respectively as soon as jQuery can do so.
Finally create some items and set the templates for the labels to the newly created templates and populate some help data for the items. Then run the page and see the new help!

"Installing" jQuery in Application Express

2008-06-07T00:31:00.002-04:00

So you've decided to use jQuery - smart move! Here's how you get it working in Application Express.

Go to http://jquery.com and download the latest copy of the library. You'll find that each version is released with a few different compression levels. You can read more about them in their site but I recommend the "Packed" version to get you up in running quickly.
Make the file you downloaded available on your web server. The most common way to do this in ApEx is to drop it in your "/i" directory. I would create a folder under that directory called "jquery" and place it in there. Later this folder can be used to hold the various plugins that you'll want to use.
Now that the file is available for use you have to "include" it in your ApEx application. To make jQuery available on every page go to Shared Components > Templates. Open the page template that your application is using. Look in the Definition/Header textarea and between the <head> and </head> tags, add a line like the following but with the directory and filename specific to your setup: * Make sure to update use the version of jquery you downloaded.
```
<script type="text/javascript" src="/i/jquery/jquery-1.2.3.pack.js"></script>
```
If you application is using multiple page templates and you want to use jQuery in all of them, you'll have to add the line from step 3 to each one. Also, these steps must be repeated for each application. If you or your company has not already done so, you might want to create a custom theme that includes it so that each future application that created will have it by default.

That's it, you're done. Now it's time to have some fun!