Feed aggregator

On Twitter, I got a lot of reactions on my previous blog post Printing to local (Zebra) printer from Oracle APEX in the Cloud.


As more people asked the question of how to print to a local printer from Oracle APEX, I will cover 3 options in this post. All options make use of some features of APEX Office Print (AOP).

When you install the AOP Sample Application, you find the examples under the following sections:


1. Call Print Window of the browser directly from a button


Create a button on the page e.g. PDF and add a Dynamic Action to it. The Dynamic Action calls some JavaScript:


This JavaScript calls following AJAX Process, which uses the AOP PL/SQL API to generate a PDF:


When the PDF is produced, the printJS JavaScript function is being called, which opens the browser's print window. The end-user can change the Printer and hit the Print button.
So this option is not a direct print to a predefined printer, but a two-step process: open print window, hit print.

2. Print directly to an IP Printer

One of APEX Office Print's unique features is that the AOP Server can send a document directly to the IP address of a printer. AOP is really smart and if it sees the printer doesn't support native PDF or the document type we send, it will convert to PDF or Postscript, which all printers support.

The only thing you have to do, in order to print directly to an IP Printer is to add some global variables of AOP. In the below example it will send a PDF to the IP Printer on the same server AOP is running but on port 3000.


3. Use of Post-Process command

You can configure the AOP Server with some post-processing commands, which allow you to run some command-line tools before and after the AOP Server is creating the output. This is a really powerful feature and one use case is to send the document directly to a local (or network or remote) printer.


Just like with the IP Printer, you define the post-process command in some global variables of AOP.

This technique I also described in the previous blog post where we printed to a local Zebra printer. The only special thing in that post was that we used ngroc to make AOP known to the Oracle Cloud.
When you have the AOP Server running on-premises, you don't have that step as your APEX app will call the on-premises AOP Server. The only thing you need to make sure is that AOP Server knows the printer you configured in the post-process command.


There you go, three options to print from your Oracle APEX app directly to a local or remote printer using APEX Office Print.

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 hiccups, 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:

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 the first APEX app
6. Create a VM Instance (Compute Cloud)
7. Setup a Webserver on the Compute Instance (VM)
8. Setup APEX Office Print (AOP) to export to PDF, Excel, Word, Powerpoint, HTML and Text
9. Setup Object Storage and use for File Share and Backups
10. Running SQLcl and Datapump from the Compute Instance (VM) to ATP
11. Sending Emails with APEX_MAIL on ATP
12. Create a 2nd Compute Instance and a Load Balancer
13. Final things to take away
14. Message: Your Oracle Cloud Free Trial has expired (but FREE keeps running)
15. Error: The request could not be mapped to any database
16. Renewing Let's Encrypt certificate
17. Configure domain to redirect to APEX app
18. Monitoring your website and APEX app

A few days ago Oracle released Oracle REST Data Services (ORDS) 19.4.

In my opinion, it's a major release and most likely worth investigating to upgrade for everybody. For me, the following 3 improvements are worth the upgrade:

Performance of REST APIs

The performance of ORDS based REST APIs was significantly improved in ORDS 19.4.0 by changing how ORDS handles proxied database connections. You can read more about this in the readme.

We have a customer hitting ORDS through a mobile app really hard (potentially 130 000 end-users), so any improvements in this area are awesome for those types of customers.

Removal of PDF Generation Support

As previously advised in the ORDS 18.4.0 Release Notes, the Apache FOP based functionality to produce PDF Reports from Oracle Application Express (APEX) has been removed in this release. This means that if you still want to print or export files in Oracle APEX, you most likely want to look at using APEX Office Print (AOP). AOP is the most integrated printing and exporting solution for Oracle Application Express and the defacto standard these days. It comes with an AOP Report which is similar to what ORDS provided to APEX: based on the print attributes it generates a PDF. But, AOP gives you tons more features and flexibility when you want to print and export from APEX!
When you install the AOP Plug-in, choose your own template, for example, and look at the different Data Types that are available.



SQL Developer Web

This release sees the introduction of Oracle SQL Developer Web, an ORDS hosted web application giving Oracle Database users an interface for executing queries and scripts, creating and altering database objects, building data models, accessing Performance Hub, and viewing database activity.

After setting the following properties in default.xml:


You can access SQL Developer Web through:

http(s)://your_server:your_port/ords/sql-developer

You log in with a database user and password who is REST enabled. In this script I REST enable the user DIMI:

BEGIN
    ORDS.ENABLE_SCHEMA(p_enabled => TRUE,
                       p_schema => 'DIMI',
                       p_url_mapping_type => 'BASE_PATH',
                       p_url_mapping_pattern => 'dimi',
                       p_auto_rest_auth => FALSE);
    commit;
END;
/

This is what SQL Developer Web looks like, a browser-based version of SQL Developer (desktop) and a better version of what you find in SQL Workshop in APEX (although not all features are in yet, for example, I miss editing of a record):


SQL Developer Web includes another jewel... a Database Dashboard and Activity Monitoring!
When you log in with a user with the PDB_DBA role, you get a whole new section:



You find also more information in the ORDS 19.4 documentation.

Jeff Smith wrote a nice blog post about how to get started with SQL Developer Web too.

Really nice release!

I would like to take this opportunity to wish all my readers a very Merry Christmas and a most happy, peaceful and prosperous New Year. My gift this year is not David Bowie and Bing Crosby doing their famous Christmas duet but a performance by Bowie of his classic “Heroes” as featured on the same […]

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

As more and more people are putting their production apps on the Always Free Oracle Cloud, it's probably a good idea to start monitoring your instance. (but remember this is a no-SLA environment)
Over the last few weeks, it's happened to me - my EU test database was stopped and one of my compute instances was accidentally dropped and restored. Oracle notified me and took action themselves:

If I had monitoring on, I would have seen this myself. The above case I couldn't solve on my own, but it could happen that some software you are running goes down, or something else makes your site/app unavailable due to your own code. If that happens you want to take action and verify if things are ok.
There are many monitoring tools out there, some are paid, some are free. I've used Pingdom and UptimeRobot. It takes only a minute to set it up. I'll show how to do it with UptimeRobot.
Sign-up at uptimerobot.com

Click the New Monitor button and specify the URL you want to monitor.Add a type of connection where you want to be notified and you are done!

You can simulate what happens if you go into your compute instance and stop the webserver.

nginx -s stop

Depending on the monitoring interval you will get an email within 5 minutes when your URL can't be reached.


You can also log in and see in the dashboard how much uptime you had and when you were down:

You can restart your web server by doing:
systemctl restart nginx
A few minutes later UptimeRobot will notify you all is good again :)

And surely in the dashboard, we are UP again:
Happy monitoring!

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

In a previous blog post, we configured the webserver on our Compute VM Instance. We added a website and configured the domain dgielis.com to point to this instance.

I got many requests on how to point the domain name to a specific Oracle APEX app. This is what I will cover in this blog post.

Lets start with connecting to our VM:

From a Terminal connect to your Oracle Cloud VM:

ssh -i ssh_key opc@public_ip

The first thing we do, is changing to the root user, as we want to configure the web server. Alternatively in front of every command you can add sudo.

We logged in as the OPC user, to become the ROOT user we do:

sudo su

# open the configuration file we created in the previous blog post
vi /etc/nginx/conf.d/dgielis.com.conf

# add following
  location / {
    rewrite ^/$ /ords/f?p=101:LOGIN_DESKTOP:0 permanent;
  }

# save and quit (:wq)

My config file looks now like this:


# to test Nginx configuration
nginx -t 

# to restart Nginx
nginx -s reload

That's it.

Places are filling up, but there are still some available at both of my acclaimed seminars that I’ll be running in London, UK in March 2020. The dates and registration links are as follows: 23-24 March 2020: “Oracle Indexing Internals and Best Practices” seminar – Tickets and Registration Link 25-26 March 2020: “Oracle Performance Diagnostics and […]

  I previously discussed how the default column order of an Automatic Index (in the absence of other factors) is based on the Column ID, the order in which the columns are defined in the table. But what if there are “other factors” based on new workloads and the original index column order is no […]

We often get the question of "how to be the most productive creating a template for APEX Office Print (AOP)".

Here are two examples of people asking in different ways:



My short answer: I recommend to use the Fast Template Switcher in our AOP Sample App or when you use the on-premises version of AOP, connect to the server and use the AOP Web Editor.

In this blog post, I will do a step-by-step guide on how I believe you will be most performant building your templates and using them in AOP.

If you didn't install APEX Office Print (AOP) yet, please read my blog post Setup APEX Office Print (AOP) to export to PDF, Excel, Word, Powerpoint, HTML and Text

Now, we will add AOP to a button on a page in an APEX app.

Dynamic Action Plug-in

When you have a button on the page, right-click on it and choose "Create Dynamic Action".
Give it a name and as Action, specify APEX Office Print (AOP) - DA [Plug-in]



Data

The most important part of any report is the data it contains. So, the first step is to define where AOP can find all the data you want to use in the report. AOP gives you the ability to get all your data through a hierarchical SQL statement by using the cursor() or JSON syntax. For example, if we want to query all the orders and order lines (details) of a given customer we can specify in the Data Source:

select
  'file1' as "filename", 
  cursor(
    select
      c.cust_first_name as "cust_first_name",
      c.cust_last_name  as "cust_last_name",
      c.cust_city       as "cust_city",
      cursor(
        select 
          o.order_total      as "order_total", 
          'Order ' || rownum as "order_name",
          cursor(
            select 
              p.product_name as "product_name", 
              i.quantity     as "quantity",
              i.unit_price   as "unit_price", 
              APEX_WEB_SERVICE.BLOB2CLOBBASE64(p.product_image) as "image",
              40 as "image_max_width"
              from demo_order_items i, demo_product_info p
             where o.order_id = i.order_id
               and i.product_id = p.product_id
          ) "product"
         from demo_orders o
        where c.customer_id = o.customer_id
       ) "orders"
    from demo_customers c
   where customer_id = :P1_CUSTOMER_ID
  ) as "data"
from dual

In case your data is more complex than you can define in a hierarchical SQL statement, you can also specify a PL/SQL Function or a JSON data source.

AOP Template

Now you want this data in a specific look and feel. AOP allows you to specify your own template written in Word, Excel, Powerpoint, HTML, and Text. A good way to start your template is by using the AOP Template (which is selected by default). This means you don't specify your own template yet, but let AOP generate a starting template for you, based on the data you provided. AOP can generate a starter template in Word, Excel, and HTML.


Based on your data, it will generate a {tag} for every column you have. When AOP sees there are multiple records (a table), it will generate a loop statement {#tag}{/tag} as well.

Now you can iterate over your template to make it exactly as you want it to look like. Just rearrange the {tags} to the specific positions where you want your data to be. Apart from that, you are in Word (or Excel, Powerpoint, ...) and have access to all of the native Word features to create a gorgeous look and feel. If you already have a document, you can also copy and paste the tags from the AOP Template into your existing document.

What I see, is that people adjust the template, upload the template, adjust, upload, adjust, upload over and over. There's actually a much faster way to iterate through template development.

Local Debug

Behind the scenes, the AOP Plug-in generates a JSON file that is being sent to the AOP Server.
To get this JSON, you can go into Shared Components > Component Settings > APEX Office Print and set Debug to Local. This will enable AOP Debug for the entire application. If you just want to put AOP Debug on for the current button, add the following to the Init PL/SQL Code of the AOP Dynamic Action:

aop_api_pkg.g_debug := aop_api_pkg.c_debug_local;

This is what you should see in the Dynamic Action:


When you click the button, you will get the JSON file.

Quickly changing Templates

With AOP we ship an AOP Sample App. This is an APEX application which showcases many features of APEX Office Print:


In the Debugging section, you find a Quick Template Changer link:


The Quick Template Changer allows you to quickly try new templates based on an AOP JSON file.


You drag the JSON file in the first box under Exported JSON.
The JSON will be parsed and the content of the JSON is shown in the JSON Data field.
Next, you drag your template in the Template section and finally, you select the Output you want and hit the Process button.


You can now remove the template and drag-and-drop a new version of the template and hit the Process button again. This way you can quickly see the result while making changes to the template.

Web Editor in AOP On-Premises version

If you download the AOP On-Premises version and run the AOP Server (which is one executable) locally, or you navigate to the server URL where your AOP Server is running, you will see the AOP Web Editor.


This editor is really powerful and has more features than the Quick Template Changer you find in the AOP Sample App. Just as before you drag-and-drop the JSON file in the Exported JSON section.

The AOP Web Editor will parse the JSON and will show a link to the template to Download (unnamed.docx). If you want to change the Template, you just drag-and-drop another template in the Template section. On this screen, you can even prepend and append files and add sub-templates. If you don't like to always select a file from the file system, you can select all files or even a directory and drag-and-drop it entirely in the File cache section, so you can swap files even quicker!


Select the Output and hit the Process button, and presto, you see the output with the (new) template!

But that is not all, you can also change the Data on the fly by going into the Data tab:


Or see the new resulting JSON file after changing the data and template:


In case you want some examples, hit the Load Sample button and select a sample.

I really believe the AOP Web Editor will help you a lot in your development of AOP Reports.

This AOP Web Editor app is actually a nice showcase that AOP can be used with any technology. The Web Editor is written with React.js.

Hope this helps you to be even more productive with AOP!

If you are reading this blog post you are probably searching for an alternative for the deprecated multimedia (or intermedia) feature of the Oracle Database... and you are in the right post as APEX Media Extension is that replacement!

Just like you, I loved the Oracle Multimedia feature in the Oracle Database. I used the feature in many different applications, mostly related to images. For example, when some students upload images of their work in an Oracle APEX app, or teachers upload images to include in tests, I made thumbnails of the images and resized them so they fit really nicely on the page.

Unfortunately, Oracle announced starting in Oracle Database 18c, Oracle Multimedia is deprecated. You can still use it, but you know it will go away at some point... and that is what happened with Oracle Database 19c. Oracle Multimedia is now desupported and doesn't even work anymore. The Oracle Multimedia packages are still there, so your code is still compiling and valid, but it's not doing anything anymore.

Just like you and others, I reviewed and tested different alternatives, but to make a long story short, I wasn't completely happy with any of them. So we at APEX R&D decided to offer a solution that is easy to use and fully supported. There's a PL/SQL API so you can use it just like Oracle Multimedia from the Oracle Database. As we love Oracle APEX, we also provide two APEX Plug-ins: one plug-in which sits on top of the PL/SQL API (which is a server-based solution) and another one which is a pure client-side (JavaScript) implementation. Our goal is that it's as easy as possible to integrate this solution into your Oracle Database and/or APEX app. We named this solution APEX Media Extension and it will be available before the end of the year (2019).


In this initial release, we've focussed on converting media, such as:

• resizing images
• cropping images
• adding watermarks to images
• compressing images 
• changing image formats (jpg, png, ...)

We are eager to hear where else you use Oracle Multimedia!

BUT that is not all... we decided to include this functionality also in APEX Office Print (AOP) (Gold and Enterprise versions)!  So, if you already use AOP, in our upcoming version you are fully covered already :)

Interested? Please leave your email on the APEX Media Extension website and you will be first to know when it's released.

When you followed along with my series of blog posts on the Best and Cheapest Oracle APEX hosting: Free Oracle Cloud you most likely will have gotten an email from Let's Encrypt that your certificate is due for renewal.


To check your certificate, go to your site in a browser and click on the lock:


Yep, corresponds to the email, in 10 days my certificate will expire.

Let's get this fixed! So connect with ssh to your Compute instance (see the previous post in the series if you forgot those commands).

When I connect to my machines I typically first run yum update to get the latest packages installed so we are current with security patches, or just, in general, keep up with the latest software.

sudo su (to become root)
yum update


Time to renew our certificate. It's very easy to do, run

certbot certonly


Ha! Apparently not so simple after all?! We got an error. This might happen when packages are incompatible. Normally yum should take care of that, but as we installed Certbot with Pip, let's upgrade all those components too.

pip install -U pip

As the above error indicates an issue with cryptography, I will update that too.

pip install cryptography --upgrade


Now, let's try to renew our certificate again:

certbot certonly

and type your domain name(s):


Cool, that worked... your certificate is now updated.

To get the new certificate active we restart the webserver (after testing if all is ok):

nginx -t
nginx -s reload


Finally, we check the certificate in a browser to see if the new one is there:


All done, time to relax again for a couple of months.

    In my previous post on Automatic Indexing, I discussed how the default index column order (in absence of other factors) is column id, the order in which the columns are defined in the table. In this post, I’ll explore if this changes if index compression is also implemented. By default, Automatic Indexing does […]

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

At some point you might face the following message: "The request could not be mapped to any database":


Oracle is monitoring usage on your Always Free Account and whenever it finds there's no activity for 7 days, it will stop your database automatically. It will preserve the data in the database, but it won't be accessible anymore.

To fix the issue, log in to your Oracle Cloud Account and go to your Autonomous Database:


You will see the database is in a stopped state. Click the Start button:


The state will change to Starting...


And after a minute it becomes available again:


The above behavior is written in the end-user documentation:
Inactivity Monitoring and Database StoppagePersistently inactive Always Free Autonomous Databases are detected and handled as follows:

• After being inactive for 7 days, the database will be stopped automatically, preserving its stored data. Inactivity measurements leading up to 7 days are based on database connections. Successfully making a SQL*Net or HTTPS connection resets these measurements to zero.
• A database that is automatically or manually stopped and stays inactive for 90 days, cumulative, may be reclaimed and permanently deleted. Inactivity measurements leading up to 90 days are based on the database being inactive or in the stopped state. Starting a stopped database resets these measurements to zero.Start an Always Free Autonomous Database by clicking the Start button on the Oracle Cloud Infrastructure console. Start a stopped Always Free Autonomous Database before 90 days to avoid losing access to its data.

But this week there were some people complaining that although they had activity, their database was stopped anyway. I witnessed the same behavior in my account, so I reached out to Oracle and they confirmed their code to identify inactivity, is not properly accounting for APEX/ORDS usage. They are already working on a fix, which they hope to apply very soon. I will update this post when I get confirmation the fix is in the data centers.

When using external (WEB/REST) services, you often communicate in JSON. So it's important to be able to generate JSON in the format that is expected by the external service.

In the case of APEX Office Print (AOP), we made it super simple to communicate with the AOP server from the database through our PL/SQL API. You just have to enter a SQL statement and the AOP PL/SQL API, which uses APEX_JSON behind the scenes, generates the necessary JSON that the AOP Server understands.

Here's an example of the Order data in JSON: a customer with multiple orders and multiple order lines:

As we are living in the Oracle database, we have to generate this JSON. The data is coming from different tables and is hierarchical. In SQL you can create hierarchical data by using the cursor() syntax.

Here's an example of the SQL statement that you would typically use in AOP (the cursor highlighted in red):

select
  'file1' as "filename", 
  cursor(
    select
      c.cust_first_name as "cust_first_name",
      c.cust_last_name  as "cust_last_name",
      c.cust_city       as "cust_city",
      cursor(select o.order_total      as "order_total", 
                    'Order ' || rownum as "order_name",
                cursor(select p.product_name as "product_name", 
                              i.quantity     as "quantity",
                              i.unit_price   as "unit_price"
                         from demo_order_items i, demo_product_info p
                        where o.order_id = i.order_id
                          and i.product_id = p.product_id
                      ) "order_lines"
               from demo_orders o
              where c.customer_id = o.customer_id
            ) "orders"
    from demo_customers c
    where customer_id = 1
  ) "data"
from dual

From AOP 19.3 onwards, the AOP PL/SQL API not only supports this cursor() syntax but also the native JSON functionality of the Oracle Database (version 12c and upwards).

The query above can also be written as the following using JSON support in the Oracle Database:

select 
  json_arrayagg( 
    json_object( 
      'filename' value 'file1', 
      'data'     value (
          select 
            json_arrayagg(
              json_object( 
                'cust_first_name' value c.cust_first_name, 
                'cust_last_name'  value c.cust_last_name,
                'cust_city'       value c.cust_city, 
                'orders'          value (
                    select 
                      json_arrayagg(
                        json_object(                               
                          'order_total' value o.order_total, 
                          'order_name'  value 'Order ' || rownum,
                          'order_lines' value (
                              select 
                                json_arrayagg(
                                  json_object(                               
                                    'product_name' value p.product_name, 
                                    'quantity'     value i.quantity,
                                    'unit_price'   value i.unit_price
                                  )
                                returning clob)      
                                from demo_order_items i, demo_product_info p
                               where o.order_id = i.order_id
                                 and i.product_id = p.product_id
                            )
                        )
                      returning clob)      
                      from demo_orders o
                    where o.customer_id = c.customer_id
                  )
              )
            returning clob)  
            from demo_customers c
            where c.customer_id = 1
          )
    )
  returning clob) as aop_json
  from dual 

You have to get used to this syntax and have to think a bit differently. Unlike the cursor syntax where you define the column first and give it an alias, using the JSON functions, you define the JSON object and attributes first and then map it to the correct column.

I find the cursor syntax really elegant, especially in combination with APEX_JSON, it's a really cool solution to generate the JSON you need. But I guess it's a personal choice what you prefer and I must admit, the more I use the native JSON way, the more I like it. If performance is important you most likely want to use native database functionality as much as possible, but I go in more detail further in this post. Lino also found an issue with the cursor syntax in the Oracle Database 19c, so if you are on that database release you want to look at the support document.

Before I move on with my test case, if you need more info on JSON in the database: Carsten did a nice blog post about parsing JSON in APEX, and although it's about parsing JSON and this blog post is more about generating JSON, the conclusions are similar. You can read more about APEX_JSON and the native JSON database functions in Tim's write-up on Oracle-Base.

As I was interested in the performance of both implementations, I run a few test cases. There are different ways to test performance, e.g. use dbms_profiler, Method R Workbench, trace, timing the results, ... Below I use Tom Kyte's script to compare two PL/SQL implementations. The interesting thing with the script it's not only comparing timings but also latches, which give you an idea of how hard the database has to work. You can download it from AskTom under the resources section:


Here's my test script:

declare
  l_sql             clob;
  l_return          blob;
  l_output_filename varchar2(100);  
  l_runs            number(5) := 1;
begin
  runStats_pkg.rs_start;
  
  -- sql example with cursor
  for i in 1..l_runs
  loop
      l_output_filename := 'cursor';
      l_sql := q'[
                select
                'file1' as "filename",
                cursor
                (select 
                    c.cust_first_name as "cust_first_name",
                    c.cust_last_name  as "cust_last_name",
                    c.cust_city       as "cust_city"
                   from demo_customers c
                  where c.customer_id = 1 
                ) as "data"
                from dual   
               ]';
      l_return := aop_api_pkg.plsql_call_to_aop (
                    p_data_type       => aop_api_pkg.c_source_type_sql,
                    p_data_source     => l_sql,
                    p_template_type   => aop_api_pkg.c_source_type_aop_template,
                    p_output_type     => 'docx',
                    p_output_filename => l_output_filename,
                    p_aop_remote_debug=> aop_api_pkg.c_debug_local); 
  end loop;  
  
  runStats_pkg.rs_middle;  
  
  -- sql example with native JSON database functionality
  for i in 1..l_runs
  loop
      l_output_filename := 'native_json';
      l_sql := q'[
                select 
                  json_arrayagg( 
                    json_object( 
                      'filename' value 'file1', 
                      'data'     value (select 
                                          json_arrayagg(
                                            json_object( 
                                              'cust_first_name' value c.cust_first_name, 
                                              'cust_last_name'  value c.cust_last_name,
                                              'cust_city'       value c.cust_city 
                                            )
                                          )  
                                          from demo_customers c
                                         where c.customer_id = 1
                                       )  
                    )
                  ) as aop_json
                  from dual 
               ]';
      l_return := aop_api_pkg.plsql_call_to_aop (
                    p_data_type       => aop_api_pkg.c_source_type_sql,
                    p_data_source     => l_sql,
                    p_template_type   => aop_api_pkg.c_source_type_aop_template,
                    p_output_type     => 'docx',
                    p_output_filename => l_output_filename,
                    p_aop_remote_debug=> aop_api_pkg.c_debug_local);                     
  end loop;    

  runStats_pkg.rs_stop;   
end;
/

I ran the script (with different l_runs settings) a few times on my 18c database and with the above use case on my system, the native JSON implementation was consistently outperforming the cursor (and APEX_JSON) implementation.

Run1 ran in 3 cpu hsecs
Run2 ran in 2 cpu hsecs
run 1 ran in 150% of the time

Name                                  Run1        Run2        Diff
STAT...HSC Heap Segment Block           40          41           1
STAT...Heap Segment Array Inse          40          41           1
STAT...Elapsed Time                      4           3          -1
STAT...CPU used by this sessio           4           3          -1
STAT...redo entries                     40          41           1
STAT...non-idle wait time                0           1           1
LATCH.simulator hash latch              27          26          -1
STAT...non-idle wait count              13          12          -1
STAT...consistent gets examina          41          43           2
LATCH.redo allocation                    1           3           2
STAT...active txn count during          21          23           2
STAT...cleanout - number of kt          21          23           2
LATCH.transaction allocation             1           3           2
LATCH.In memory undo latch               1           3           2
LATCH.JS Sh mem access                   1           3           2
STAT...consistent gets examina          41          43           2
LATCH.keiut hash table modific           3           0          -3
STAT...calls to kcmgcs                  64          69           5
STAT...dirty buffers inspected           6           0          -6
STAT...workarea executions - o           2          12          10
STAT...free buffer requested            71          52         -19
STAT...lob writes unaligned             80          60         -20
STAT...lob writes                       80          60         -20
STAT...sorts (rows)                      0          20          20
STAT...execute count                    91          71         -20
STAT...sorts (memory)                    0          20          20
LATCH.active service list                0          25          25
STAT...consistent gets                 183         156         -27
STAT...consistent gets from ca         183         156         -27
STAT...consistent gets pin (fa         142         113         -29
STAT...consistent gets pin             142         113         -29
STAT...lob reads                       160         130         -30
LATCH.JS queue state obj latch           0          42          42
LATCH.object queue header oper         151         103         -48
STAT...workarea memory allocat          66          -6         -72
STAT...db block changes                431         358         -73
STAT...consistent changes              390         315         -75
LATCH.parameter table manageme          80           0         -80
STAT...undo change vector size       8,748       8,832          84
LATCH.enqueue hash chains                1          88          87
STAT...parse count (total)             100          10         -90
STAT...session cursor cache hi         171          71        -100
STAT...opened cursors cumulati         171          71        -100
STAT...free buffer inspected           126           0        -126
STAT...calls to get snapshot s         470         330        -140
STAT...db block gets from cach         958         744        -214
STAT...hot buffers moved to he         220           0        -220
STAT...redo size                    12,016      12,248         232
STAT...db block gets                 1,039         806        -233
STAT...db block gets from cach       1,029         796        -233
STAT...session logical reads         1,222         962        -260
STAT...file io wait time             5,865       6,279         414
STAT...recursive calls                 561         131        -430
LATCH.cache buffers chains           3,224       2,521        -703
STAT...session uga memory          196,456           0    -196,456
STAT...session pga memory        1,572,864           0  -1,572,864
STAT...logical read bytes from   9,928,704   7,798,784  -2,129,920

Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
       3,853       3,180        -673    121.16%

There are many different iterations of this test, using bind variables, etc. It seems "logical" that a native DB implementation is better performance-wise than a combination of PL/SQL (APEX_JSON) and SQL (cursor). But I always recommend you just run the test in your own environment. What is true today, might be different tomorrow and a lot comes into play, so if there's one thing I learned from Tom Kyte, it's don't take things for granted, but test in your unique situation.

So, in real life using AOP, will you see a big difference? It depends on the complexity of your SQL statement and data, how many times you call a report etc. but my guess is, in most cases, it's probably not much of a difference in user experience.

A simple test would be to do "set timing on" and compare the implementations:


Or if you are using AOP on an Oracle APEX page, you can run your APEX page in Debug mode and you will see exactly how long the generation of the JSON took for the data part:


Happy JSON'ing :)

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

Today I got an email that my Oracle Cloud account was Expired. While I have an Always FREE Oracle Cloud, when I signed up I also got some extra credits that lasted for a month. Those credits are no longer valid.


When you log in to your Oracle Cloud Dashboard you will get a notification on top too, but nothing to worry about.


It has some consequences tho, on the menu, some options are grayed out. The one I actually use is the Email Delivery, which seems to be grayed out too although normally you should be able to send 1,000 emails per month. So maybe grayed out also means, not full service.


When I checked it out, it said it's part of the paid plan. I remember some discussions at Oracle Open World where they recommend upgrading to a Paid account, but as you only use the Always FREE services, you are not charged.


So I decided to upgrade to a Paid account: Pay As You Go:


You have to provide a Credit Card, but that was a bit of an issue for me. Apparently, Safari is not really working well with this screen, so I switched to Chrome. The next hick-up I had was when I added my AMEX card... it said it was an invalid card.


Then I used my VISA card and that seemed to work well:

Click the Start Paid Account:


Finally, it will say your payment method will be reviewed and after that you are live.


It wasn't immediately clear for me I had to wait for the confirmation email, but when I went to Payment Method again, I saw the review was still in progress:


And a few minutes later I got the email that my account was upgraded:


When you look at your Oracle Cloud Dashboard, there's a cost calculator, so you see how much you have to pay. As long as I use the Always FREE components, I expect the amount to stay 0 :)


But the nice thing now is that you have access to all of Oracle Cloud again (e.g. Email Delivery).

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

By now we have seen how you can set up the different components from the Always Free Oracle Cloud.

During Oracle Open World I talked to the people behind the Always Free Oracle Cloud, and they told me that when your account is inactive for a specified amount of time (I forgot if it's 5 days, or a week or more?), your instance is being backed-up to the Object Storage. You can see it as a VM which is being put in stand-by or halted and saved to disk. When you need it again, it can be restored, but it takes time and it might be annoying when you don't know this is what is happening.

If you have a production app running in the Fee Oracle Cloud, be sure people use your app at least once inside the window Oracle foresees. Maybe in the future, Oracle could foresee a setting where we can specify the (in-)activity window as a developer.

I'm really impressed by this free offering of Oracle and see many use cases for development environments and small to midsize applications. I believe the limits we get in the free plan are really generous of Oracle and much more than any other cloud provider. Here's a quick overview of what it looks like at the time of writing:

• 2 Autonomous Databases, each with 1 OCPU and 20 GB storage
• 2 Compute virtual machines, each with 1/8 OCPU and 1 GB memory
• Storage:  2 Block Volumes, 100 GB total. 10 GB Object Storage. 10 GB Archive Storage.
• Additional Services:  Load Balancer, 1 instance, 10 Mbps bandwidth. Monitoring, 500 million ingestion data points, 1 billion retrieval data points. Notifications, 1 million delivery options per month, 1,000 emails sent per month. Outbound Data Transfer, 10 TB per month.

So what if you outgrow these limits? It means your applications are successful, so you can be proud of that :) and at that time hopefully, there's enough revenue to upgrade to a Paid Oracle Cloud plan. This can be done super easy... you click the upgrade to the paid plan button and there you go!Oracle will copy your DB, instance, ... and you go from there.
The way that Oracle is doing the upgrade is really cool, as it means you keep your free instance. So I see myself doing some development on the free instance, then for production upgrade to a paid plan. At that time I still have the development environment. The other free service could be the TEST environment, so you have DEV, TEST both free and PROD paid.

If you didn't check it out by now, go and try out the FREE Oracle Cloud yourself by going to https://www.oracle.com/cloud/free/ :)

Thanks Oracle!

This post is part of the OGB (Oracle Groundbreakers) Appreciation Day 2019, a thank you to everyone that makes the community great, especially those people that work at keeping us all moving!

Before I give my tip on how to add an error message from your PL/SQL code in your Page Process to a notification message in Oracle APEX, I want to start with thanking some people.

What keeps me going are a few things:

• The innovations of technology and more specifically the Oracle Database, ORDS, and Oracle APEX. I want to thank all the developers and the people behind those products. They allow me to help other people with the tools they create and keep on learning about the new features that are released. 
• I want to thank the fantastic #orclapex (Oracle APEX) and Groundbreakers community. I believe we are a great example of how people help and support each other and are motivated to bring the technology further. Over time I got to know a lot of people, many I consider now friends.
• I want to thank you because you read this, show your appreciation and push me forward to share more. I'm passionate about the technology I use. I love helping people with my skill set of developing software and while I learn, share my knowledge through this blog. 

So back to my tip of today... how do you show a message in the notification on a page?

You can do that with the APEX_ERROR PL/SQL API.

To see the usage yourself, create an empty page, with one region and a button that submits the page.In the Submit Process, simulate some PL/SQL Code where you raise an error.
For example:

That's it! Now you can get your errors in the notification message area of your Oracle APEX Page.

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

By now we have seen how you can set up the different components from the Always Free Oracle Cloud. In this post, I want to show you how to keep your compute instances, databases, and load balancers up-and-running.

During Oracle Open World I talked to the people behind the Always Free Oracle Cloud, and they told me that when your account is inactive for a specified amount of time (I forgot if it's 5 days, or a week or more?), your instance is being backed-up to the Object Storage. You can see it as a VM which is being put in stand-by or halted and saved to disk. When you need it again, it can be restored, but it takes time and it might be annoying when you don't know this is what is happening.

There's a very easy way to keep your Always Free Oracle Cloud running all the time... just put activity on those machines, databases and APEX apps :) I'm not suggesting to log in yourself every day, rather create an automated job to create some activity on your account!

Log in to Oracle APEX and go to SQL Workshop > RESTful Services. By default the schema you are in is not registered with ORDS, so no RESTful Services are allowed. Click the Register Schema with ORDS button:


It will ask if you want to enable RESTful Access and if you want to install the Sample Service. Leave all defaults and click the Save Schema Attributes:


You are now all set to create your own RESTful service:


As a sample schema is created, we will just adapt one of the existing RESTful services.
Open the Modules and oracle.examples.hr > version > GET:


Here I changed the Source Type to be Query and the Source I made:

select apex_web_service.make_rest_request('https://dgielis.com', 'GET')
  from dual

Change my website to your website. Hit the Apply Changes button and copy the full URL and test it in a browser if you get something back.

Why am I'm doing that? This REST web service calls basically our web server on the compute instance. So on every call of this REST web service, our VM will have some activity. 

The last step is to complete the loop, and make sure that ATP has some activity.

Log in to your VM and become root (see in previous posts how to do that if you need help with this step). Add a cronjob to run a command to call our REST web service every hour:

crontab -e

0 * * * * curl -sL -w "%{http_code}\\n" "https://dgielis.com/ords/cloud/hr/version/" -o /dev/null

To check your crontab do:

crontab -l


There you go, now the compute instance is doing a call to ATP and ATP in return is doing a call to the VM :) we will now always have some activity on our instance and Oracle will never shut it down.

That's it... thanks so much for staying with me during the series and all the best with the Always Free Oracle Cloud.


Update 6-OCT-2019: because of Juergen's feedback, I want to explain further the reason for this post. This blog post was not intended to keep instances running for nothing. If you are a developer and want to try something and don't do anything with it afterward, it makes all sense for Oracle to backup/mute the instance so resources can be better used.
However, if you wrote an app for somebody who only uses it occasionally, and it falls in the window of Oracle muting it, the above technique can be used to keep it running. In that case, from the end-users perspective, they don't understand why they get an error and as a developer, you don't want to go in every time to fire things up again. I also wanted to explain what is happening when you don't use your instance, it's not staying there forever. Depending on when you read this, things might have changed, but this is the info I got from Oracle at Open World. In the comments, I went in more detail on two use cases I have myself.

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

In my blog post Create a VM Instance (Compute Cloud) we created a VM instance in the Free Oracle Cloud. The cool thing is that you get two VMs for free. In this post, we will set up the other always free compute instance.

Just like when we created our first instance, hit the Create a VM instance:


Give your instance a name and before I just hit the Create button, BUT this time you want to create the Show Shape, Network and Storage Options first:


The most important part of that screen is the "Assign public IP address" section. If you don't need this Compute instance to be accessible from the internet you can ignore it, but if you want to host a website, for example, you might want to check it. If you didn't do it, you can always add a public IP later, but I personally found it cumbersome and hard to understand the network piece. I had to do many different steps to get it to work to have an internet connection to that machine, while when you have a public IP address, Oracle does everything for you... anyway, it depends on your use case what you need, but I do want to highlight it. Also, it seems that the default changed from when I wrote the first post; by default, you don't have a public IP address. It might be that Oracle is trying to push you to use a Load Balancer (see later on in this blog post) and that might actually make sense.


Clicking the Create button will show that your instance is being provisioned.


When you go back to the overview you should see both of your Always Free Compute instances:


Clicking on the name, you will get the details. This screenshot shows when you don't specify a public IP address.


To access that machine, as it doesn't have a public IP, I connected to my first instance and from there, as I am on the subnet, I can connect to the Private IP Address:


An alternative for a URL to go directly to your VM instance is to front it with a Load Balancer.

Which brings us to the Load Balancer topic. With the Always Free Oracle Cloud, we also get a Load Balancer for free. There are different use cases for using a Load Balancer, but here are my own reasons why I have used a Load Balancer before:

1. Distribute the traffic automatically over different machines. For example, when you use our APEX Office Print (AOP) Cloud you will actually hit our load balancer, behind the load balancer we have two to five different machines. It's not only to handle the large number of prints we get, but it also makes our lives easier when we want to upgrade without downtime. We upgrade one clone instance, and when done, new machines are brought online and old ones are shutdown. We patch our own service with zero downtime.
2. The Load Balancer has the SSL certificate and handles the HTTPS requests while the backend servers have HTTP.
3. On a Load Balancer, you have integrated health checks, so you can be warned when things go wrong, even when there's only one server behind the Load Balancer.

So lets get started to set up a Load Balancer in the Oracle Cloud:

Click on Networking > Load Balancers:


Click the Create Load Balancer button:


It will ask for a name and type. For the Always free instance, use Micro with Maximum Total Bandwidth.
By default Small is selected, so don't forget to change it:


Next you want to add a Backend to this Load Balancer, so click the Add Backends button:


In the pop-up you can select the instances you want to put behind this Load Balancer:


Furthermore, on the screen you can select a Health Check Policy:


In the next step, you can upload the SSL certificate, in case you want the Load Balancer to be accessible through HTTPS. You can also choose to just configure the Load Balancer for HTTP (which I don't recommend):


Hit the Create Load Balancer and you will get an overview that the Load Balancer is being created:


Once it's ready the icon turns green and you will see the Public IP Address of your Load Balancer:


Instead of putting the IP Address of your instance directly in the DNS of your domain name, you put the IP Address of the Load Balancer in.

A Load Balancer can do much more, you can have different Rules, SSL tunneling, etc. You can read more about that in the online documentation.

Hopefully, now you know how to set up a second compute instance and you have an idea what a Load Balancer can do for you.

We are almost done with this series... but you definitely want to read the next blog post, which is the last one where I give some important information to keep your Always Free instance running.

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

In this post, we will configure the Oracle Cloud to support our instances, databases and Oracle APEX to send out emails. In my blog post 5. Setup APEX in ATP and create first APEX app, I initially said you can't use APEX_MAIL in APEX in ATP, but I was wrong, so I few days after my post I updated it, to point you to the documentation with the configuration steps you have to do to make it work.

The reason I thought you can't use APEX_MAIL was that during my tests, sending emails failed. I hadn't read the documentation ;) In this post, I will share how I got it to work after all.

The first thing you have to do is create an SMTP Credential for your user. You do that by logging into your Oracle Cloud account, go to Identity > Users and select your user:



Click the SMTP Credentials in the left menu:


Hit the Generate SMTP Credentials button, and give it a name:


On the next screen, you will see the USERNAME and PASSWORD. Take a note of this as you won't get to see it anymore after:


You will come back to the overview screen, but again, as far as I can see there's no way to get the password again, so if you lose it, you need to set up one again:


Now we will let Oracle APEX know about those parameters. Login as ADMIN through SQL Developer for example (see this blog post on how to do that) and run the following statement:

BEGIN
  APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_HOST_ADDRESS', 'smtp.us-ashburn-1.oraclecloud.com');
  APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_USERNAME', 'ocid1.user.oc1..xxxxxxxxxxx@ocid1.tenancy.oc1..xxxxxxx');
  APEX_INSTANCE_ADMIN.SET_PARAMETER('SMTP_PASSWORD', 'Fxxxx');
  COMMIT;
END;
/


Here is a screenshot when running:



Log in to Oracle APEX, go to SQL Workshop and try to send out emails:


It says statement processed, but when you query the APEX_MAIL_QUEUE, you will see it's still stuck with an error ORA-29278: SMTP transient error: 471 Authorization failed:


There's one more step you have to do, specify the email addresses you want to allow to send emails from. Go in your Oracle Cloud Account Dashboard to Email Delivery and click the Email Approved Senders and hit the Create Approved Sender button




Add the email address you want to send emails from and hit the Create Approved Sender button:

In the overview you will see all allowed email addresses:


When we try to send again and check the APEX_MAIL_LOG, we see the emails are effectively sent:


That's it, you can now send emails out of your APEX apps :)

We are almost done with the series. In the next post we will create a second compute instance and set up a Load Balancer.