Dimitri Gielis Blog

Subscribe to Dimitri Gielis Blog feed
I created this Blog to share my knowledge especially in Oracle Application Express (APEX) and my feelings ...
Updated: 3 days 3 hours ago

Best and Cheapest Oracle APEX hosting: Free Oracle Cloud

Tue, 09/17/2019 - 00:20
I really have to write about the Oracle Cloud Free Tier as it's the third time in my career I've been blown away by Oracle.

The first time was when I first saw and worked with the Oracle Database, the second time when I saw HTMLDB (now APEX) and today, again, with the announcement of the Free Autonomous Oracle Cloud.

The Free Oracle Tier is a hardcore smackdown on any other cloud offering which includes a virtual machine, database and development environment!

The Free Oracle Cloud was announced by Andy Mendelsohn at Oracle Open World 2019.
You get a free Oracle Autonomous Database, 2 Virtual Machines (Compute), Storage and other services:

There's nothing like this Oracle offering on the market. The best database in the world, running on amazing hardware and all of this for free. This is not just for APEX Developers, this is for anybody who wants a datastore or virtual machine in the cloud without wanting to manage things themselves.

Jon, from JMJ Cloud, wrote RIP Exadata Express, where do I run my RAD stack now?, the answer today for me is in the Free Oracle Autonomous Oracle Cloud. For many of my customers I also used the Exadata Express service before, as it was ideal to start developing new Oracle APEX apps. The Free Oracle Autonomous Database Cloud is a better version of what Exadata Express has ever been. I would always start my development in this new free Oracle Cloud offering as it allows you to scale up while you grow.

I really believe everybody should sign-up for this service. As with every new service, the beginning might have some hickups, but to me it's worth starting with this service straight away.

I signed up for the Free Oracle Cloud  in multiple regions, and love it so far.
So I thought to write a series of blog posts on how you can get started (links will be updated while articles are published):

1. Sign-up for Free Oracle Cloud
2. Setup Autonomous Transaction Cloud (ATP)
3. Connecting with SQL Developer (Desktop) to ATP
4. Connecting with SQL Developer Web to ATP
5. Setup APEX in ATP and create first APEX app
6. Create a Virtual Machine
7. Setup a Webserver on the Virtual Machine
8. Setup APEX Office Print (AOP) On-premises on the VM
9. Connecting ATP to VM
10. Connecting VM to ATP
11. Final steps: never let the service expire

Free Oracle Cloud: 2. Setup Autonomous Transaction Cloud (ATP)

Tue, 09/17/2019 - 00:20
This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.
In step 1 we signed-up for the Free Oracle Cloud. Once we receive the email that everything is setup, after logging in you will see in the Oracle Cloud dashboard an "Always Free Eligible" box.In this post we will create our first FREE Oracle Autonomous Transaction Database.
Login to the Oracle Cloud:

Click the Create a database link.

Give the database a name and select the type of workload you want to create. In most Oracle APEX projects I believe Transaction Processing is the one to go with:

Scrolling down on the page will show the Always Free switch, make sure to enable that :)
Further down you will need to provide some credentials that you will use to login later on.

Hit the Create Autonomous Database button and you will receive a detail screen that the provisioning has started:

You can now navigate to other parts of the Oracle Cloud. You will also receive an email when the provisioning is complete:

In the navigation (hamburger icon top left) click on the Autonomous Transaction Processing link and you will see the dashboard of the Autonomous Databases you have:
Once it's available you can click on it and see all details:

In the next post we will connect to our new Free Oracle Autonomous Database.

Free Oracle Cloud: 1. Sign-up for Oracle Cloud Free

Mon, 09/16/2019 - 22:32
This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.

To get started, go to https://www.oracle.com/cloud/free/ and click the Start for free button:

Follow the wizard to sign up for the Oracle Cloud Program:

When you enter your Account details, it's important to point out that your Home Region is really important. It's only in your Home Region that you can get the Free Oracle Cloud service.
I would select the Home Region based on where most of your visiters/customers and yourself are based. Also provide correct information about yourself and a valid phone number.

You will get a notification on your phone to continue:

Provide a password:

Enter your payment information. It's important to add a valid credit card in order to get the Free Oracle Cloud, if you don't you enter in Trial mode.

Your credit card is not being charged (well for 1 USD to test the card), but it's important to have the credit card as Oracle sees your account as a Paid account and it will open more functionality :)

That's it - your account is now being created...

I setup a couple of Free Oracle Cloud services and in most instances the account is created within a minute, and you get redirected to the sign in form:

When signing in you will see the Oracle Cloud dashboard. Note that in the following screenshot, the Free Oracle Cloud Tier is not ready yet.

You will receive a welcome email.

And a few minutes later you will receive a message you are all set, and your FREE Oracle Cloud account should be ready to be selected.

But sometimes, the account is not created straightaway and you need to wait a bit. In a few cases I had to wait a few hours for the email, but at the end it always worked for me. It might be because I used this service when it was just released and maybe some datacenters weren't 100% ready yet.

In the next post we will setup a free Autonomous Database (ATP).

See APEX Debug info in PL/SQL and SQL Developer

Tue, 06/11/2019 - 12:18
When developping Oracle APEX apps I like to instrument my code with APEX_DEBUG and/or Logger.

With our APEX Office Print (AOP) PL/SQL API and APEX Plug-in we did the same, we use APEX_DEBUG and Logger behind the scenes to allow you to see what it going on. But when I tried to view the APEX debug messages in SQL Developer, I didn't see any. Christian Neumueller of the APEX Dev team, gave me the answer: APEX debug is buffering it's output, to reduce the I/O overhead. Buffering is disabled for LEVEL9, but the other levels only write:
 - after 1000 records
 - at the end of request processing
 - when you detach the session

This explained perfectly what was happening for me.

Here's a quick example when you want to debug the AOP PL/SQL API from PL/SQL and SQL Developer. The code downloads an Interactive Report of page 200 to Excel and stores it in a table.

l_return blob;
l_output_filename varchar2(100) := 'output';

apex_debug.enable(p_level => apex_debug.c_log_level_info); 
-- for more details, use: c_log_level_app_trace
apex_debug.message(p_message => 'Debug enabled.');

l_return := aop_api_pkg.plsql_call_to_aop (
p_data_type => aop_api_pkg.c_source_type_rpt,
p_data_source => 'report1',
p_template_type => aop_api_pkg.c_source_type_apex,
p_template_source => 'aop_template_ir_customers.xlsx',
p_output_type => aop_api_pkg.c_excel_xlsx,
p_output_filename => l_output_filename,
p_aop_url => apex_app_setting.get_value('AOP_URL'),
p_api_key => apex_app_setting.get_value('AOP_API_KEY'),
p_app_id => 498,
p_page_id => 200);

insert into aop_output (output_blob,filename) 
values (l_return, l_output_filename);

dbms_output.put_line('To view debug messages:');
dbms_output.put_line('select * from apex_debug_messages where session_id = '
||apex_util.get_session_state('APP_SESSION') ||' order by message_timestamp');


Running the SQL statement to view the debug messages:
select * from apex_debug_messages where session_id = 16458652970080 order by message_timestamp

Et voila... the APEX debug info is available straight away :)