Oracle Apex Notebook

APEX4 Password Strength Meter Item Plugin

Written by Paulo Vale on Friday, July 16, 2010

Download Oracle Application Express 3.2

Written by Paulo Vale on Saturday, February 28, 2009

The new APEX release 3.2 is finally available for download. This new version covers essentially the Oracle Forms to APEX converter, but also has some extras regarding security.

Download Oracle Application Express 3.2

Oracle Application Express 3.2 Release Notes

Application Express Documentation and Tutorials

Oracle Forms convertion to Apex is here... almost!

Written by Paulo Vale on Thursday, February 12, 2009

Finnaly fresh news... the apex.oracle.com demo server was upgraded to Application Express 3.2.0.00.21. You can't download it yet, but you can test it online. Very soon we'll have a production release available.

You can read all about Oracle Application Express Release 3.2 new features details here.

Additional info on Joel Kallman's post about the new release.

jQuery: 3rd Birthday and new release 1.3

Written by Paulo Vale on Thursday, January 15, 2009

jQuery is the way of the future in what concerns JavaScript framework integration with Oracle Apex. In the last months, several Oracle Apex Bloggers have posted nice examples to take Apex applications interfaces to another level taking advantage of jQuery functionalities.

Today, on the project’s 3rd birthday, the jQuery development team has announced the release of jQuery v1.3, the latest and greatest release of jQuery which includes a lot changes and new features like:

• Sizzle Selector Engine
• Live Events
• jQuery Event Object Overhaul
• HTML Injection Rewrite
• Offset Rewrite
• No More Browser Sniffing

Aditional details on jQuery 1.3 new features can be found via the release notes document.

Build an image gallery using APEX

Written by Paulo Vale on Friday, December 19, 2008

This time, I'll show how to build a simple image gallery using Oracle Application Express (APEX) and taking advantage of Jquery framework capabilities. But first of all, follow the link to see the magic happening ;)

There are several slideshow or gallery plugins for Jquery. You can choose the one you like more and re-adapt the code to fit the plugin requirements. My choice was Pikachoose.

Pikachoose is a lightweight Jquery plugin that allows easy presentation of photos with options for slideshows, navigation buttons, and auto play.



Lets begin creating a simple database structure to store the images.

Create a sequence to be used by image id:

<br />CREATE SEQUENCE "GAL_IMAGES_SEQ"<br /> INCREMENT BY 1<br /> START WITH 1<br /> MINVALUE 1<br /> MAXVALUE 999999999999999999999999999<br /> NOCYCLE<br /> NOORDER<br /> CACHE 20<br />/<br />

Create a table to store the images:

<br />CREATE TABLE "GAL_IMAGES"<br /> ( "IMAGE_ID" NUMBER NOT NULL ENABLE,<br /> "IMAGE_NAME" VARCHAR2(4000),<br /> "MIME_TYPE" VARCHAR2(4000),<br /> "IMAGE" BLOB,<br /> CONSTRAINT "GAL_IMAGES_PK" PRIMARY KEY ("IMAGE_ID") ENABLE<br /> )<br />/<br /><br />CREATE OR REPLACE TRIGGER "BI_GAL_IMAGES"<br /> BEFORE INSERT<br /> ON "GAL_IMAGES"<br /> FOR EACH ROW<br />BEGIN<br /> IF :NEW."IMAGE_ID" IS NULL<br /> THEN<br /> SELECT "GAL_IMAGES_SEQ".NEXTVAL<br /> INTO :NEW."IMAGE_ID"<br /> FROM DUAL;<br /> END IF;<br />END;<br />/<br /><br />ALTER TRIGGER "BI_GAL_IMAGES" ENABLE<br />/<br />

Now on APEX, following the wizard, create a data entry form based on "GAL_IMAGES" table.


Go to Shared Components>Application Items and Create an Application Item. Ex: G_IMAGE_ID.

Create an Application Process to retrieve the image from the database. Identify the point at which this process will run as "On Demand". The point of "On Demand" is that it will only fire when requested by a page process.

<br />DECLARE<br /> l_mime VARCHAR2 (255);<br /> l_length NUMBER;<br /> l_file_name VARCHAR2 (2000);<br /> lob_loc BLOB;<br />BEGIN<br /><br /> SELECT "MIME_TYPE", "IMAGE", "IMAGE_NAME", DBMS_LOB.getlength ("IMAGE")<br /> INTO l_mime, lob_loc, l_file_name, l_length<br /> FROM "GAL_IMAGES"<br /> WHERE image_id = :G_IMAGE_ID;<br /><br /> OWA_UTIL.mime_header (NVL (l_mime, 'application/octet'), FALSE);<br /><br /> HTP.p ('Content-length: ' || l_length);<br /> HTP.p ('Content-Disposition: filename="' || l_file_name || '"');<br /> OWA_UTIL.http_header_close;<br />-- Download the BLOB<br /> WPG_DOCLOAD.download_file (lob_loc);<br />END;<br />

Now create the page where you want to put the image gallery and then, Edit Page > HTML Header:

<br /><link type="text/css" href="#WORKSPACE_IMAGES#pikabottom.css" rel="stylesheet"><br /><script type="text/javascript" src="#WORKSPACE_IMAGES#jquery-1.2.6.min.js"></script><br /><script language="javascript"><br /> <!-- $(document).ready( function (){ $("#pikame").PikaChoose(); }); --><br /></script><br /><script type="text/javascript" src="#WORKSPACE_IMAGES#pikachoose.js"></script><br />


On the same page, create a PL/SQL Dynamic Content region:

<br />DECLARE<br /> CURSOR c1<br /> IS<br /> SELECT "IMAGE_ID"<br /> FROM "GAL_IMAGES";<br />BEGIN<br /> HTP.p ('<ul id="pikame">');<br /><br /> FOR gallery_rec IN c1<br /> LOOP<br /> HTP.p<br /> ( '<li><br /> <img src="f?p=&APP_ID.:0:&SESSION.:APPLICATION_PROCESS=proc_show_image:::G_IMAGE_ID:' || gallery_rec.image_id || '" />/></li>'<br /> );<br /> END LOOP;<br /><br /> HTP.p ('</ul>');<br />END;<br />

And run...

Oracle SQL Developer 1.5.3 released

Written by Paulo Vale on Friday, December 12, 2008

I've just noticed that, the new version of Oracle SQL Developer (1.5.3) is now available for download.

Reading the release notes I can see the major functionality introduced, beyond the traditional list of bug fixes, is the translation to Japanese. The good news is that the next SQL Developer 1.5.4 release will be translated to other 7 languages: Spanish, Italian, German, French, Brazilian Portuguese, Simplified Chinese and Korean.

My UKOUG 2008

Written by Paulo Vale on Thursday, December 11, 2008

Last week I went to Birmingham UK to attend the UKOUG 2008 conference. João came along with me and I can speak for both of us when I say that we had a great time. We found Birmingham to be a very clean and organized city and it was also good because we managed to stay in London for the weekend where we had the time to visit some of the most famous touristic attractions.


The conference itself was very good. There weren't that many APEX related sessions, but there were other interesting topics regarding SQL Developer and Oracle Database like performance, security, globalization and PL/SQL development.


In what concerns the APEX sessions...

I liked very much Roel's presentation with the title "(Re)Developing a logistic application in APEX in the real world". I think he made this presentation at OpenWorld, but I didn't had the chance to attend at that time. With this presentation Roel showed us some tricks using jquery, ajax and json to add extra functionality to an APEX application. Really nice job!

John also presented a session that I attended for the first time with the title "Application Express Best Practices". There were some nice tips there and most of them used by few Apex developers and system administrators. I think John is a very talented speaker... I had this opinion from a session I had the opportunity to attend in the past, and I could confirm it this time.

There were two other sessions by Dimitri Gielis and by David Peake. Dimitri's session ("Mastering Charts in APEX") was similar with the one he presented at OpenWorld. He had to rush in the best part of the presentation, when he was showing Anychart 5 functionalities and advanced customization, with no apparent reason since the room was clear for at least more 45 minutes after the session, and the presenter and the attendees were interested in continue with the presentation! David also rushed is presentation about "Oracle Application Express Now and in the Future". He managed to show (live demos included) Websheets, Forms Conversion and Interactive Reports in only one session!!!

It was very nice to find some familiar faces like David, John, Roel and Dimitri that I had the opportunity know at the OpenWorld last September, and to meet Anthony, the newest member of the APEX development team. All nice guys :)

UKOUG 2008 Conference

Written by Paulo Vale on Wednesday, November 26, 2008

This year I'll attend to the UKOUG 2008 Conference in Birmingham from 1st to 5th December. The conference agenda looks interesting, although not having so many APEX related sessions.

It seems that there are some nice bars in Birmingham :) and Dimitri is planning an informal APEX Meetup on Monday after the Opening Party. It's a good opportunity to meet people from the APEX community. You can follow the details on this Oracle APEX forum thread.

My agenda will be around Database Development, APEX and FORMS:


Monday
Advanced SQL for PL/SQL Programmers
Oracle Application Express Now and in the Future
Oracle Forms: Features and Future
Oracle 11g/10g Developers: What You Need to Know
UKOUG 2008 Opening Party


Tuesday
Web 2.0 in Buisness or Education . Which is the better marriage?
Application Express Best Practices
Change Change Change
Oracle Forms – The New World
Weird PL/SQL
Database Development with Oracle SQL Developer: An Overview
Community Focus Pubs


Wednesday
Oracle SQL Developer: Focusing on a Few Advanced Features
Obtaining and Interpretting Execution Plans using DBMS_XPLAN
Migrating Oracle Forms to SOA and J2EE (Oracle ADF)
Advanced Oracle Application Express Tips and Techniques
Bringing Oracle Designer and CASE repositories back to life – a return on your investment
Visual Data Modeling with Oracle SQL Developer
25th Year Celebration Party


Thursday
Mastering charts in Oracle Application Express (APEX)
Being Steven Feuerstein
Designing PL/SQL applications
Consolidate your Desktop Databases to Oracle 11g
(Re)Developing a logistic application in APEX in the real world
Securing APEX an application on a Microsoft platform
Performance Tuning ApEx Applications


Hope to see you there!

Timepicker and APEX

Written by Paulo Vale on Tuesday, November 18, 2008

ClockPick is a jQuery time picker plugin. As I showed in previous posts, it's fairly easy to integrate third party javascript components with APEX.

A demo of ClockPick working with APEX can be found here.

How to do it?

1- Upload files to your APEX server. You can upload them directly to the server filesystem or use static files in Shared Components;

jquery.clockpick.1.2.4.js
clockpick.1.2.4.css
jquery-1.2.6.min.js

2- Include reference to the CSS file and ClockPick plugin JS file on the page template between HEADER tags or directly in you page properties, Header section:

<br /> <br /> <link rel="stylesheet" href="file_path/clockpick.1.2.4.css" type="text/css"><br /> <br /> <script src="file_path/jquery.clockpick.1.2.4.js"><br />


3- Include reference to the JQuery library file (again in page template or page header):

<br /> <br /> <br /> <script src="file_path/jquery-1.2.6.min.js"></script><br /> <br /> <br /> <br /> <br /> <br />

4- Now the script to make it all happen. Note that this function is binding P9_TIME text element. You can reference the many items you want. You can also change the hour range to be displayed and execute a function after the selection is done. Read ClockPick website to get detailed information about all the configurations available.

<br /> <br /> <script type="text/javascript"><br /> $(document).ready(function() {<br /> function cback( time ) {<br />         var c = $("<div style='color:red'>Time Entered: " + time + "</div>");<br />         c<br />         .hide()<br />         .insertAfter( this )<br />         .fadeIn(1000)<br />         .fadeOut(1000);<br />     }<br /> <br /> <br />         $("#P9_TIME").unbind().clockpick({<br />             starthour : 8,<br />             endhour : 20,<br />             showminutes : true}, cback);<br />         <br /> <br /> });<br /> <br /> </script><br />

ORA-01403 when viewing BLOB data in a report (APEX)

Written by Paulo Vale on Friday, October 31, 2008

This post is almost a note to myself but I hope to help others when you Google for this little problem.

One of the features introduced with Oracle Apex 3.1 is the Declarative BLOB support. About this subject there is already a good article on Oracle By Example (OBE) site: Defining and Viewing BLOB Data in Oracle Application Express 3.1.

Last Friday I lost almost an hour just to have a simple classic report with an image column. I was getting a ORA-01403 error because simply I wasn't referencing the Table Primary Key in my SQL Query!

<br /> report error:<br /> ORA-01403: no data found<br />

These are the steps needed to have the image column successfully displayed:

Report Source Query
Important Tip: You must include the Primary Key column in your query.

<br /> SELECT my_id, DBMS_LOB.getlength ("MY_LOGO") my_logo<br /> FROM my_table;<br />

Report Column Attributes

Format Mask Attributes
Important Tip: Database table and column names are case sensitive.

Tabular Form Element
Include the reference to the Schema, Table and Blob Column.

Oracle Develop 2008 Hands On Labs

Written by Paulo Vale on Friday, October 31, 2008

In case you have missed Oracle Open World 2008 and specifically the Oracle Develop Hands On Labs, this post is for you.

The Oracle by Example (OBE) is a site that provides hands-on, step-by-step instructions on how to implement various technology solutions to business problems. The tutorials presented at Oracle Develop 2008 Hands On Labs, part of Oracle OpenWorld 2008 are now available at OBE site.

Related to Oracle Application Express there are some interesting topics deserving some attention. Some tutorials are basic skills level but you will also find some advanced tutorials. I'm sure we can all learn something with them and that's why I made this list.


Under the topic "Extending the Oracle Application Express Framework with Web 2.0" you will find two excellent tutorials on how to incorporate JavaScript and AJAX into your Apex applications:

Incorporating JavaScript into your Application
Summary:
- Change the value of form elements
- Create client-side JavaScript validation
- Enable and disable form elements
- Hide and show form elements

Using AJAX into your Application
Summary:
- Add a dependent select list
- Set multiple items based on another item
- Add rollover item help


The topic "Creating High Fidelity PDF Reports with Oracle Application Express" takes Apex reporting to another level integrating Oracle BI Publisher functionalities:

Creating a PDF Report with Multiple Queries
Summary:
- Create a report query with multiple queries
- Create a report layout using Oracle BI Publisher Desktop
- Invoke a report from an application page

Including Dynamic Images in Your Report
Summary:
- Add and load a BLOB column
- Create a function that performs the BLOB to CLOB based 64 encoded format conversion
- Create a report query and layout
- Invoke the report from an application page

Storing and Accessing Reports in the Database
Summary:
- Create a table to store your reports
- Generate and store a report in the database
- Access the stored report in the database

Creating Reports With Dynamic Layout Selection
Summary:
- Produce multiple report layouts
- Create selection criteria items and processes for report generation
- Run a report dynamically


The topic "Maximizing the Capabilities of Oracle Application Express - Interactive Reports" is like a showcase on using and manipulating Interactive Reports in Oracle Application Express 3.1:

Using Interactive Report Regions
Summary
- View a record in your report
- Search for information in your report
- Manipulate the interactive report using the actions menu
- Manipulate the interactive report using column headers

Building and Customizing an Interactive Report
Summary
- Create an Application with an interactive report
- Manipulate and customize your interactive report

Utilizing Advanced Interactive Report Region Techniques
Summary
- Reset your Interactive Report Manually
- Create a declarative filter
- Create a derived predefined filter


The topic "Building a Functional Application Using Oracle Application Express 3.1" is appropriated to beginners and will show how you can rapidly create an application using Oracle Application Express:

Manipulating Database Objects
Summary
- Create a table
- Modify a table

Creating and Running an Application
Summary
- Create an application
- Edit Application Objects
- Create List of Values (LOVs)

Adding Additional Components to your Existing Application
Summary
- Add and modify a report, form, chart, and calendar
- Create LOVs and validation items
- Apply a new theme
- Update a navigation list

Adding Security to your Application
Summary
- Create a user
- Limit access to a user


Other interesting subjects, not directly about Apex, but somehow related and not to be missed:


Migrate Your Third-party Database to Oracle Database 11g Today
Migrating a Microsoft Access Database to Oracle Database 11g
Migrating a Microsoft SQL Server Database to Oracle Database 11g
Migrating a Sybase Database to Oracle Database 11g

Managing Your Database with Oracle SQL Developer
Managing your Database with Oracle SQL Developer - An Overview
Oracle SQL Developer 1.5 New Features
 
Source Code Control and User-defined Extensions in Oracle SQL Developer
Using Source Code Control in Oracle SQL Developer
Creating User-defined Extensions in Oracle SQL Developer

Oracle SQL Developer Data Modeling - Early Adopter Release

Written by Paulo Vale on Friday, October 10, 2008

The long waited Data Modeling functionality for Oracle SQL Developer is here. Oracle SQL Developer Data Modeling runs on Microsoft Windows XP and Vista, Linux and Mac OS X.

This is a first Early Adopter release, not a final product and I'm sure that the team behind the development, would appreciate all the feedback we can give them.

My OpenWorld 2008: 25 Set

Written by Paulo Vale on Saturday, September 27, 2008

The day started too early after the big party the night before. It was that last day of OpenWorld 2008 and there were lots of people going home.

Session 1: Dispelling myths about Apex – John Scott

I surely didn’t expect to see a full room, being this session at 9 AM and having the big party the night before. I guess Jes is even more popular now that he has his own book :) I bought the book but didn’t have the chance to read it yet, but judging by the index I think it’s worthy to buy it.

I found this session particularly interesting. It wasn’t a technical session with development tips but was very useful for companies working with customers that are a little afraid to go for APEX.

The session started we an original set of slides that John asked the audience to read loud. It was a fun moment.  Then there was an interesting timeline animation with the history of APEX.

Basically John introduced some slides with common statements on APEX alleged weaknesses. Questions like “Is it a rewrite of WebDB?” or “Is it supported by Oracle?” or even “Is it just an Excel replacement.

 
I sure want a copy of that slides :)

Session 2: Building a Web 2.0 Interface with Oracle Application Express - Mark Lancaster

With this presentation, Mark Lancaster explained some tricks on how to integrate ExtJs with Apex, taking Apex to the next level in terms of Web 2.0 interface. You can find his application and see the nice interface by yourself at http://apex.oracle.com/pls/otn/f?p=200801.


For the OpenWorld closing there was the “It’s a Wrap!” party by the beautiful Yerba Buena Gardens. Time for the last drinks and food at Openworld and a nice chat with Roel Hartman, Francis Mignault and Patrick Bonneville.


The day finished with a nice dinner in an Italian Restaurant at Fisherman’s wharf.

My OpenWorld 2008: 24 Set

Written by Paulo Vale on Friday, September 26, 2008

It was a big day… Larry Ellison’s keynote with the X thing , my presentation and the Appreciation Event.

I also had the opportunity to have preview demo of APEX 4.0 features at the Demo Grounds.


Session 1: Soup-to-Nuts RAD development using Oracle SQL Developer and APEX – Mike Hichwa, Kris Rice & David Peake, Oracle

I had lots of expectations for this session because of the new SQL Developer data modeling feature. I think it´s a feature that we all are waiting for. There was a small demo on that in this session and I quite liked it.

The big question was: will SQL Developer replace Designer? The answer was no, because this is not exactly the same as Designer, but I’m feeling that a lot of people will leave Designer :)


Session 2: Building commercial SaaS (Software as a Service) applications with Apex

This time I wasn’t on the audience side, but on the stage along with representatives of other companies, some of them large companies with large projects built in APEX.

On the audience we could see many people thinking in starting doing business with applications built with APEX, which was nice. It turned out to be very good to share our experience with each other with different methods of approaching customers, building and securing applications.

This was a very interactive session with people asking a lot of questions and I think it went quite well since I had some positive feedback.


At the end of the night I went to the Appreciation Event in the Treasure Island. And that was really such a big event. People, food, drinks, music… It was fun.

My OpenWorld 2008: 23 Set

Written by Paulo Vale on Wednesday, September 24, 2008

The first session of the day was schedule to 11:30 AM. I had some free time that I used to get around and see what was happening.

I went to Moscone North and after blogging a bit on the couches, went to the Unconference section and then the bookstore. I couldn’t find John Scott’s Pro Oracle APEX book, but later at night he told me that the book was there, so maybe I’ll go to the bookstore again tomorrow, if I have the time. Then I went to Moscone South into the exhibition hall, just for a really quick visit.


Session 1: Writing custom authentication scheme for Oracle Apex – Raj Mattamal

Wow... Raj Mattamal must be the fastest speaker at OpenWold :)

His session was around getting custom authentication to work between workspaces.

First he started by quickly explaining the authentication methods provided be APEX. Then he focused on the custom authentication to provide a method to maintain sessions between applications in the same workspace using a cookie. This was a single APEX instance approach that explained the way page sentry function works.

After that Raj explained how he managed to have something like a single sign-on feature across different workspaces and logging out from each individual application.

I really enjoyed this session and the theme was also very interesting.


Session 2: The power of APEX repository – Patrick Wolf

Patrick Wolf made a very original presentation. Slides were very nice with animations and strong pictures, and there was that moment with the lights off and sounds of birds singing, like if we were in the Garden of Eden :)

The presentation itself was about playing with APEX repository: the dictionary views, the applications and workspace metadata and the logging/monitoring.

Patrick talked about the advantages of using the APEX repository for tasks like: quality checks, application documentation and application sitemaps. Then he introduced his Apex Essentials, which is a tool for application quality check. I’ve tried this tool myself and I can just advice you to do the same because it will help you detect common application errors.

Then he talked a bit about his known ApexLib project, which is a tool that adds some functionalities that APEX doesn’t support like: cascading LOVs; browser checks; validations for date picker; tabular forms validation; etc.

The session finished with Patrick talking a little about monitoring application activities.


Session 3: Building large commercial applications with Oracle DB 11g and APEX – Dennis Vanill and Eric Scholer from PAETEC

With this presentation with could see a fine example of how APEX can manage with large projects. They have migrated from a 14 years old application with almost 800 databases. A big project!



They have also developed something that they call Pinnacle APEX Framework to customize their application actions and help with repetitive tasks when developing.


Session 4: Zero-cost business intelligence using Oracle DB 11g and APEX 3.1 - Jim Lancer, Paetec

Essentially this was a demonstration of capabilitiest that can be found in Interactive Reports and Flash Charts and how they can help analyze enterprise data. PAETEC also added some customizations to get the most of these functionalities.


Finally I had a decent night of sleep. I think the beers at Apex meetup on the end of the night helped a little :)

My OpenWorld 2008: 22 Set

Written by Paulo Vale on Tuesday, September 23, 2008

Today I have changed my agenda and attended two sessions that weren’t initially in my schedule. The first was the one presented by Dimitri Gielis about working charts in APEX and the second one was about mashups and integration with APEX by Simon Boorsma.

Before that I went to Moscone North to watch a bit of the keynote. You can’t believe the number of people that were there! The following picture it's outside the room, next to some big screens.

Session 1: Advanced charting with APEX – Dimitri Gielis

The presenter tried to make this session the most interactive possible with some funny slides and he managed quite well to do it. The session idea was to show that when using flash charts in APEX, in what concerns customization, you are not limited to the options presented on the chart properties page. You can customize your charts even more if you analyze and modify the XML behind it.

Dimitri made live demo of things like making drilldown charts and reports, protecting the links with checksum, adapt the chart scale, add copyright text, change labels orientation and other tricks.

At the end, he announced that his company has a partnership with AnyChart.com to provide an upgrade kit to Anycharts version 5 for the APEX 3.1. Has you all surely know, actual APEX version uses Anycharts 3 when you create a flash chart from the traditional wizard and uses Anycharts 4 when you create a chart from an Interactive Region.

He got lots of questions and the time flew away. So, he had no time to explore the final idea and the advantages of having Anycharts 5.

Session 2: Converting from Oracle Forms to APEX – David Peake

There is phenomenon called APEX buzz. You’d be impressed with the number of people in the room witch never had seen APEX before. I’m sure they all were Forms people… I can tell that, beyond the obvious, by the questions asked. From that point of view this was an excellent marketing session for APEX, with David using and abusing of interactive reports features.

I have myself a background on Oracle Forms too, and when watching that live demos, I was thinking in how impressed I would be if I didn’t knew APEX before. Also, David has great skills as a public speaker.

The presentation was based in a featured expected for the next APEX 3.2 release, which is a tool to import Oracle Forms .fmb files to APEX. With the demo we saw two Oracle Forms .fmb files being imported to APEX and transformed into web forms interface.

As David kept saying, this is not a “silver bullet” solution and it’s not also a Forms emulator. Each conversion will need hours of additional work after the import, but this tool is meant to help with the first steps. But again, Oracle Forms is not going away, so you should only considerer the change if you really have a business need.

There are however several advantages on moving to APEX, and David Peake referred to some of them like: the modern web 2 computing; out of the box functionalities like Interactive Reports and Flash Charts; easy skill transition for Form developers; and of course the unbeatable price (it is a no cost database feature).

I didn’t count the number of questions coming from the audience, but it was by far the most participative session I attended until now.

Session 3: Apex Mashup – Simon Boorsma

One of the advantages of APEX is that it’s easy to integrate third party functionalities like mashups. A mashup is obtained collecting data from different sources and combining them into a unique result, like for example google news does.

In this session, Simon Boorsma showed us nice examples of mashup integration with APEX. He uses google maps and amazon store APIs to collect information into an APEX page. He also made an example of using dapper to get flickr photos.

Session 4: Web 2 development with APEX – Carl Backstrong

This was by far, the most technical session until now. Carl talked about the best way to debug javascript and Ajax, combining two indispensable tolls: Firefox and Firebug.

He worked a lot of interesting examples with Ajax, like getting data from the database, asynchronous posting with apex.ajax, the use of arrays and of course JSON.

By night, party with OTN night at Hilton Hotel. Also, a little jump into InstallFest with Unbreakable Linux and Oravle VM. I got one of that t-shirts with the penguin on the back for you João Oliveira ;).

My OpenWorld 2008: 21 Set

Written by Paulo Vale on Monday, September 22, 2008

Today I had the chance to attend 4 sessions. Two of them were Hands on Labs from Oracle Develop. Also, it was nice to put some faces in the names I’m used meet on the net. I met Carl Backstrong, Joel Kallman, David Peake, Dimitri Gielis, Francis Minault, John Scott and Patrick Wolf.

Session 1: Hands-on Lab: Extending the Oracle Application Express Framework with Web 2.0 - APEX Development Team

There was a full room of interested audience attending this session. I guess this topic of using Javascript and Ajax is the hardest part for a PL/SQL developer who starts using APEX.

The session consisted in following step by step tutorials to perform tasks like changing values in form elements before submitting the page, creating client side validations and enabling/disabling form elements, among others.

The good news is that this and every Hand-on Lab tutorials will be published on OTN some weeks after the conference at http://otn.oracle.com/obe, and believe me, there are some nice tricks you can use to upgrade your applications.

Session 2: Using Oracle Database 11g and Oracle Application Express to Change Business Practices and Realize ROI - Anton Nielsen, Concept 2 Completion; Diana Suteu, Harvard Pilgrim Healthcare

I liked this session very much. Anton Nielsen, along with Diana Suten explained how they implemented in a short period of time (one month I think) a solution for subscribing insurances over the net.

They used agile methodology taking advantage of the client human resources to get the project done in such short time. Soft requirements definitions redefined over the project, prototyping and testing onsite and even letting the client employees to change themselves some parts of the application like forms and reports labels.

This was possible taking advantage of some of the APEX features like having a web based interface allowing development anywhere and having super users being able to act as developers.

Session 3: Go global with APEX – Joel R. Kallman

For me, this was the best presentation of the day. If you are thinking in having your applications available in more than one language and have the opportunity to attend this session in some other event, do yourself a favor: don’t miss it.

In a very interactive session with risky live testing, Joel Kallman showed us how to translate an application, changing language and altering numbers, currency and dates format.

Joel also revealed some of the features – globalization related – expected for the APEX 4.0 release like having options to define application timestamp format and application timestamp with time zone format, a modern popup calendar using jQuery and using client time zone.


I wish I had seen this presentation some months ago…

Session 4: Hands-on Lab: Creating "High-Fidelity" PDF Reports with Oracle Application Express - APEX Development Team

This was another Hands-on Lab session, where BI Publisher was integrated with APEX to generate complex PDF reports including charts and using custom made templates. An impressive demonstration of BI Publisher capabilities, but being such an expensive product it is very difficult to make a competitive APEX application using it.

This tutorial will also be available on OTN at http://otn.oracle.com/obe.


After the sessions I was so tired due to jetlag that I decided to go for a little rest at the Hotel. I felt a sleep and missed the blogger meetup.

Time for a little visit to Welcome Reception Party at Howard Street tent and Yerba Buena Gardens and the day was over.

One year blogging

Written by Paulo Vale on Sunday, September 21, 2008

Yes, that is right. This blog is one year old.

I thank all of you out there for coming around, reading what I want to say about APEX. Thanks a ton. I hope you will stick with me for the next year too.

Top 10 of most visited posts:

Check all checkboxes
Javascript Calendar Integration
Colorful web forms
Javascript Tooltip Integration
Substitution string and SQL Developer
Populate date field using javascript
Nice filter region template
Translating internal messages used by APEX
Turn Report into PPR Report
A real live APEX application

Most commented posts:

Javascript Tootip Integration
A real live APEX application
Navigate to previous page

San Francisco

Written by Paulo Vale on Sunday, September 21, 2008

I’m in San Francisco for the Oracle OpenWorld 2008 where I’ll be participating in one of the official sessions under the title “Building Commercial Software-as-a-Service Applications with Oracle Application Express”.

I’ve arrived yesterday around 6:30 PM after a tiring 18 hours long trip. I’ve started at Oporto Airport where I took a plain to Lisbon, then Philadelphia and finally San Francisco. As you can imagine, I’ve arrived completely exhausted.

It gets dark really soon in San Francisco. I took the BART to get to the city centre around 7 PM and it was completely dark outside.

After checking with the hotel, I took a short walk just to feel the city atmosphere and have a quick meal. The first impression I had was a nice, clean, organized, big city. Many people walking in the sidewalks, speaking numerous different languages. I’ve stopped some people to ask for directions and no one seemed to be from San Francisco… I guess San Francisco beyond an American city, it’s a World city. I’ve also noticed a bad thing: homeless people checking garbage cans and asking for money in the streets.

Today, the jetlag effect made me wake up at 2 AM. I forced myself to sleep a little bit more and managed to sleep until 5 AM. Considering that yesterday I woke up at 5:30 AM to start the travel and went to bed at 6:30 AM the next day (10:30 PM local time) I’ve been more than 24 h awake, excluding two or three small periods on the plain.

I have a full day with lots of sessions to follow and with the blogger meetup at the end of the day.

Keep checking this blog. I’ll try blog everyday during the OpenWorld.

Get server URL in Apex

Written by Paulo Vale on Monday, September 08, 2008

Some time ago, I had this requirement to send to the application end users, links to specific pages passing some parameters.

The problem was obtaining the full URL including the server's name and port where APEX was running.

The OWA_UTIL package contains utility subprograms for getting the value of environment variables. Specifically the OWA_UTIL.GET_CGI_ENV function returns the value of CGI environment variables, like REQUEST_PROTOCOL, HTTP_HOST, SERVER_PORT and SCRIPT_NAME, this last one to get the mod_plsql cartridge and the database access descriptor name.

Usage:

<br /> owa_util.get_cgi_env('REQUEST_PROTOCOL')<br /> owa_util.get_cgi_env('HTTP_HOST')<br /> owa_util.get_cgi_env('SERVER_PORT')<br /> owa_util.get_cgi_env('SCRIPT_NAME')<br />

You can quickly test these values creating a PL/SQL Dynamic Content region like this:

<br /> BEGIN<br />    HTP.p (   'Apex URL: '<br />           || OWA_UTIL.get_cgi_env ('REQUEST_PROTOCOL') || '://'<br />           || OWA_UTIL.get_cgi_env ('HTTP_HOST')        || ':'<br />           || OWA_UTIL.get_cgi_env ('SERVER_PORT')      || '/'<br />           || OWA_UTIL.get_cgi_env ('SCRIPT_NAME')      || '/'<br />          );<br /> END;<br />

Now to complete the URL you just need to append the well known f function with the right values. In case you have forget, here is the Apex f function URL Syntax:

<br /> f?p=App:Page:Session:Request:Debug:ClearCache:itemNames:itemValues:PrinterFriendly<br />