Wednesday, September 18, 2019

Free Oracle Cloud: 3. Connecting with SQL Developer (Desktop) to ATP

This post is part of a series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud.

In the previous post we setup our Oracle database in the Free Autonomous Oracle Database Cloud.
Now we want to connect to this database from our machine with SQL Developer.

Login to your Oracle Cloud Account. In case you forgot the link, you can always go back to your emails and in the "Your Oracle Cloud Account is Fully Provisioned" you find a button to Sign in.

Typically it's in this format:
So in my case this is:

Once in, navigate on the left hamburger icon to Autonomous Transaction Processing and select the Autonomous Database (see previous blog post - last two screenshots). In the overview screen click the DB Connection button.

A modal dialog appears to download the credentials (wallet). Click the Download button:

The modal dialog changes and you need to enter a password and hit download:

Now we will make a new connection in SQL Developer. Right click on the Oracle Connections and click the New Connection... link:

Enter a name of your choice. In the User Info section as Username you enter admin and as paswoord, the paswoord you entered when you setup your database (previous blog post).
Select for Connection Type Cloud Wallet. A details section will open where you can select the file you downloaded before in the Configuration File.

Hit the Test button and see if you receive a Success message. Once fine, you can save and connect.

The admin user is a special user in the Oracle Cloud. You can see it as the sys/system user, with many of the same rights. So you can query for example dba_users to see all the users/schemas in the database:

So now you are all set to play with your database. You can setup new users, create tables, load and query data etc.

In this post we saw how to connect from SQL Developer Desktop version to the Oracle Cloud Database. In the next post I will show how to connect with SQL Developer Web, a webbased alternative to SQL Developer (Desktop).

Tuesday, September 17, 2019

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

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

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 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).

Best and Cheapest Oracle APEX hosting: Free Oracle Cloud

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

Tuesday, June 11, 2019

See APEX Debug info in PL/SQL and SQL Developer

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 :)

Saturday, March 30, 2019

Monitoring your Oracle APEX environment; when is a good time to do an upgrade?

Yesterday Oracle released Oracle APEX 19.1. We typically upgrade our environment within a week.

The question is always, when is a good time to upgrade?

As we are hosting multiple applications and sites in our environment, we want to find a spot with minimal impact to our customers. For some applications which are only accessed by some people in Belgium, it's very easy to schedule an upgrade at night as the chances are not high people accessing the app. But in the case of APEX Office Print, we have customers all over the world in different timezones. So they might want to look at their dashboard or download the latest version at any moment in time.

For our AOP service itself, we have an internal policy we don't allow downtime. We load balance, so even when performing upgrades of AOP, there will always be a server available to handle the requests. For our Oracle database and APEX environment, we are fine with a few minutes downtime while upgrading the server.

In Oracle APEX itself, when you go to Monitor Activity - Page Views by User by Hour you get a graphical overview when your applications are being used:

This screen works well for a given workspace. If you want to look cross workspaces, for your entire APEX instance, you could build something yourself similar to the above. An overview like that would give you an idea based on historical usage which hours have the least amount of impact.

Now that you found the window, you still want to check if somebody is using our APEX apps.

So before I start an upgrade, I always check the last activity in our APEX apps.
The script I use to monitor the activity in our entire APEX environment - as SYS user run in SQL>

    TO_CHAR(session_created, 'DD-MON-YYYY HH24:MI') AS session_created,
    TO_CHAR(session_idle_timeout_on, 'DD-MON-YYYY HH24:MI') AS session_idle_timeout_on,
    TO_CHAR(session_idle_timeout_on-(session_max_idle_sec/24/60/60), 'DD-MON-YYYY HH24:MI') AS last_activity,
    TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI') AS time_now,
    round((sysdate-(session_idle_timeout_on-(session_max_idle_sec/24/60/60)))*24*60) as minutes_ago
 FROM apex_workspace_sessions
WHERE user_name NOT IN ('APEX_PUBLIC_USER','nobody')
ORDER BY minutes_ago, workspace_name, session_idle_timeout_on DESC;

This gives me the following overview:

So I see how many minutes ago (last column) which APEX workspace was used by which user. I could even follow the APEX session to find out more about the APP etc. but that is not really relevant for me. In the script, I only check for logged in users.

In case I also want to see for direct connections to the database, or I want more real-time info from the APEX session, I run the following script to check the session in the Oracle Database:

    --sql_id, sql_exec_start, event,
    client_info         AS workspace_id_auth_user,
    module              AS db_schema_apex_app_id_page_id,
    action              AS action,
    client_identifier   AS auth_user_apex_session
 FROM gv$session
WHERE type = 'USER'
  AND service_name = 'apex_pdb';

This gives me the following overview:

In the above screen you see I'm connected to the database with SQL Developer. The other sessions are coming from our connection pool. What is interesting is that APEX is instrumented to populate session info in the database like module, client_info, and client_identifier. This is important as APEX/ORDS is working with a connection pool so it would be hard to see what APEX session corresponded to which database session in case they didn't.

The above helps me to find a good time to perform upgrades, without impacting too many people.
So I thought to share my scripts as it might help you too.

Friday, March 29, 2019

Where are my static application files of Oracle APEX?

Ever got the error that there's an issue with your environment because the Application Express files have not been loaded when you try to open Oracle APEX in your environment?

Since the Announcement of the availability of Oracle APEX Static Resources on a Content Delivery Network I typically configure APEX to use the CDN.

Steps to do so for the entire environment:
  • Navigate to the apex/utilities subdirectory from the directory you unzipped APEX to
  • Connect to your database as the SYS user
  • Run:  @reset_image_prefix.sql
  • when prompted for the image prefix, enter the correct path, e.g.
Now, when I got the above error, I forgot which images folder APEX was using. Was there an issue with the connection to the CDN, was it a local issue?

Below is how I found out how the APEX images directory was configured and if it was using the CDN or local files.
Connect as SYS in a SQL window (SQLcl, SQL Developer, SQL Plus, ...):

SQL> set serveroutput on
SQL> begin
  2  dbms_output.put_line(apex_180100.wwv_flow_image_prefix.g_image_prefix);
  3  end;
  4  /

PL/SQL procedure successfully completed.

SQL> begin
  2  dbms_output.put_line(apex_180200.wwv_flow_image_prefix.g_image_prefix);
  3  end;
  4  /

PL/SQL procedure successfully completed.

Above you see that in APEX 18.1 the /i/ directory is used on the local web server.
On APEX 18.2 the CDN is being used.

Note: whenever you run the above command change to the correct APEX user (version), so for APEX 19.1 you use apex_190100.

Friday, March 01, 2019

Including an image from APEX Static Application Files in an email

Some people use APEX Office Print (AOP) to send out HTML emails based on a custom HTML template they created. Today we got a question in our AOP support inbox how to include images in those emails.

In an AOP Template, you can use a tag {%image} and AOP will replace that tag with a base64 encoded image. This works great when you open the resulting HTML file in a browser, but when using emails it's not so great as some email clients don't support base64 encoded images e.g. Outlook.

That the images don't always work is not really an AOP issue, it's more an issue with HTML emails.
Here's a good read on supporting images in HTML emails.

In short, it's better to include an URL to the image as that is supported by most, if not all, email clients. Typically the images are stored in a BLOB column in a table or people want to reference an image that they uploaded in APEX > Shared Components > Static Application Files.

This blog post shows a technique to reference an image stored in the database through a URL.

1. Create a new Public Page in an Oracle APEX Application e.g. Page 22

2. Create a region on the page

3. Create a hidden item on the page e.g. P22_FILE_NAME

4. Create a Before header Process "Retrieve image from DB" with following PL/SQL Code:

  l_file_name     apex_application_static_files.file_name%type;
  l_mime_type     apex_application_static_files.mime_type%type;
  l_file_charset  apex_application_static_files.file_charset%type;  
  l_file_content  apex_application_static_files.file_content%type;
  select nvl(file_name, 'output') as file_name, 
         nvl(mime_type, 'application/octet-stream') as mime_type, 
         nvl(file_charset, 'utf-8') as file_charset, 
    into l_file_name, 
    from apex_application_static_files 
   where application_id = :APP_ID 
     and file_name      = :P22_FILE_NAME;
  owa_util.mime_header(l_mime_type, false);
  sys.htp.p('Content-Disposition:attachment; filename="'||l_file_name||'"; '||'filename*='||l_file_charset||''''''||l_file_name);

  when no_data_found

5. That's it.

Now you can reference the image with the following URL: https://my_server/ords/f?p=MY_APP:22:0::::P22_FILE_NAME:my_image.png

If you want to test the functionality in an email, go to SQL Workshop >SQL Commands and use the following code:

    p_to        => '',
    p_from      => '',
    p_subj      => 'Mail from APEX with inline image',
    p_body      => 'Please review the email in html.',
    p_body_html => 'Here the inline image: <br/> <img alt="Image" height="150" width="150" src="https://my_server/ords/f?p=MY_APP:22:0::::P22_FILE_NAME:my_image.png">');


Now you can send HTML emails in APEX with references to images that are stored in the database.

Sunday, February 10, 2019

Region only shown in development mode in Oracle APEX

In the last months, I had to look up several times in different projects how to show a region only when I was logged in into the App Builder in Oracle Application Express (APEX). So I thought to write a quick post on it.

In one project we had to log in with different users to test the behavior of the authorization schemes, so people saw the things they should see and could do the things they are allowed to do. As the logins where not straight forward we created a region with those test credentials. Other people were testing too with their own credentials, so we really wanted to keep the original login page, and decided to just add a region on the page we would only see when we were logged in into APEX itself.

Today I added some new pages to an app and wanted to make sure the navigation to those pages were only visible to me. I know, you should do this in DEV, and then when all is fine, propagate the app to TEST and PROD. The truth is, I've some applications that only exist in one environment and so I update straight in "production". Those apps are still backed up automatically every night, so worst case I can always take the version of the previous day. But just to be clear, this is not good practice ;)

So how do you show a region only when you are in development mode in Oracle APEX?

You go to the Conditions section of your region, list entry or any component in APEX really, and add a PL/SQL Expression: apex_application.g_edit_cookie_session_id is not null

Alternative you can use "Item / Column not null" and specify APP_BUILDER_SESSION:
*) updated blog post 11-FEB-2019 after comment of Christian (thanks Christian!)

It would be cool if there was a condition type "Development Mode", but maybe I'm the only one needing this...

Typically you would use Build Options (see Shared Components) to include or exclude certain functionality in your APEX app, but in the above two use cases, it won't really work.

Another enhancement would be that the Status of the Build option would include "Dev Only" next to Include and Exclude.