AE APEX Blog Aggregator

OraTweet - Tweeting on the Enterprise

Tue, 25 Aug 2009 19:16:49

Update: You can now download the OraTweet package at http://oratweet.com !

How It All Began:
Although I didn't come up with the whole micro-blogging concept, I did realize the potential it had in the enterprise when I first stumbled upon Twitter. Instead of just writing on this blog, a forum, or in Twitter something like, "Man, someone should write an enterprise twitter," I went to work using Oracle APEX which is an amazing rapid web application development tool that allowed me to focus on the real issue: create a micro-blogging messaging system. Since APEX sits right on top of our great flagship (Oracle RDBMS + mod_plsql = FTW!) it allowed me to use my pl/sql ninja skills.

It took me literally one weekend to have the bare bone architecture up and running.
As I posted here before I used a loaded JAVA/PLSQL XMPP library based on smack api to support IM messaging right from the Oracle JVM and created OraTweetBot as an IM listener. Hooking OraTweet to our internal IM system works flawlessly since we are using open source XMPP protocols. User adoption is also a plus since users can use their existing IM clients to interact with OraTweet. OraTweet also has the option of sending and getting updates from Twitter with PL/SQL, thus giving the user the option to post to the world or just internally.

From the beginning I got great feedback from our own Web 2.0 AppsLab adoption team and got some great pointers on corporate adoption. Since this is a side project I let OraTweet sit for a few weeks without much advertisement while I was doing improvements (and my actual work!). But Meg Bear and the Fusion Talent Management Products team stumbled over OraTweet while looking for a solution to bridge the communication gap within their global team. They contacted me, and we worked together on some improvements and launched a pilot.
At this point Carl Backstrom from the APEX team offered to help me. Now thanks to him we have a working API, and we have plans to deploy OraTweet globally once it is ready. Having this API lets OraTweet work as a global messaging system allowing third parties and platforms such as OpenSocial interact with OraTweet users.

After one week of alpha/beta life and not much internal advertisement OraTweeters numbers went from a dozen to the hundreds and is still growing, which shows that there is a hidden demand for internal communication tools like this. I like the fact that OraTweet flattens enterprise hierarchies and opens communication between all levels.

Now the best part is that I'm planning to release OraTweet for free as an APEX package once all the beta wrinkles are out. It works great next to the other free packages such as ARIA, which is our internal employee directory.

What I envision as I release OraTweet is seeing companies, universities, and organizations running their own OraTweet instance, allowing them to keep their information private yet strengthening their own internal communities. It should be the same way we do email and instant messaging: We manage our own information, which allows us to broadcast messages safely in our own microcosm.

PS Screenshots coming soon!

Update 1 - Screenshot 1

Update 2 - Latest Screenshot

Ignore sql error messages in pl/sql process

Mon, 16 Nov 2009 11:56:06

Have you experienced the case that you want to execute a process and when a specified error occurs then it should go on like nothing happened.

In my case I had several pl/sql process and one was to delete a database link. In my special case there shouldn't be an error if no database link exists.

Here is the code snippet for it:


declare
-- error variable
v_no_link EXCEPTION;
-- Map error number returned by raise_application_error to user-defined exception.
PRAGMA EXCEPTION_INIT(v_no_link, -2024);
-- About the error: http://download.oracle.com/docs/cd/B28359_01/server.111/b28278/e1500.htm#sthref1158 

begin
-- Drop existing database link 
EXECUTE IMMEDIATE 'drop database link ' || UPPER(:P1_I_INSTANCE_NAME);
commit;

EXCEPTION
  WHEN v_no_link THEN
    null;
  WHEN OTHERS THEN
    raise_application_error(SQLCODE, 'SQLERRM');
end;

More information: PL/SQL User's Guide and Reference - Error Handling

Using Decimals in APEX Page Numbers

Mon, 16 Nov 2009 09:36:53

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

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

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

Oracle 11g DB on Windows 7 – Success

Mon, 16 Nov 2009 03:32:11

Short Version Tried to install Oracle Database 11.1.0.7 32-bit on Windows 7 32-bit. Failed at Network Configuration section. I found that in my case, the issue was that I installed in a path with spaces in it (c:\program files\oracle\….). Un-installed. Reinstalled in c:\oracle\product\11.1.0.7. Success!!! Longer Version OK, I admit it, I’ve been running Windows 7 since the first [...]]]>

Interactive Reports Not Working Properly on New Install

Fri, 13 Nov 2009 13:59:04

This is just a quick note to self, but may be useful to others experiencing similar problems. I would not recommend the Fix Two solution for production environments before conducting further research though...

Preface
Recently I installed Oracle Apex 3.2 in an existing database. Plain vanilla install on an Oracle 9.2.0.5 database with an Oracle iAS 9.2.0.3 in front. Installation went smooth, demo application works. Shift. Different site, same plain install, but this time on a 10.2.0.4 database, and 10.1.2 application server.

The Demo Effect
What is the first thing you show off when demonstrating Apex? The interactive reports, that is a no-brainer :-) But at both of these installations, something goes wrong. When clicking a report column, I just get the spinning wheel, no other response. What is amiss?

Dissecting the Problem
At the first location, I had no time and no Firebug. At the second location I had both, and two failed installations creating some sort of consistency.

In the Firebug console, I can see a javascript error pops up when a column is clicked. Sometimes it throws string not terminated error, sometimes some other cryptic message, but always the same javascript function. Examining the response in Firebug shows something odd; the response is cut short. Depending on the distinct values of the column I clicked, the response might be cut inside a string (string not terminated error), or in-between. When clicking numeric columns, it works. Hm... Special characters? NLS?

Fix One
Patching the Apex installation to 3.2.1 worked for the installation on the 10g system. IR's started working when the patch was applied.

One down, one to go...

Fix Two (The Dirty Fix)
Examining dads.conf for the 9i installation, I see previously configured dads has a different setting of PlsqlNLSLanguage. Both installs go against databases with NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252 (don't ask me why, seems like a popular choice for older systems in Norway).

Changing from PlsqlNLSLanguage from AMERICAN_AMERICA.ALUTF8 to AMERICAN_AMERICA.WE8MSWIN1252 did the trick, IR's are now working as expected. I have not noticed anything else breaking (yet), but I am not at all comfortable with the workaround.

The documentation clearly states:
"The character set portion of the PlsqlNLSLanguage value must be set to AL32UTF8, regardless of whether or not the database character set is AL32UTF8."
or for older versions of iAS:
"The character set portion of the nls_lang value must always be set to AL32UTF8, regardless of whether or not the database character set is AL32UTF8."

Luckily, the 9i installation will not go to production in it's current state. Phew...

(ā'pěks)

Thu, 12 Nov 2009 16:51:47

At the ODTUG APEXposed conference in Atlanta this week, a customer asked me to blog about how to abbreviate and pronounce the abbreviation for Oracle Application Express. I've seen numerous abbreviations (Apex, ApEx, APPEx, AppEx, APEX), and I think the lack of a formal abbreviation lends to the confusion in pronunciation. So here goes:

Whenever an abbreviation is used in informal writing, it's abbreviated in all uppercase: APEX
It's pronounced (ā'pěks). It's a long 'A' as in 'acorn'.
In formal written materials from Oracle, it is always written as 'Oracle Application Express' and never abbreviated.

In conversation and in some presentation materials, you will see the Oracle Application Express team use the above spelling and pronunciation.

Collecting ideas for APEX 4.0 Plug-Ins...using Google Wave

Wed, 11 Nov 2009 17:06:51

As you all know by now APEX 4.0 will have some new functionality regarding the creation of Custom Item Types and Custom Region Types. These types will be called "Plug-Ins". The idea is that there will be a public App Store like library where you can up- and download plug-ins.

A snippet from the Oracle Open World APEX 4.0 presentation:

Easy way to enhance the existing built-in functionality of Oracle APEX with new item types, region types, dynamic actions, etc.
Developers use in similar fashion to native widgets
Wizard support and declarative setting of attributes
Included in APEX application export
By providing this plug-in system in APEX, we want to engage the APEX community to create a rich ecosystem around Oracle APEX.

The APEX Development Team can’t possibly incorporate all the widgets that developers would like to utilize. By using plug-ins developers can readily incorporate additional item / region types to enhance the functionality, appearance and user friendliness of their applications. Once defined, plug-in based components are created and maintained very much like standard APEX components. We believe that the APEX community will build many plug-ins and make them available to others. Much like with the SQL Developer plug-ins it will be up to the contributors whether these are freeware, etc.

In the demo the APEX Dev Team is showing an Amazon style 'star rating' (as an Item Type Plug-In). (like the image above)

So maybe now is time to gather ideas for Plug-Ins (using this cool collaboration tool). So don't be shy and add your ideas about the Plug-Ins you think you could use (or even create yourself !) or are just very cool....

Custom Item Types

Amazon like star rating

Custom Region Types

Google Wave
Google Visualizations

So please add your own ideas to this wave, and let's make collaboration happen...

Using the APEX IRR search region

Wed, 11 Nov 2009 15:09:41

Hi all,

The APEX team integrated a nice little region template which mimics the search part of an interactive report. It is called "Report Filter - Single Row". As the name says it has only one row to implement items.

Using one graphical way for searching in your whole application makes life easier for your costumers.

There is just one thing I didn't like on it. The "Search"-icon. To erase it make a copy of the template and erase this part: <img src="#IMAGE_PREFIX#htmldb/builder/builder_find.png" />

The template definition should now look like that:


<table class="apex_finderbar" cellpadding="0" cellspacing="0" border="0" summary="" id="#REGION_STATIC_ID#" #REGION_ATTRIBUTES#>
<tbody>
<tr>
<td class="apex_finderbar_left_top" valign="top"><img src="#IMAGE_PREFIX#1px_trans.gif" width="10" height="8" alt=""  class="spacer" alt="" /></td>
<td class="apex_finderbar_middle" rowspan="3" valign="middle"></td>
<td class="apex_finderbar_middle" rowspan="3" valign="middle" style="">#BODY#</td>
<td class="apex_finderbar_left" rowspan="3" width="10"><br /></td>
<td class="apex_finderbar_buttons" rowspan="3" valign="middle" nowrap="nowrap"><span class="apex_close">#CLOSE#</span><span>#EDIT##CHANGE##DELETE##CREATE##CREATE2##COPY##PREVIOUS##NEXT##EXPAND##HELP#</span></td>
</tr>
<tr><td class="apex_finderbar_left_middle"><br /></td></tr>
<tr>
<td class="apex_finderbar_left_bottom" valign="bottom"><img src="#IMAGE_PREFIX#1px_trans.gif" width="10" height="8"  class="spacer" alt="" /></td>
</tr>
</tbody>
</table>

At the end it will look similar to that:

Wed, 11 Nov 2009 11:27:29

Setup BI dashboard in APEX

I have been looking around for some good Business Intelligence Dashboard templates to be used together with APEX. What our customer wanted was a dashboard which was easy to navigate and functional to use.

We wanted to put in some web 2.0 functionality such as drag and drop for placing portles in the position the user wanted and and lots more Jquery stuff such as tabs, modal popup windows, nice help window, date picker, resize of menu navigator, hide/show portlets. The layout is automaticly saved in the database.

The web 2.0 features is made by using Jquery to get a slick and fast refresh of the page.

What we ended up with was a APEX theme which you can have a look at

http://www.apex-home.com/pls/apex/f?p=56502:300:2432006777088735::::NEWSID:200

Thanks to Stephen Blair at apex-themes which made a very good job putting this together.

Together with Oracle Warehousebuilder, BI Publisher, APEX Interactive reporting, and Anycharting it should be possible to make functional and good solutions without high starting cost.

regards
Jon

AUSOUG Conference 2009 Day One

Tue, 10 Nov 2009 18:50:36