OraNA :: Oracle News Aggregator

On Tuesday I was in the Netherlands to give a presentation about "The Future of APEX".

The location was astonishing! The place is called Media Plaza and is located at the "Jaarbeurs" in Utrecht, the Netherlands. It's definitely an innovative place where you get lots of ideas, so a very good place to talk about Oracle Application Express.

There were 135 people subscribed for this free seminar, so we can definitely say that APEX is gaining a lot of interest. I did a poll about who was in the audience and everybody already heard about APEX! Not everyone worked with it yet and I was surprised there were so little on the latest version of APEX. But that turned out great, as I could show them some cool new features ;-)


We split the evening in two parts. In the first part I briefly talked about my way to APEX, gave an introduction in APEX, but for the most part I did a demo with hints & tips. I like to show things and I think APEX is really good for that.

I created a completely new application and included some of the nicest features in APEX: of course Interactive Reports, but also how easy it is to include images (Blob support), do PDF printing and adhoc changes.

I liked the interactivity with the public very much! For ex. I gave an example where I created a new column in the Interactive Report, called "new list price", basically the list price of a product increased with 10%. At one time a nice lady asked me if the new value is physically stored in the database (which isn't) and how to get the changes in then. I first started to explain how you could do it, but as APEX is extremely RAD, I just decided to do it in front of 135 people. Live demo's is always a risk, but if it works, the public loves it! Doing these little things shows the real power of APEX.


In the second part I talked about the future of APEX or what we may expect in the coming releases. I briefly touched custom item types, new versions of 3rd party products, declarative ajax, improved error handling etc. but most of the time I spend on the Forms migration to APEX tool the Apex Development team is working on (I recorded David Peake's presentation at ODTUG ;-)), the Data Modeling capabilities that will be in SQL Developer and of course Websheets!

Looking at the many questions I got, the feedback afterwards and that people started to be enthusiastic with each other about our lovely development tool, I believe this evening was a success. I definitely enjoyed doing it!

Creating a query and including the ROWNUM will work only for those reports, with disabled column sorting. Once you enable the column sorting, the ROWNUM will not show the right order. This is because ApEx does the sorting after determining the ROWNUM.

A simple trick can help you to overcome this issue. Just escape the ApEx internal #ROWNUM# like this:

SELECT '#ROWNUM#' SEQUENCE, empno, ename, sal
  FROM emp;

and it will give you the right ROWNUM regardless of your sorting.



You can see that working in this example in my Demo Application.

This weekend the OTN Forums got updated.

The forums went down for some time and when they finally got up again I got a lot of errors.
I cleared the cache of my browser and that helped at first sight, but after a while the problems were back again. Although there are some new features (tag, rich editor, new icons, ...), I wonder if it really needs to be like that.

For me a forum needs to be fast, reliable and searchable, but that's absolutely not the case! It's down a lot and it's even slower than before. I guess it's because they just released this version.

Some other people already blogged about it too:
- Dietrich Schroff (with some nice screenshots)
- Amardeep Sidhu
- Paulo Vale
- Asif Momen

Normaly, if there are page items of type "Text Field" they will not submit the page unless you change them to "Text Field (always submits page when Enter pressed)". If you have only one item on you page of type "Text Field" this behaviour changes and the page will always submit if you press "Enter". Sometimes, you don't want this to happen. Especially, if you have some javascript attached to your field. To avoid that you can use the following trick:

1. create another item on you page of type "Text Field",

2. in the "HTML Form Element Attributes" of that item put

style="display:none"

The result is that the second item will not be displayed and the first item will not submit on pressing "Enter".

The question in the forum was:

I need to allow my users to type in a list of names and return the names that are not in a table.

1. User types [ Tom Joe Bob MIKE ] into a Text Area item named :P2_NAME_CHECK

2. A table named CUST contains two rows with TOM and Bob in the 
   CUST_NAME column.

3. A report returns two rows JOE and MIKE. 

Usually, it is oposite. You want to show the list of users that match the criteria.

The solution for this problem is quite easy if you know how to use pipelined functions. This is a case where the pipelined functions shine. I created an example in my Demo Application showing how this can be done.



Basically, the pipelined function will create a row for all substrings returning no rows from the emp table, showing those substrings as rows.

Recently there have been several discussions in the forum regarding multiple tabular forms on one page. I thought it is easy if you know how to create one tabular form manually, you will be able to do it for any other number of those forms. However, many participants of the forum do not share my opinion. This is why I decided to create a very basic example showing how that works. You will find it in my Demo Application together with the required code. If you want to see all the details, you may apply for an account following the instructions on the login page of my Demo Application.



Have fun.

After a downtime, OTN Forums came up with some look changes and new functionality. After a quick look, here are some of the changes I've noticed: - pre tag don't work anymore!!! However there is still the option to use the code tag. Unfortunately this code tag highlights random keywords! - A link to report post abuse; - New post editor, a Rich text editor with possibily of:    . Marking a

Now that you have posted your Twitter updates from APEX, you might want to read your friends time line. The procedure is very similar to posting of updates, except this time you want to receive the XML and process it for display inside a SQL query report. The returning XML will show the 20 most recent statuses posted in the last 24 hours from the authenticating user and that user's friends.

create or replace

FUNCTION get_twitter_timeline(t_user IN VARCHAR2,   t_pass IN VARCHAR2)

RETURN XMLTYPE

IS

http_req utl_http.req;

http_resp utl_http.resp;

t_update_send VARCHAR2(200);

res_value VARCHAR2(4000);

XML_RETURN CLOB;

BEGIN

t_update_send := '--head';

--utl_http.set_proxy('http://www-yourproxy.com:80'); --If you need to specify a proxy use this.

http_req := utl_http.begin_request('http://twitter.com/statuses/friends_timeline.xml',   'POST',   utl_http.http_version_1_1);

utl_http.set_response_error_check(TRUE);

utl_http.set_detailed_excp_support(TRUE);

utl_http.set_body_charset(http_req,   'UTF-8');

utl_http.set_header(http_req,   'User-Agent',   'Mozilla/4.0');

utl_http.set_header(http_req,   'Content-Type',   'application/x-www-form-urlencoded');

utl_http.set_header(http_req,   'Content-Length',   to_char(LENGTH(t_update_send)));

utl_http.set_transfer_timeout(to_char('60'));

utl_http.set_authentication(http_req,   t_user,   t_pass,   'Basic');

utl_http.write_text(http_req,   t_update_send);

http_resp := utl_http.get_response(http_req);

BEGIN

WHILE 1 = 1

LOOP

  utl_http.read_line(http_resp,   res_value,   TRUE);

  XML_RETURN := XML_RETURN || res_value;

END LOOP;

EXCEPTION

WHEN utl_http.end_of_body THEN

NULL;

END;

utl_http.end_response(http_resp);

return XMLTYPE(XML_RETURN);

EXCEPTION

WHEN others THEN

XML_RETURN := sqlerrm;

RAISE;

END get_twitter_timeline;

Usage:

SELECT EXTRACTVALUE(VALUE(twitter), '/status/user/name')  AS NAME

, EXTRACTVALUE(VALUE(twitter), '/status/text') AS TEXT

, EXTRACTVALUE(VALUE(twitter), '/status/created_at') AS CREATED_AT

, EXTRACTVALUE(VALUE(twitter), '/status/source') AS SOURCE

, EXTRACTVALUE(VALUE(twitter), '/status/user/screen_name') AS SCREEN_NAME

, EXTRACTVALUE(VALUE(twitter), '/status/user/profile_image_url') AS PROFILE_IMAGE_URL

FROM

TABLE(XMLSEQUENCE(EXTRACT(get_twitter_timeline('twitter_user','twitter_password'), '/statuses/*'))) twitter

Note that according to Twitter API the public timeline is cached for 60 seconds so requesting it more often than that is a waste of resources

Happy Posting :)

Two weeks ago I attended the ODTUG Kaleidoscope 2008 conference in New Orleans. This was a great event.

The view from my hotel room was truly awesome:


On Saturday I did have some time to hang out with Patrick Wolf in the French Quarter for breakfast:


On the first (pre conference) day, there was an APEX symposium held. The focus was on customer success stories showing off successful real world implementations of APEX projects to add even more "street" credibility to APEX.

It was very interesting to hear about the different projects.

My presentation was about the successful implementation of two projects at the German Telecom Shops. My main struggle there has been the very tight deadlines (only a few weeks) in which I had to deliver the applications.

(Scott Spendolini and me)

(giving the presentation)

(answering questions)

I am very happy that my presentation was well received. You can download the presentation here.

Also, it was the first time for me to record my online demo and show the video during my presentation. This way I could pause the demo and explain more details where necessary. Also I wouldn't have to switch between screens so often to copy/paste information for the demo. Sometimes this makes a presentation a bit hard to follow. The tool I used for that was the Camtasia Studio by Techsmith. I am very pleased with the produced results.

I really enjoyed hanging out with the APEX development team and meeting really nice folks I know from the Oracle APEX forum.

Regards,
~Dietmar.

Today Germany and Turkey play against each other for a place in the finals at Euro 2008. I think it will be a good match although I wished Portugal was still in.

For the people betting on the games in DG Tournament, don't forget to bet on the semi-finals!
I had a look at the rank and it seems that the first 30 people still make a good chance to win the bet.


That the best may win...

Raj Mattamal has put up an interesting topic for a presentation at Oracle Open World 2008, it's about Writing Custom Authentication Schemes for Application Express. The presentation is not just the basic stuff you will find in the manual or the tutorial, it goes way further. For example it covers SSO over multiple workspaces.

In case if you are not interested in the topic, it's still always fun to watch Raj doing a presentation/see him talking (fast). This guy has way to much energy or is drinking to much Red Bull ;-)

So people, vote for him that he is able to present that topic at Oracle Open World 2008!

And don't forget about the other great Oracle APEX sessions!

I started a pet project called OraTweet. It is an Oracle internal "Twitter" with some key advantages such as the ability to follow groups and IM (XMPP/Jabber) integration. Aside from having our own internal "tweets" a user can direct a message @oratweet and the application will post it to the generic OraTweet Twitter account. Also the user will be able to store its own credentials and post to its own account (ie @noelportugal). I started playing with the Twitter API and first start sending updates with a combination of a Java Class to execute a shell Curl command. I soon realized that was not the best way to do it, so I went to work and got the following Procedure. (note that this is the quick and dirty version, feel free to extend it and make it more foolproof)

CREATE OR REPLACE PROCEDURE update_twitter(t_user IN VARCHAR2,   t_pass IN VARCHAR2,   t_update IN VARCHAR2) AS
http_req utl_http.req;
http_resp utl_http.resp;
h_name VARCHAR2(255);
h_value VARCHAR2(1023);
t_update_send VARCHAR2(4000);
res_value VARCHAR2(32767);
show_header NUMBER := 0;--0 False, 1 True
show_xml NUMBER := 1;--0 False, 1 True
BEGIN
t_update_send := 'status=' || SUBSTR(t_update,   1,   140) || '';
--utl_http.set_proxy('http://www,yourpoxy.com:80'); --If you need to specify a proxy un comment this line.
http_req := utl_http.begin_request('http://twitter.com/statuses/update.xml',   'POST',   utl_http.http_version_1_1);
utl_http.set_response_error_check(TRUE);
utl_http.set_detailed_excp_support(TRUE);
utl_http.set_body_charset(http_req,   'UTF-8');
utl_http.set_header(http_req,   'User-Agent',   'Mozilla/4.0');
utl_http.set_header(http_req,   'Content-Type',   'application/x-www-form-urlencoded');
utl_http.set_header(http_req,   'Content-Length',   to_char(LENGTH(t_update_send)));
utl_http.set_transfer_timeout(to_char('60'));
utl_http.set_authentication(http_req,   t_user,   t_pass,   'Basic');
utl_http.write_text(http_req,   t_update_send);
http_resp := utl_http.get_response(http_req);

DBMS_OUTPUT.PUT_LINE('status code: ' || http_resp.status_code);
DBMS_OUTPUT.PUT_LINE('reason phrase: ' || http_resp.reason_phrase);

IF show_header = 1 THEN
FOR i IN 1 .. utl_http.get_header_count(http_resp)
LOOP
utl_http.get_header(http_resp,   i,   h_name,   h_value);
DBMS_OUTPUT.PUT_LINE(h_name || ': ' || h_value);
END LOOP;
END IF;

IF show_xml = 1 THEN
BEGIN
WHILE 1 = 1
LOOP
 utl_http.read_line(http_resp,   res_value,   TRUE);
 DBMS_OUTPUT.PUT_LINE(res_value);
END LOOP;

EXCEPTION
WHEN utl_http.end_of_body THEN
NULL;
END;
END IF;

utl_http.end_response(http_resp);

EXCEPTION
WHEN others THEN
DBMS_OUTPUT.PUT_LINE(sqlerrm);
RAISE;

END update_twitter;

Usage:

begin
UPDATE_TWITTER('my_twitter_acct','my_twitter_pass','Hello World from Oracle PL/SQL and APEX http://apextoday.blogspot.com');
end;

My following post will include on how to create an XMPP/Jabber listener with Java and how I hooked it up to my application.

I'm talking about Raj Mattamal's session up for votes on mix.oracle.com. Raj is a good friend of mine and used to be on the APEX team until he decided to strike out on his own to use APEX to make money in the real world. He is very knowledge about all subjects APEX and this one in particular.

Both Tyler and Dimitri have mentioned voting for Raj's session but I just wanted to throw my endorsement out for this session as well.

If you've seen Raj present before you will know that it is an event in itself and when you combine that with the amount of useful information that comes out of the session it's a winner. Just take a look at my review of his session from last Oracle World.

I have to admit I'm doing this for a selfish reason, I missed his session at ODTUG , I didn't think it was a big deal since I've seen it before, until I was informed it was an entirely different session I was pretty disappointed on missing it, plus he might have some blackmail on me I don't know and don't want to find out ;).

Vote Early , Vote Often.

Last week at ODTUG there were 18 Oracle Application Express sessions.

At Oracle Open World there will be even more! Currently there are 23 APEX sessions listed (including mine - search for application express), but there's still a voting going on. You can vote for your favorite APEX sessions at Oracle Mix.

Anton Nielsen and Raj Mattamal's presentations I saw at ODTUG and I liked them very much. I'm sure the others will be nice to follow as well... so, vote!

Great to see so many APEX candidates!

Well alot of people have been giving day by day reports about this years ODTUG (where do they get the time). So I figured I'd just post a weekly roundup on the proceedings.

ODTUG

The ODTUG 2008 event itself was alot of fun and very well organized.
From Tom Kyte's opening keynote , which was a very cool and non technical , looking at why we always need to question how and why we do the things we do because the way you used to do it might not be the right way anymore and it's our job to always make sure things are done in the right way.

To the closing night get together, complete with band , fortune tellers and beads the event was just very interesting with the right amount fun to keep everybody looking forward to what comes next. And the infrastructure of the event was put together so that getting to the sessions or events you needed to took the minimal of effort or fuss. I recommend this event to anyone interested in any of the tools that Oracle provides.

APEX @ ODTUG

All I can say is "WOW"!! I knew people are using and interested in APEX but the amount of interest and usage is amazing. Almost every APEX session had a full room and many were standing room only. Many times I would just end up standing outside the door looking in just so I wasn't taking up a seat , that's the type of guy I am ;). I would say everybody at the event was surprised at the interest and energy of the APEX crowd including the APEX crowd itself.

During the sessions I'm amazed at the things people have done with our product, both from the people that just use the stock out of the box features. To applications that don't look or act like APEX at all until you look at the URL in the browser.

Both of these scenarios are perfect examples of the awesome cross section of APEX developers and uses, from the business user just trying to solve a business problem in the quickest / cheapest / reliable way around, to the advanced developer using custom javascript and/or third party libraries to provide APEX based RIA. APEX runs the gamut. Trying to be everything to everyone is hard and from everything I saw at ODTUG I would say that APEX is doing a very good job of it.

Carl @ ODTUG

Well here I have and will to be my own worst critic , unless someone else wants to jump in just try and make it constructive. I'll start with where I felt things were bad, and then mention the good. I wasn't as happy with my presentation as I should have been, and it was 100% my own fault, I will do better next time.

The Bad.

Two things I learned for giving presentations.

1. Never rewrite your whole presentation the day of the presentation. This seems such an obvious statement but this was my second presentation ever and I've made this same mistake twice now, I will not do it again.

2. Make sure that your mic is adjusted correctly before you start. Trying to adjust a mic in a very hot room 5 minutes into you presentation , while already a bit nervous, wastes too much time, makes the presenter even more nervous and compounds the problem. Next time mic will be right on the collar and everything will be perfect.

The Good

Rewriting my presentation was the right thing to do, I just should have done it earlier. The presentation I had , which I will eventually show , was very flashy and whizbang and would have been useless to pretty much everybody, though would have made me look great ;).

What I wanted to do ,and did, was show people some nuts and bolts examples of how things can be done in APEX, not how to fix or build particular things but to give people ideas of what can be done. Using an interactive report as the example I went through the features that are in APEX that allowed us to build them.

It ended up being a short and sweet session (48 min) but the room was very hot and it was the end of the day so I think the timing was near perfect in that respect. And I had enough people come up to me afterwards to ask questions and/or ask for the application that I think I did fairly well.

Thanks to everybody that attended, it does make one feel good to know that so many people are interested.

One thing I will do next time is Dietmar's suggestion , and how he did his session, which is to use screencasts of the functionality, live demo's are great but a screencast will show the same thing without the issue of having to jump back and forth between applications.

After a bit of cleanup I'll be putting both the slides and the application out for everybody to take a look at , plus to ODTUG so they can host on their site as well.

New Orleans

This was my first time to New Orleans and I had a real nice time. There were some very good restaurants and watering holes and I only had time to sample a few of each. The French Quarter was very cool with some of it's old style architecture. Bourbon Street was a experience in itself , and considering how crowded it was on just a random week I couldn't imagine being there during Mardi Gras, it must be insane.

Being from Vegas we have a joke , "Sure it's 120 f (50 c) but it's a dry heat!" , if New Orleans taught me one thing , that is not a joke! Vegas might be 120 but New Orleans at 82 felt alot hotter. I will tell that joke again in the future, but it will be a cold shiver down my spine as I remember what a non-dry heat feels like ;).

Lakers @ ODTUG

Congratulation's Boston.

Game 6 just happened to be the same night as the APEX meetup , which made going to a place with a TV mandatory. I was the only Laker fan in attendance at the APEX meetup and I remember all the names and faces of you haters :D

Hand grenade

A horrible yet intriguing drink, the name should be enough to keep you away from it, you have been warned.

Overview

At events like this it's the people that make it worthwhile , both the people in attendance and the people giving the session's, and this years ODTUG is no exception. Some of the most interesting ideas / questions / comments come outside the sessions, though the sessions are the catalyst. I learned quite a few new things about APEX and how people use it , along as with some other Oracle technologies and can't wait to start putting this information into action.

If you want more detailed accounts try searching through APEX Blog Aggregator or the Oracle News Aggregator there are many postings with much more detail on specific sessions.

I'm recovering from the States, but not too long. My next presentations will be in the Netherlands.

From September 2-4 we have our European APEX Training Days. John Scott and myself will provide an open training on various topics (see the agenda in the above link). The last two trainings (in London and Brussels) went well. The only requirement to attend the training is to register and to have a little bit experience with APEX. We start from a knowledge based on the 2-day development guide.

If you want already a taste what APEX can do for you or you just want to have some fun, you can attend a FREE seminar on the 1st of July. In the evening I'll give a talk for Ordina Netherlands.

I'll start with a demo of APEX and depending the level of the audience, talk about Best Practices. After the break I'll talk about the latest and greatest of APEX. If you want to attend register here.

From the Ordina NL website (in Dutch):
--
De toekomst voor APEX?

Dimitri Gielis laat het je zien!

Waarschijnlijk ben je wel bekend met HTML-DB en de opvolger APEX. Het lijkt misschien leuk voor kleine en eenvoudige applicaties. Maar wij bij Ordina Oracle Solutions denken dat we meer kunnen met APEX en willen dat graag met jou delen.

Dimitri Gielis van Apex Evangelists laat je deze avond zien hoe de toekomst van APEX eruit ziet. Met het laatste nieuws van de ODTUG Kaleidoscoop erin verwerkt. Dimitri Gielis is een internationale expert op het gebied van APEX. Ook op de ODTUG in New Orleans zal hij spreken over APEX en in discussie gaan met experts op het gebied van Oracle ADF.

Dimitri zal een korte inleiding en demo geven en daarna dieper in gaan op de best practices op zijn eigen unieke manier. In het tweede deel een vooruitblik op wat gaat komen op het gebied van APEX. Gaat java de hete adem van APEX in de nek voelen?

Wij bieden jullie de gelegenheid om deze sessie GRATIS bij te wonen!

Deze unieke kennissessie vindt plaats op 1 juli vanaf 17:30 uur in de Media Plaza te Utrecht. Het programma duurt tot 21.00 uur. Wij zorgen voor het buffet. Inschrijven kan op de aanmeld pagina.
--

See you there or maybe on our training!

During my presentation of "Advanced Charting in APEX" during ODTUG I got a question how to specify always the same width of a column chart in APEX.

I had a look into this, but couldn't find an easy way to do it in Anychart 3 (the current version in APEX).

If you look at the Anychart 3 documentation then you'll find this section:

There's not really a width you can specify for every bar. Like I said during the presentation, it would be extremely difficult to do that for Anychart, as it doesn't know how many data it will need to show. The only option I see at the moment is to dynamically set the column_space and block_space, depending on the number of values you have. Like I gave in my example the dynamic scaling (maximum of the chart); you create an hidden item and give it a value. You use the hidden item in your Custom XML to define the left_space, right_space, column_space and block_space.

If somebody else already did it another way, feel free to add a comment.

The last day... the most difficult one for me. Meeting all the people at the conference and listening to all sessions in the previous days is very nice but also very demanding and intense. The tiredness, the jetlag and all those expressions you start to feel.

So giving a presentation on the last day is a challenge. From early in the morning Lucas and I were on stage for the APEX vs ADF shootout. Some people said there were 300 people in the room, but others counted around 150, so let's say we had a lot of people ;-)


I like to be on stage with Lucas, he's a very knowledgeable person and amazingly objective about the tools. Whereas I'm the very *pro* APEX one ;-) Some ADF people told me I need to balance more, which I might do in the future as there are also some nice features in ADF. Not that I'll use ADF in the future, but it's not bad to know more about it.

In the Netherlands we did the presentation a little bit different. We had more time so we had more time to do Q&A. This time I spent quiet a lot of time on Interactive Reports as I thought it's one of the nicest features and less time explaining how I build the complete application. After the session I got some questions how I did things in the Survey app. I'll try to blog about the survey application in the next weeks.

In the Netherlands I went first, but this time Lucas presented first. I changed my style a bit so I followed the same steps as Lucas. I started from scratch and at one time I thought to just build the whole application in 30 minutes, but then I got an error. I didn't use triggers in the app I created in December, but when I followed the wizards during the demo, I forgot about that and said to APEX to use a trigger. Of course the trigger didn't exist. After I created it, the first demo worked. Of course, my second demo didn't work anymore. I normally don't create a trigger and just use the sequence, but as I created the trigger in the first demo, I overwrote the value.
I got a bit nervous when I got the error as I didn't know what was happening (although the errors where clear and related to the data model). Of course I blamed Lucas for the error ;-)
I could fix it and the demo worked, but only in the evening I knew exactly why it went wrong. In the morning everything still worked, but because I changed the order of my demo's (first creation of app from scratch of showing the app I created in December) it didn't work from the first time. It's always a risk to do live demos, but I like it so much, so not doing it is not an option for me ;-) You find the application here (here my blog post after the shootout in December).

I felt better after my presentation in the Netherlands. I think I should have done it more like when I did it in December and do it a bit different, but it's always after the presentation you know it. I wonder what the people thought about it.

Literaly after the session the adrenaline went away and I started to feel the tiredness even more. I went with some friends to a nice restaurant next to the Mississipi river. There were some great pictures taken... I hope I'll see them on some other blogs! Afterwards we walked a bit around and went back to the hotel. In the evening I crashed and had a nice sleep.

The next morning I flew back to Belgium over Chicago. Coincidence, but Marcie, who's doing the OBE's of APEX on OTN was on the same flight!