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 probablymost 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...
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.
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.
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...
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
declare
l_new_id number;
begin
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;
exception
when others then
raise;
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: resources.zip.
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
declare
l_image_id number;
begin
insert into hr.images (content_type, image, filename) values (:contentType, :body, :file) returning image_id into l_image_id;
commit;
/* 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;
end;
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
begin
delete from hr.images where image_id = :imageid;
commit;
:status := 200;
end;
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:
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.
