Wednesday, October 17, 2012

Moving your APEX app to the Oracle Cloud

Now that the Oracle Cloud is publicly available, I want to show you what it takes to put your APEX application on there. 

As an example I will use the application "DGTournament" that I created in 2006 to promote APEX and upgraded every two years to the latest version of APEX. I thought this would be a good app to use as there's some legacy code in, but it also contains many new features that came with every release of APEX. Next to that you might actually have played with it, the app/site is still live and we can also do a side-by-side comparison of Amazon EC2 vs Oracle Cloud.

In this first post I want to take my current application with all it's data and move that onto the Oracle Cloud.

You can apply for an account on this link and you will receive some emails when you are ready to go as described in a previous post

Step 2: Connect to the Oracle Cloud from Oracle SQL Developer

Kris Rice did an excellent blog post about it. Next to that post I would definitely recommend reading the Oracle Cloud documentation as it contains important information how to connect with SFTP. You have to reset your password first, to do that you need to connect to IDM, all that is in the doc very well explained.

Step 3: Move your database objects, data and APEX app to the Oracle Cloud

Normally I would take a datapump export of my existing schema and take an export of my APEX application. So that was the route I wanted to follow, however I couldn't find the option to do a datapump import in the Oracle Cloud! (not through the tree, not through the Datapump wizard in SQL Developer)

After looking into the Oracle Cloud documentation again, it said you have to use the Cart option (which you find under the View menu) in SQL Developer. I used that before, I even asked for an enhancement request. 

So I connected to the database DGTournament is currently in and dragged everything from the tree into the Cart. (note that you need to specifically drag the package bodies, otherwise you just have the specifications) I also thought to be smart and dragged my APEX application into the cart (see next screenshot at the bottom)... 

So once everything is in the cart (and you can say you want all data for all tables as well) you can deploy to the cloud by clicking on the cloud icon (top, most left).

Fill out the details of the Oracle Cloud connection and hit Apply. A few minutes later all my database objects and data were in the Oracle Cloud! I was impressed how smooth it was.
The following screenshot shows the deployment overview and I expanded the tables tree of my Oracle Cloud connection so you can see everything is effectively in, even the BLOB columns and data got transferred without any issue.

As I dragged the APEX application in the Cart as well, I thought I would find it in my APEX Builder, but it wasn't there, so you have to manually import your APEX application.

Step 4: Import your APEX application (through the APEX Application Builder)

Importing an APEX app is not a big deal, export your app from one APEX instance and import through the wizards in the other APEX instance and done.

Step 5: Run your APEX application

Next I just ran the application but got an error ... it would have been wonderful if everything just worked, right? The backend was there and the app was there ... unfortunately I got an Oracle error ORA-... (see Step 6).

Step 6: Review (and try to recompile) all invalid database objects

And here is the biggest catch with the Oracle Cloud: some database packages are not available in the Oracle Public Cloud. So for example I recompiled one of my packages and got this error:

So after reviewing my code, following packages I used are not working in the Oracle Cloud:
    • I used this to hash the password
    • I used it to calculate timings
    • I used it for crlf (character linefeed)
    • I used it to send out mails with attachments
I also had in the APEX application direct calls to my PL/SQL package, for example to show an image I used in the src attribute of the img tab: "#OWNER#.tdg_pkg_content.SHOW_IMAGE?p_image_id=#IMAGE_ID#". But as you can't give the correct grants (to APEX_PUBLIC_USER) this doesn't work either.

Step 7: Make your code "Oracle Public Cloud"-compatible

So this basically means that all the package calls I do, that don't work, I will need to either remove or rewrite to something that does work.

For example the DBMS_UTILITY package I don't really need anymore as you can get timings native in APEX today, so not necessary to calculate yourselves.

The call to UTL_TCP for just the character linefeed I can remove too.

UTL_SMTP is interesting... in 2006 (and later in 2008), when I wrote some part of the application, there wasn't the apex_mail package yet that supported attachments, so that is why I had to use utl_smtp and write everything myself... but even using the apex_mail package wouldn't work as sending mails from the database in general is not supported in the Oracle Cloud. So the only way to solve that issue, is to integrate with a service (for example MailChimp) that sends emails for you.

And showing the images I wouldn't really code anymore using my own procedure either, as you can now use the native BLOB (image) support in APEX.

So that leaves me with DBMS_OBFUSCATION_TOOLKIT which I need to replace by something else that works in the Oracle Cloud.

Step 8: Review and Test the entire application (in all browsers)

There might be some code flying around in your application which should have been in packages (but isn't) or other components of the APEX app that are not compatible, for example some third party plugins which need some specific filesystem files and folders which you can't create in the Oracle Public Cloud.

To identify the code that should be in packages and which might not be compatible I use the APEX Advisor to find those and I did! When you upload your profile picture in DGTournament I create a thumbnail on the fly with ORDSYS.ORDIMAGE... and it looks like that's something you can't use either in the Oracle Public Cloud. It actually remembers me of one of my conversations with the Oracle team during my beta testing; I really would love to see Intermedia and Locator support in the Oracle Cloud as many of my applications are using that... the answer was it might come in the future, but it's not available yet.

For the rest you just need to go through your application and test it...

Step 9: Enjoy the Oracle Public Cloud

Once you have everything running and everything is compatible, you can enjoy the Oracle Public Cloud... Click this link to see DGTournament in action in the Oracle Public Cloud. (note that I didn't correct all incompatibilities yet and didn't upload all external files it needs)

In my next blog posts I will talk about other things like the performance, doing the maintenance etc. ... and my last post will be a conclusion.


Huysmans Filip said...

How did you get your hands on a database cloud instance @Oracle? I signed up for it, but I'm still waiting to get one.

Dimitri Gielis said...

I'm unsure what the procedure is at Oracle to activate accounts...

The moment the Cloud went live at OOW I signed up for it. If you did the same, maybe because I was in the beta group before, I got activated quicker, but that is guessing.

Unknown said...

Hi Dimitri,

Thnx, was very helpful to connect to cloud with sql dev.

I ran into a versioning problem as cloud apex is 4.1.1 and the app i made in is apex 4.2
Importing 4.2 export files into 4.1.1 not possible as is.
Any ideas/suggestions other than rebuild your app in 4.1.1?


Stanley Schmidt

Dimitri Gielis said...

I think waiting till the Oracle Cloud runs on 4.2 is the best option if your app is already on 4.2 or take a backup of 4.1.1 and run that in the cloud if it's not changed to much... building everything again might be an option depending the size of your project and how eager you want to run in the Oracle Cloud, but that would be my last option.

You could also not use the Oracle Cloud and run your APEX 4.2 projects on your own server (on Amazon, premix, or other 3rd party provider)...

SteveUK said...

Great post...

Why would Oracle be taking so long to upgrade the cloud to Apex it not production ready?

Omar Sawalhah said...

Hi Dimitri,
Regarding sending Emails from oracle cloud, this is what has been written in the documentation.

Sending Emails
You can use the APEX_MAIL package to send an email from an Oracle Application Express application. APEX_MAIL contains three procedures. Use APEX_MAIL.SEND to send an outbound email message from your application. Use APEX_MAIL.PUSH_QUEUE to deliver mail messages stored in APEX_MAIL_QUEUE. Use APEX_MAIL.ADD_ATTACHMENT to send an outbound email message from your application as an attachment.


APEX_Mail.Send( '', '',
'This is some test plain text',
'This is some test HTML',
'Test Email Subject' );
There is a built in limit of 5000 emails in any given 24 hour period. If you try to send more, you get an error.

I am not sure

TestingWhiz said...

The importance of APEX has increased considerably with the release of Oracle cloud.But according to me APEX is a custom framework as compared to Java,PHP or .NET web development and is more open than other cloud environment.

Automated testing tool

Altklube said...

Hello Dimitri,

Thanks for your blog.

I have a question regarding the procedures which you cannot use from the page.

I'm using Deneks solution to show a PDF blob in a page region by calling the procedure from the region.

This does not work in the oracle cloud.

Unfortunately I have not been able to think of a working alternative.

Do you know a clever way to show a PDF blob in a page region without using the procedure?

Thanks in advance.


Marco Oosterhoorn.

Dimitri Gielis said...

Marco, maybe you can use an iframe with as source p?n procedure or another build-in APEX procedure?

Piotr Jasiński ( / DBE) said...

Hello Dimitri,

I'm APEX themes developer from and I have customer who bought a theme from us. We have a problem sending static files to APEX images folder (there is no explanation on the web AFAIK). Did you managed to send static files in Oracle Cloud?

Best regards,

Dimitri Gielis said...

Hi Piotr,

You can't touch the APEX images folder (this is not good practice anyway as you lose changes with every upgrade of APEX).

I believe the Oracle Cloud has a place where you can store files too, or you can use another hosting like for example Amazon S3 and you reference the files from there in your App.

Hope that helps,

Piotr Jasiński ( / DBE) said...

Thanks Dimitri!

We will create Substitution string with link to our folder somewhere outside the OC :), and put this instead of #IMAGE_PREFIX# in our page templates :)

Best Regards,

Bahadir Sever said...

Hi Dimitri

similarly i am trying to move one of our app on to oracle cloud schema service

for authentication i am using DBMS_OBFUSCATION_TOOLKIT on but due to lockdown it is not permitting to use it in oracle cloud.

if it is possible can you share your replacement code or package that you used for md5 hash


Anonymous said...

I am in the same problem with DBMS_OBFUSCATION_TOOLKIT. Is compromising all my custom authentication. Is there any alternative? apex_util.get_hash can't be used for persistent comparison (second parameter is deprecated, so always your hash will be different). Any help will be really appreciated.

Unknown said...

Hii Dimitri,

I am not able to connect oracle cloud to sql dev. please help me out with the sftp credentials in sql dev to connect to cloud.
Also the steps of deploying the application are not clear to me. so request u to respond for the solution.


Dimitri Gielis said...

Which Oracle Cloud did you take?
Exadata Express, Database schema, Database Service?