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

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

Javascript Tooltip Integration

Written by Paulo Vale on Tuesday, January 08, 2008

As I've said in my previous post, there is another interesting project for handling tooltips, the BoxOver Tooltips from the same author of the calendar script.





It is a small javascript that you can integrate with you APEX applications. It is very simple to use and has powerful customizations possibilities like using your own CSS to change the look and feel. You can even include pictures in your tooltips. Go to the project page for nice examples and howtos. Click here to see it working with APEX.



To install the script, download the latest version of BoxOver tooltips and copy the boxover.js script into a filesystem directory or just upload it as a static file under Shared Components. Include the following somewhere in the HTML of your page (Header section of you page or directely in page template if you want the tooltip feature available in all application pages). Substitute the path of the file accordingly making sure it points to the correct location of the boxover.js file.





Now you can use this feature just by calling it in an appropriated place. A simple example, using a Text Field is adding the following tag to "HTML Form Element Attributes" property :

TITLE="header=[Date] body=[Click here to pick a date]";

BoxOver is configured through the "title" attribute of HTML elements. This means you can use it in all your HTML objects. Works in all latest browsers incl. IE 6, IE 5, Mozilla 1.4+, FireFox 0.9.3 Opera 7.

Javascript Calendar Integration

Written by Paulo Vale on Monday, January 07, 2008

One of the good things about APEX is that we're not limited to the buit-in features. There are hundreds of examples of free code you can find with the help of Mr. Google :)

Today I'll show one of these examples. A small javascript calendar that you can integrate in you APEX applications. Click here to see it working.

I've found this simple but great small javascript calendar at http://calendar.swazz.org/ . Click here to download the latest version.

To install the script:

1- Copy the calendar.js script into a filesystem directory or just upload it as a static file under Shared Components.

2- Include the following somewhere in the HTML of your page (Header section of you page or directely in page template if you want the calendar available in all application pages). Substitute the path of the file accordingly making sure it points to the correct location of the calendar.js file.



3. Create a Text Item in your page and open the item properties page. Add to the the code:

onfocus="this.select();lcs(this)"
onclick="event.cancelBubble=true;this.select();lcs(this)"

4. Run the page and click the Text Item.

There is another interesting project for tooltips from the same author of the calendar script. If I manage some free time i'll blog about it later.