Oracle Apex Notebook

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 />

This blog in a cloud

Written by Paulo Vale on Friday, September 05, 2008

After reading this post by Jake Kuramoto, I was curious to see how Apex Notebook blog looks like in a word cloud. Here is the result:


Wordle is a service for generating word clouds from text sources like blog feeds. The words that appear more frequently in the source text are bigger in the final image.

Back to real life

Written by Paulo Vale on Monday, September 01, 2008

After 15 well deserved :) days of vacations in the sunny Algarve (south Portugal) I'm back to work. It is hard when I think that just two days ago I was at those wonderful beaches, eating fresh sea fish...

However, not everything went fine :( I just had one of the major sports disillusion, provoked by a bad referee decision.

I've just noticed that while I was away some things happened in the APEX world,  starting with the release of APEX 3.1.2 patch. Judging by the number of fixed bugs I think it is worthy to apply the patch. There are already reports saying everything went fine applying the patch.

A new version of OTN forums came out (again) but unfortunately there are still some problems. Fortunately there are people who can help us deal with some issues like the look and feel.

My agenda at OpenWorld 2008

Written by Paulo Vale on Monday, August 11, 2008

Essentially, at OpenWorld I'll be looking for everything related with APEX. Unfortunately it's impossible to attend all APEX related sessions, because (fortunately) there is a huge number of interesting sessions and some of them share the same time schedule. David Peake has built an application that puts together all APEX sessions (good work btw) which helps picking the sessions. Beyond the officiall sessions, I'll try to reserve some time to see what's happening at OpenWorld Unconference.

I'm also planning to go to the Blogger Meetup. It's a good opportunity to meet the faces behind the blogs and of course, drink some beers ;). This year, the Blogger Meetup is organized by Eddie Awad and will take place at ThirstyBear Restaurant and Brewery next September 21, 2008 at 7pm.

Back to OpenWorld agenda, here is my list:

Sunday, Sep 21

S298611, 10:30 - 11:30 at Golden Gate A2, Marriott
Hands-on Lab: Extending the Oracle Application Express Framework with Web 2.0
APEX Development Team

S299315, 11:45 - 12:45 at Salon 14/15, Marriott
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

S298612, 15:45 - 16:45 at Golden Gate A2, Marriott
Hands-on Lab: Creating "High-Fidelity" PDF Reports with Oracle Application Express
APEX Development Team

Monday, Sep 22

S298617, 11:30 - 12:30 at Golden Gate A2, Marriott
Hands-on Lab: Maximizing the Capabilities of Oracle Application Express--Interactive Reports
APEX Development Team

S300442, 14:30 - 15:30 at Salon 14/15, Marriott
Migrating from Oracle Forms to Oracle Application Express
David Peake, Oracle

S301401, 16:00 - 17:00 at Rm236, Moscone South
Analyze your PL/SQL Code with Oracle Application Express and DBMS_PROFILER
Flavio Casetta, Yocoya.com

S298613, 17:30 - 18:30 at Salon 14/15, Marriott
Web 2.0 Development with Oracle Application Express
Carl Backstrom, Oracle

Tuesday, Sep 23

S298615, 11:30 - 12:30 at Golden Gate A2, Marriott
Hands-on Lab (Part 1): Building a Functional Application with Oracle Application Express
APEX Development Team

S301630, 13:00 - 14:00 at Rm 200, Moscone South
Security: Writing Custom Authentication Schemes for Oracle Application Express
Raj Mattamal & Josh Millinger, Niantic Systems, LLC

S300210, 14:30 - 15:30 at Salon 02, Marriott
The Power of the Oracle Application Express Repository
Patrick Wolf, Sphinx IT Consulting

S299306, 17:30 - 18:30 at Salon 14/15, Marriott
Zero-Cost Business Intelligence Using Oracle Database 11g and Oracle Application Express 3.1
Jim Lancer, Paetec

Wednesday, Sep 24

S298610, 09:00 - 10:00 at Rm 104, Moscone South
Soup-to-Nuts RAD Development Using Oracle SQL Developer and Oracle Application Express
Mike Hichwa, Kris Rice & David Peake, Oracle

S298825, 11:30 - 12:30 at Rm 307, Moscone South
Oracle Application Express and Oracle XML Database: A Match Made in the Database
Mark Drake, Oracle 

As a Speaker
S300458, 17:00 - 18:00 at Rm 304, Moscone South
Building Commercial Software-as-a-Service Applications with Oracle Application Express
David Peake, Oracle; Paulo Vale, Neoface; Francis Mignault, INSUM; Jim Brull, Centroid; Doug Gault, Hotsos; Dennis Vanill, Paetec

Thursday, Sep 25

S300042, 09:00 - 10:00 at Rm 300, Moscone South
Dispelling Myths About Oracle Application Express (IOUG)
John Scott, APEX Evangelists

S300242, 15:00 - 16:00 at Rm 302, Moscone South
Building a Web 2.0 Interface with Oracle Application Express
Mark Lancaster, Theiss Pty. Ltd

Me at Oracle OpenWorld 2008

Written by Paulo Vale on Monday, August 11, 2008

It is my first time at Oracle's big event, the OpenWorld. David Peake was kind enough to invite me to participate in one of the official sessions.

So, if you want to meet me, you can find me at San Francisco next September :)

Here is the session description, don't forget to register:



Session Id: S300458

Title
Building Commercial Software-as-a-Service Applications with Oracle Application Express

Date: 24-SEP-08
Start Time: 17:00
End Time: 18:00

Location: Rm 304, Moscone South

Description:
There is a huge buzz around software as a service (SaaS). Come to this roundtable to hear from a collection of companies (from small to very large) that have successfully deployed commercial SaaS applications with Oracle Application Express. The discussion delves into development, deployment, support, and other issues encountered in building these assorted commercial applications. So if you have ever had a brilliant business idea and want to know how to make it into a commercial reality, this session is a must.

Speakers:
David Peake, Oracle;
Paulo Vale, Neoface;
Francis Mignault, INSUM;
Jim Brull, Centroid;
Doug Gault, Hotsos;
Dennis Vanill, Paetec

More APEX sessions at Oracle OpenWorld 2008

Written by Paulo Vale on Monday, July 21, 2008

According to Oracle OpenWorld Blog, the top 25 voted sessions at Oracle Mix will be presented at Oracle OpenWorld 2008. Among the 25 winners there is a good representation of APEX, meaning that APEX community is very active. The winners, APEX related are:

Rank: 1 (113 votes)
Security: Writing Custom Authentication Schemes for Oracle Application Express (Oracle APEX). 
Submitted by: Raj Mattamal

Rank: 5 (90 votes)
How to Hack an Oracle Application Express Application. 
Submitted by: Anton Nielsen

Rank: 6 (87 votes)
Successfully Developing and Integrating Applications in Oracle APEX Within Oracle E-Business Suite.
Submitted by: Haseeb M

Rank: 12 (75 votes)
(Re)Developing a Logistic Application in Oracle APEX in the Real World. 
Submitted by: Roel Hartman

Rank: 16 (68 votes)
Oracle APEX Team Development: Best Practices, Collaboration, and Application Deployment. 
Submitted by: Priya Lapham

Rank: 18 (66 votes)
Rapid Development and Validation of Oracle APEX Applications at an FDA-Regulated Medical Device Company. 
Submitted by: Deb Groskreutz

Rank: 21 (61 votes)
Using Oracle APEX to Analyze Your PL/SQL Source Code.
Submitted by: Flavio Casetta

What I miss the most in interactive reports

Written by Paulo Vale on Monday, July 21, 2008

I'm obliged to say that the Interactive Report Regions is one of the best features in APEX. The possibilities are huge. It's like passing the power to the end users.



A few days ago, I've posted about IRR having an upgrade in the next APEX release. The feature is being called as websheets. The possibility of having a tabular form with inline edit combined with the actual IRR features it's a "just can't wait" functionality.



Still, Interactive Reports are not perfect :) There are some functionalities I miss from the classic reports. I know that I can still use the classic reports, and I do use them many times, but having the possibility I tend to use the IRR as much as I can.



One of the functionalities I miss the most from classic reports is the possibility of being based on a function returning a query. This adds the possibility of a report being dynamically based on different tables or views and having dynamic where clauses. One of the situations where this is very useful is if you want to build a report wizard system in your application.

Also important, would be the possibility to have control over the report template and CSS, something similar to what we have in APEX classic reports.

OTN Forums Upgrade

Written by Paulo Vale on Sunday, June 29, 2008

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 thread as a question;
   . Adding emoticons;
   . Adding Tags;
   . Automatic saving drafts;
   . Run spell checker;

I'm sure there are more changes ;)

APEX web form based on procedure using out parameter

Written by Paulo Vale on Friday, June 20, 2008

When creating a form region on APEX, there are several options to base the form such as a table, a view, a webservice or... a stored procedure.

Creating a form based on a stored procedure can be very useful to execute database manipulation operations in a simple way. In fact, it is even possible to obtain responses from the database directly to an APEX page using OUT parameters.

To illustrate this, here is a simple example. Let's say you want to build a form where a user is asked to guess a number. The page should also inform the user if the guessing is right or wrong.

First of all let's create the procedure. Note that there are two parameters on this procedure. p_guess (IN parameter) is used to send the value and p_result (OUT parameter) is used to receive the procedure response.

<br /><br />CREATE OR REPLACE PROCEDURE guess_number (<br /> p_guess IN NUMBER,<br /> p_result OUT VARCHAR2<br />)<br />IS<br /> v_number NUMBER (5, 0) := 22;<br />BEGIN<br /> IF v_number > p_guess<br /> THEN<br /> p_result := 'The secret number is higher than ' || p_guess;<br /> ELSIF v_number < p_guess<br /> THEN<br /> p_result := 'The secret number is lower than ' || p_guess;<br /> ELSE<br /> p_result := 'Congratulations. The secret number was ' || v_number;<br /> END IF;<br />END guess_number;<br />

Now to create the form, create a new page or region. Next choose "Form", "Form on a Procedure", identify the database schema and enter the stored procedure name. Follow the form wizard creation till the end entering adequate values.


After you run the page you will be presented with two fields. Enter a value into "P_Guess" and press submit button to get a response into "P_Result".


Now to beautify the form you can hidde the result field and integrate the response message into the notification template. If you go to page edit mode you will see the auto-generated "Run Stored Procedure" after submit process. Edit this process, click "Messages" and put the reference to the item "Process Success Message" like this: &P6_RESULT.


Follow the link to run this example: http://apex.oracle.com/pls/otn/f?p=25110:6

APEX websheets!

Written by Paulo Vale on Tuesday, June 17, 2008

Just came across this blog post on Dimitri Gielis's Blog and I just can say.... well, just follow the link and see for yourself. Web 2.0 features out of the box, tabular form with inline Edit and much much more.


The screenshot was stolen from Dimitri Gielis's Blog.

NEOPI - Free tool to prepare Linux for Oracle installation

Written by Paulo Vale on Wednesday, June 11, 2008

NEOPI stands for Neoface Oracle Pre-Installer.


This is a free tool which installs the missing but required Linux packages for an Oracle Software installation. Beyond that, also configures the kernel and security parameters and creates the Oracle account and all due privileges. Configuration files can be customized to the DBA needs.

My colleague at Neoface João Oliveira, blogged about NEOPI some days ago. Please follow the link to his blog. There, you can find all the details and the download link.

If you try this tool, we would be very happy to get some feedback.

Change default Date Picker Icon

Written by Paulo Vale on Tuesday, May 27, 2008

So you don't like de default Date Picker icon? The good news is that there a very simply way to change it.

Change the request of a select list with submit

Written by Paulo Vale on Monday, May 26, 2008

Today I had this requirement with a Select List with Submit in a new application I'm developing. I had a form with an Automatic Row Processing (DML), After Submit process. This process is used only for updates and should be triggered when the value of a Select List changes.


I've created the Select List with Submit but the process was not executed. The reason is that by default the request value is the name of the Select List item and as you can see in the previous picture, the data manipulation process will perform a SQL UPDATE only when the request value is one of the listed values (SAVE, APPLY CHANGES, UPDATE, UPDATE ROW, CHANGE, APPLY, APPLY%CHANGES%, GET_NEXT%, GET_PREV%).

To get around this, here is what I did: first I've changed the item back to a normal Select List and then added the following code to the 'Element > HTML Form Element Attributes' property:

onchange="doSubmit('UPDATE');"

Here is a screenshot of the Select List item properties:


Happy programming :)

Oracle Application Express 3.1.1 patch set was released

Written by Paulo Vale on Thursday, May 22, 2008

Yes it's true, the patch is out. I found it reading Joel Kallman blog. As Joel states in his blog, this not a new APEX version... it is a patch with bug fixes.

One of the things that makes me happy with our decision at Neoface of directing all the development efforts to this technology (APEX) is the frequent updates APEX team is giving us.

They are doing a great job.

Nice filter region template

Written by Paulo Vale on Wednesday, April 23, 2008

One of the good things that Oracle APEX Team made to help the new developers are the Packaged Applications. Packaged Applications are working example applications that you can use to get the feel of APEX capabilities. You can also learn how things get done, since you can access all the source.

This leads me to the point of this post. I like very much the way, reports filters and options are organized in these example applications. The person who made this, certainly has a good taste :)

Look at the next screenshot to see what I mean. I'm talking about that gray region where the search field and conditions are.


Since I liked it so much, I've decided to use it in my applications. For instance, I've integrated this region template in our APEX built CRM App (Better.CRM).


How to do it? :) Just copy the code from any of the Packaged Applications and then:

- Shared Components > Templates > Create
- Template Type: Region
- Create Region Template: From Scratch
- Name: Finder Bar
- Template Class: Button Region Without Title
- Template Code:
<br /><style type="text/css"><br />table.TopBarUIFix {<br /> margin:0 0 6px 0;<br /> empty-cells:show<br />}<br />td.TopBarUIFixL {<br /> white-space:nowrap;<br /> border-right:1px solid #cccccc;<br /> border-top:1px solid #cccccc;<br /> border-bottom:1px solid #cccccc;<br /> background:#eeeeee;<br />}<br />td.TopBarUIFixC {<br /> white-space:nowrap;<br /> border-top:1px solid #cccccc;<br /> border-bottom:1px solid #cccccc;<br /> background:#eeeeee url(#IMAGE_PREFIX#htmldb/builder/builder_find.png) no-repeat;<br /> background-position: center;<br />}<br />td.TopBarUIFixC a {<br /> display:block;<br /> height:100%;<br /> width:25px;<br /> text-decoration:none;<br /> cursor:help;<br />}<br />td.TopBarUIFixR {<br /> border:none;<br /> white-space:nowrap;<br />}<br />td.TopBarUIFixL *, td.TopBarUIFixR *, td.TopBarUIFixC * {<br /> vertical-align:middle;<br />}<br /></style><br /><table class="TopBarUIFix" cellpadding="0" cellspacing="0" border="0" summary="" id="#REGION_ID#"><br /><tbody><br /> <tr><br /> <td><img src="#IMAGE_PREFIX#htmldb/builder/builder_find_top.png" width="10" height="8" alt="" /></td><br /> <td rowspan="3" class="TopBarUIFixC" valign="middle"><a style="cursor:default;"> </a></td><br /> <td rowspan="3" class="TopBarUIFixL" valign="middle" style="">#BODY#</td><br /> <td rowspan="3" width="30"><br /></td><br /> <td rowspan="3" class="TopBarUIFixR" valign="middle" ><span style="margin-right:10px;">#CLOSE#</span><span>#EDIT##DELETE##CREATE##CREATE2##PREVIOUS##NEXT#</span></td><br /> </tr><br /> <tr><br /> <td style="background-image:url(#IMAGE_PREFIX#htmldb/builder/builder_find_bg.png);height:60px;"><br /></td><br /> </tr><br /> <tr><br /> <td><img src="#IMAGE_PREFIX#htmldb/builder/builder_find_bottom.png" width="10" height="8" alt="" /></td><br /> </tr><br /></tbody><br /></table>

Now you just need to create an HTML Region in your page, apply the new Finder Template and include the fields in the region.

That's it. I hope this helps you make good looking apps :)

New blog

Written by Paulo Vale on Saturday, April 05, 2008

I would like to point you to a new APEX related blog. João Oliveira is my personal friend, works with me at Neoface and he is an experienced Oracle DBA.

APEX is not an exclusive subject, but you will find on beyondoracle blog many interesting topics regarding this technology, specialy in terms of server configurations, security and tunning.

He started his blog last February, so please give him a visit and a warm welcome.

Partial Page Request Reports

Written by Paulo Vale on Thursday, April 03, 2008

Last December I wrote a post about Turning a Report into PPR Report, making some changes in report template.

PPR means Partial Page Request, in other words, you can navigate your report records without needing to submit the whole page.

With the last APEX update, in case you havent noted yet, this feature option is already added to the report properties, so you don't need to change report template anymore.

To enable it, go to the report region properties and change to "Report Attributes" tab, "Layout and Pagination" section and change "Enable Partial Page Refresh" to Yes.



Have fun.