Saturday, October 22, 2011

Install, Rinse, Repeat

Every time I start installing Oracle on Linux I always end up with the same dark thoughts: Why does it have to be so hard! An the worst part is: It isn't! But let me be precise with the last statement: It isn't hard if you know how! But in the beginning, you don't, so it is... You get the picture.

On my last endeavor to create a shiny new VirtualBox machine, I decided to take notes. Here is what I aimed at:

Yes, both Apache httpd and Apache Tomcat present. I needed a versatile development vm, with a known (to me) web server, and a known (again, to me) web container working with APEX, the APEX Listener and Oracle 11g XE database.

There will be typos, there will probably most likely of course be things I have forgotten to write down. On the whole, I hope it will save you some grey hairs (or in my case, loss of gray hairs :-))

Beware, the iframe controls will not be very responsive before the document has completed loading, and the document is quite large. Go for the direct access if it gets too troublesome...

Direct access to the Google doc is here:

Here goes:

Friday, October 21, 2011

Updated Hotkeys for APEX 4.1

I finally got around to updating the hotkeys userscript for Application Express 4.1. There were some changes in the APEX Builder html that made an update necessary.
Cool image by Josef Stuefer, available by CC BY 2.0
I have to say, that after I started using shortcut keys in the builder I never looked back. These days I get confused when developing in a browser where the script is not installed.

The script can be downloaded here:

Just click the big green Install-button, and you can start using it. The script is tested with Google Chrome, but should work equally well with Firefox.

The keys are the same old:

  • F8: Run Page
  • F9: Apply Changes
  • Alt + F9: Clicks the highlighted button
  • Shift + F9: Cancel
  • Alt + PageDown: Next
  • Alt + PageUp: Previous
  • Ctrl + Shift + S: Shared Components
  • Ctrl + Shift + E: Edit Page
  • Ctrl + Shift + Z: Toggle return to page on/off

If you disagree with the key mapping; then change them! The source is there for the changing :-)

If there are any grievous errors, then do not hesitate to call...

The old hotkeys userscript for APEX 4.0 is still available here:

Thursday, October 20, 2011

Multiple File Upload with jQuery and APEX Listener

Did you ever want to do this?

Envious of the fancy multiple image upload functionality I saw from Google+, I set out to do the same. This is more of a log of observations than a full recipe. There is no APEX involved but the listener. To follow this you have to have an APEX Listener v1.1 (or higher) up and running against an Oracle 10g (or higher) database. This will not work with Internet Explorer or Opera (not multiple files), I have only tested on Google Chrome.

jQuery to the Rescue
jQuery has a lot (capital L type of lot) of plugins, one really nice I stumbled upon was Sebastian Tschan's jQuery-file-upload. Demo is here, download is here, documentation is here.

Setup Database Objects
The images has to go somewhere, and I created an images table in the hr-schema:
   --drop table images cascade constraints;
   --drop sequence images_pk_seq;

   create table images (
      image_id        number,
      content_type    varchar2(255),
      filename        varchar2(4000),
      image           blob,
      constraint img_pk primary key (image_id) using index)

   create sequence images_pk_seq;

   create or replace trigger trg_images_bi 
   before insert on images 
   referencing new as new old as old for each row
      l_new_id number;
      if :new.image_id is null then
         select images_pk_seq.nextval into l_new_id from dual;
         :new.image_id := l_new_id;
      end if;
   when others then
   end trg_images_bi;

   alter trigger trg_images_bi enable;

   grant all on hr.images to APEX_PUBLIC_USER;
You have to grant insert/delete at the least to the user defined in the listener (normally APEX_PUBLIC_USER).

Setup Imagehandler Resource Template
This is where the actual data manipulation is going to happen. You can import all resource templates in this example by importing this file:

Log into listenerAdmin (http://myhost/apex/listenerAdmin).
  • Click Resource Template
  • Click Add Resource Template
  • Click Add Handler, and choose method POST
  • The Post handler will handle all uploads and inserts
  • Click Add Parameter, and set the following: status, X-APEX-STATUS-CODE, Header, Out, Integer
  • Click Add Parameter, and set the following: location, X-APEX-FORWARD, Header, Out, String
  • Click Add Parameter, and set the following: file, filename, Header, In, String (This should actually map to the filename, and be inserted with the rest of the data. For some reason, this does not work, and I'm too lazy to find out why...)
  • Add the data as seen below, and click Save

      l_image_id number;
      insert into hr.images (content_type, image, filename) values (:contentType, :body, :file) returning image_id into l_image_id;
      /* This status will actually not be seen, as we have to redirect to get control of response */
      :status := 201;
      :location := 'imagehandler/response/'||l_image_id;
  • Click the imagehandler resource template
  • Click Add Handler
  • Choose method DELETE
  • Click Add Parameter, and set the following: imageid, <blank>, URI, IN, Integer
  • Click Add Parameter, and set the following: status, X-APEX-STATUS-CODE, Header, OUT, Integer
  • Add the data as seen below, click Save

      delete from hr.images where image_id = :imageid; 
      :status := 200;
  • Click the imagehandler resource template
  • Click Add Parameter, and set the following: imageid, <blank>, URI, IN, Integer
  • Click the GET tab Add the code as seen below, click Save

   select content_type, image
   from   hr.images
   where  image_id = :imageid

Setup Imagehandler Response Resource Template
I have yet to find a way to get a PL/SQL Block resource to respond with a json array, only single json attributes. It is a shame really, giving control of the response to the developer would have helped a lot. As it stands now, the client will not get a proper response (201 - created), and we have to add an extra resource template. If there is a way, please do tell, the docs gives nada here.

This jQuery plugin requires a json array, and in order to do that, we have to cheat.
  • Click Add Resource
  • Click Add Parameter, and set the following: imageid, <blank>, URI, IN, Integer
  • Template Add the code as seen below, click Save

   select 'application/json','[{"name":"'||:imageid||'.jpg","size":'||dbms_lob.getlength(img.image)||',"url":"\/apex\/imagehandler\/'||image_id||'","thumbnail_url":"\/apex\/imagehandler\/'||image_id||'","delete_url":"\/apex\/imagehandler/'||image_id||'","delete_type":"DELETE"}]'
   from hr.images img
   where img.image_id = :imageid
Looks a bit greek, I know. You can look at the server response for more details, but basically you are returning file name, download url, thumbnail url and delete uri/method in a json array.

Setup jQuery Plugin
Nothing to it really, download and unzip the archive somewhere reachable on a web server or appserver. In this example I unzipped and renamed the folder so that I could reach the example/index.html on http://myhost/js/multiupload/example/index.html.

You have to make one change in the example/index.html to make it work: Point the form action to your image handler:
<form action="/apex/imagehandler/0" method="POST" enctype="multipart/form-data"> 
Notice how we have to add {imageid} at the end, parameters are not optional (at least not in this version of the listener), so if it is left out, it will not match the resource templates URI pattern. 

Ready for a test run! 

Some notes on working with the Application Express Listener
  • The documentation is a bit lacking (to say the least)
  • Debugging is hard, 500 server error does not say much
This is a very simplistic example, there are no security involved, and it is not embedded into an APEX page. Anyhow,  as a quick example, it (barely) serves the purpose.