Uploading and viewing photo’s with your mobile APEX application

How to upload photographs on you mobile phone with an Apex application? The question was asked to me this morning.

At our company we all drive a company car. To invoice the fuel of our foreign trips we have to invoice this at the leasing company. How easy would it be to declare it during your foreign trip. Just make a photo of the receipt and sent it directly to the leasing company with a mobile Apex application .
Here’s is the datamodel:

CREATE TABLE "CARS"
( "ID" NUMBER(2,0),
"CAR_REGISTRATION" VARCHAR2(10),
"DRIVER" VARCHAR2(20),

CONSTRAINT "CAR_CON" PRIMARY KEY ("ID") ENABLE
)
/
CREATE TABLE "DECLARATION"
( "ID" NUMBER(3,0),
"CAR_ID" NUMBER(3,0),
"MILEAGE" NUMBER(6,0),
"DATE" DATE,
"AMOUNT" NUMBER(5,2),
"PHOTO_RECEIPT" BLOB,
"MIMETYPE" VARCHAR2(255),
"FILENAME" VARCHAR2(255),
"IMAGE_LAST_UPDATE" DATE,
CONSTRAINT "DECLARATION_PK" PRIMARY KEY ("ID") ENABLE
)
/
ALTER TABLE "DECLARATION" ADD CONSTRAINT "DECLARATION_FK" FOREIGN KEY ("CAR_ID")
REFERENCES "CARS" ("ID") ENABLE
/
CREATE SEQUENCE "CAR_SEQ" MINVALUE 1 MAXVALUE 999
INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE
/
CREATE SEQUENCE "DECLARATION_SEQ" MINVALUE 1 MAXVALUE 999
INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE
/
CREATE OR REPLACE TRIGGER "BI_DECLARATION"
before insert on "DECLARATION"
for each row
begin
if :NEW."ID" is null then
select "DECLARATION_SEQ".nextval into :NEW."ID" from dual;
end if;
end;
/
ALTER TRIGGER "BI_DECLARATION" ENABLE
/

Notice the columns; mimetype,filename and image_last_update in the declaration table. Why these columns. A Blob is not “normal” data and requires specialized processing. In Apex you can eliminate this by using these additional three columns.

For my application i will make a new appliction using theme 19. This is a special theme for mobile applications.
In my this demo application i will make tree pages:
1. Form for uploading the the photo (table declarations).
2. a report with an overview of cars and navigation to the form page declaration (1).
3. a report with an overview of declarations.
My application will be running on my hosted Oracle Apex account on apex.oracle.com.

I will start with creating an new application with :

1. Create application and click next
2. accept default properties and click next
3. select report and form, in field ‘table name’ select the table ‘Declaration’ and implementation ‘Classic’ click button add page and press button next
4. change property tabs to no tabs and press next
5. accept default property no for ‘copy shared components..’ and press next
6. change authentication scheme to ‘No authentication’ and press next
7. select theme 19 and press next

Theme 19 is especially developed for mobile (smart phones) applications. This means the layout is simple.

1. In overview screen press the create button

Ready? No! Now i have two pages one report page and one form page to modify or insert new declarations. Because boths pages (table declaration) are a detail of the table cars i need a report page in which i can select a car to navigate to the form page declaration. Also i have to make some modifications to handle the images and i want a form page to enter new cars.

1. Press the button ‘Create Page’
2. select page type form and press next
3. select form on a table with report
4. accecpt default for ‘Table /view owner’ and press next
5. select table cars and press next
6. change implementation value interactive to classic and press next
7. accpet default ‘do not use tabs’ and press next
8. select all columns except ‘ID’ and press next
9. accept default ‘edit link image’ and press next
10. accept default form properties and press next
11. accept default primary key and press next
12. select existing sequence, select sequence ‘CAR_SEQ’ and press next
13. select all columns and press next
14. accept default process options and press next
15. in confirm selections page press button finish
16. select run page

For now i will create a few cars.

I need to modify the edit link because i want to navigate to the declaration page in stead of the car form page.

1. press the edit page 3 [=report page cars] link
2. in the region section click link ‘edit report columns’
3. in the columns attributes section click link ‘id’
4. change column link attributes: page : 2 (=page number of form declaration)
   clear cache : 2 (=page number of form declaration)
   item 1 name : p2_car_id (use list button to select px_car_id
   item 1 value : no change this is car id.

1. click button apply changes
2. click link run page
3. click link edit car

As you can see this page contains the columns mimetype, filename and image_last_update. In my mobile applications i not want to show these columns.
Also i need to change the properties of the button ‘bestand kiezen’ for the field photo receipt. To do this press the link edit page 2 (= page form declaration) at the bottom of the page .

1. in the items section click the link ‘Edit declarations’
2. select tab ‘delete multiple items’
3. delete items p2_mimetype,p2_filename,p2_image_last_update
4. select tab items and click link edit p2_photo_receipt
5. in the source section click link ‘BLOB Download Format Mask’

image 1

1. change the properties mimetype column,filename column and BLOB last updated column as in displayed image and press apply.
2. press button apply changes
3. click in breadcrump on page 2

When pressing the button cancel or create i want to navigate back to the report page cars

1. in the button region click the link cancel
2. in the section optional url redirect change value page to 1 to 3 (=page report on car)
3. press button apply changes
4. in the branchges region click the link ‘goto page’.
5. in the action region change the value of the property page from 1 to 3 (= page report on car)

To verify everything works fine just press the run page link. When i now press the cancel button i will navigate back to the car report page.
Click the edit link to navigate to the declaration page to insert a declaration.

Remember the report page for declararations (page 1). If you run the page you do not see an image but a download link to download the image. I will change this so that you can see the images directly. Press the ‘edit page’ link and go to the report page declarations (1) .

1. In the region section click link ‘edit region’ and delete the columns “MIMETYPE”,”FILENAME”,”IMAGE_LAST_UPDATE” in the section source.
2. select the tab ‘report attributes’ and click the link ‘photo_receipt’
3. in the section column formatting click the link ‘blob download format mask’.
4. Change the values as showed in image 2.

Notice that the property format mask is changed to image. Instead of a download link now the image is displayed.

Now i can run my application on my mobile phone.
http://apex.oracle.com/pls/apex/f?p=[app Nr]:3 (3=pagenumber report cars).

To view all inserted declarations you can view page 1 on your phone or just in you browser.
http://apex.oracle.com/pls/apex/f?p=[app Nr]:1 (1=pagenumber report declarations).