Sunday, January 19, 2020

Print PDF from Oracle APEX to Local or Remote Printer

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.

Tuesday, January 14, 2020

Printing to local (Zebra) printer from Oracle APEX in the Cloud

These days Oracle Application Express (APEX) is being used more and more in "local" applications like a bar, a restaurant, a power station, etc. On many occasions, they also have the requirement to print something to a local printer.

As the Always FREE Oracle Cloud is becoming more and more popular and stable, people use that service to host their APEX apps, instead of installing Oracle XE with APEX locally.

In this blog post, I will demonstrate how to print from the Oracle Cloud to a local Zebra printer by using APEX Office Print (AOP).

In my test case, I have a Zebra Printer GK420t connected to my local computer.

It's a 3 step process:

  1. Run APEX Office Print (AOP) on a local pc
  2. Make AOP available to the Oracle Cloud
  3. Use the AOP APEX Plug-in to do the print

1. Run APEX Office Print (AOP) on a local pc

The first thing we do is download the on-premises version of APEX Office Print (AOP) for your Operating System. Unzip the file and go to the server directory and run APEX Office Print.

./APEXOfficePrintOSX64

The first time you run AOP, AOP will create an aop_config.json file, which holds the configuration of AOP. We want to make a small modification to the file to include the local printer name.
So exit AOP again (CTRL-C) and do:

vi aop_config.json


On the last lines, you see post_process_commands, here you define your command to print to your local printer. Note that you can have as many post process commands by comma separating them.

If you want to know which printers are available on your machine, you can do:

lpstat -p | awk '{print $2}'


It will not only show you the local (connected) printers, but also the network printers. You can use any of those with AOP.

That's it! Just run AOP again and you are all set 😀



2. Make AOP available to the Oracle Cloud

Next, we need to make this local AOP server known to the Oracle Cloud. There are different ways of doing this, you could create a domain and point to the IP address of the local machine, but as the Oracle Cloud only accepts HTTPS, you would also need to set up an SSL certificate (e.g. with Letsencrypt). As this is a local machine, there's a high chance that the IP changes, so I looked for another solution which makes it easy to expose a local webserver to the internet.

This is exactly what a tool called ngrok is doing. Ngrok creates a public URL for a local webserver, in our case AOP.

Installation of ngrok, is as simple as installing AOP: download, unzip, run!

Download ngrok, unpack and run the executable:

./ngrok http 8010


It will show you the URL you can use to access your local AOP server, both HTTP and HTTPS.
In the Oracle Cloud, you always want to use the HTTPS connection.

3. Use the AOP APEX Plug-in to do the print

Import the AOP plug-in in your Oracle APEX app and specify the HTTPS URL as the AOP URL in the Component Settings of the Plug-in. Next, create a Dynamic Action and specify the Post Processing Command you want to run:


That's it! Don't think it can be any simpler than that ;)

Thursday, December 26, 2019

Important update of ORDS... release 19.4

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!

Monday, December 23, 2019

Free Oracle Cloud: 18. Monitoring your website and APEX app

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!

Free Oracle Cloud: 17. Configure domain to redirect to APEX app

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.

Friday, December 13, 2019

The best way to be productive with APEX Office Print (AOP)

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!

Wednesday, December 11, 2019

Alternative for Oracle Multimedia: APEX Media Extension

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.

Tuesday, December 10, 2019

Free Oracle Cloud: 16. Renewing Let's Encrypt certificate

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.

Update: Morten made the remark why not to automate the renewal:


It's a great comment! When you look at the Certbot instructions, it actually gives you the steps to auto-renew, so you might have already done that. I actually have this running on some of our servers, but I didn't include this step in my initial blog post when we configured the webserver.
So, in case you didn't set up the automatic renewal, or the automatic renewal failed, you have the steps above to fix it.