The announcement by Oracle about the new Always Free Cloud Tier is fantastic news. You can now have two free Autonomous Databases with APEX up and running in a few minutes. It’s free for anyone to use and is great for existing APEX developers or anyone who wants to see what APEX is capable of. Before you read on, you can find out more about our APEX Services here.

So what do you get?

• 2 Always Free Autonomous Databases with 1 OCPU and up to 20GB of storage. Each with APEX, SQL Developer Web and RESTful services.
• 2 Compute VMs, each with 1/8 OCPU and 1GB memory.

What are the limitations for an APEX developer?

• Authentication scheme used to access App Builder.
• Ability to submit and approve self-service workspace requests and change requests.
• Web proxy, Oracle Wallet, and print server configuration.
• Daily limits of outbound web service calls and email messages.
• An option to make insecure outbound web service calls.

• HTTP Header Variable.
• LDAP Directory.
• Oracle Application Server Single Sign-On.
• PDF, Excel, and Word printing options are disabled.
• Only SMTP Application Express instance parameters may be set using the APEX_INSTANCE_ADMIN package.
• Vanity URLs or custom domain names are not supported.

After reading the announcement and reviewing the limitations, I wanted to know how easy it would be to get an APEX environment up and running in the free cloud. I also wanted to know if I could use a third-party solution for PDF printing.

I am pleased to say I found it very straightforward to get my first Autonomous Database and APEX environment up and running, the whole process only took me about 15 minutes.

For generating PDFs from APEX, I decided to use PL/PDF as it’s simple to install and only requires a new schema and a set of packages, tables and types to be created. It is also quick to develop a sample test print. If you have not used it before you can download a trial from www.plpdf.com.

Again, I am pleased to say I have no issues doing this in the Free Cloud environment and was able to have a basic APEX application generating some sample PFDs within 30 minutes. This is how I did it.

Step one – Create a new Workspace

The first step was to open my cloud environment and access the service console for my ATP database. From here, I opened the development menu and selected the “Oracle APEX” option.

The next step was to log into the APEX application builder as Admin and create a new workspace and database schema, which I named “DEMO”.

Step two – Install PL/PDF

Next, I downloaded and installed the latest version of PL/PDF using the installation guide and SQL Developer Web to run the scripts. This consisted of the following steps.

1. Login as ADMIN and create the PLPDF user and grant the required privileges.
2. Login as PLPDF and create the database objects (tables, packages and types).
3. Recompile the schema using dbms_utility.compile_schema.
4. Grant the required privileges on the PLPDF objects to the DEMO schema used by the APEX workspace.
5. Login as DEMO and create the required synonyms to the PLPDF objects.

Step three – Create test packages to generate a basic PDF

The next step was to create a procedure to generate a sample PDF file using PL/PDF and a procedure to call from APEX to download it.

Simple procedure to generate a test PDF

The following procedure generates a PDF using a string passed in as a parameter and writes it to the “store_blob” table (created during the PL/PDF installation).

PROCEDURE gen_sample_pdf(pv_sample_text IN VARCHAR2) IS
    vl_pdf BLOB;
  BEGIN
  
    DELETE FROM store_blob
    WHERE filename = 'SimplePDF.pdf';
  
    plpdf.init();
    plpdf.newpage;
    plpdf.setprintfont(p_family => 'Arial',
                       p_style  => NULL,
                       p_size   => 16);
  
    plpdf.printouttext(p_x   => 20,
                       p_y   => 20,
                       p_txt => pv_sample_text);
  
    plpdf.senddoc(p_blob => vl_pdf);
  
    INSERT INTO store_blob
      (blob_file,
       created_date,
       filename)
    VALUES
      (vl_pdf,
       SYSDATE,
       'SimplePDF.pdf');
  
  END gen_sample_pdf;

I also wrote this procedure for calling from an APEX page to download the PDF.

PROCEDURE download_report(pv_filename IN VARCHAR2) IS

  CURSOR c_pdf IS
    SELECT *
    FROM store_blob
    WHERE filename = pv_filename|| 's';

  lr_pdf c_pdf%ROWTYPE;

BEGIN
  OPEN c_pdf;
  FETCH c_pdf
    INTO lr_pdf;
  IF c_pdf%FOUND THEN
    owa_util.mime_header(ccontent_type => 'application/pdf',
                         bclose_header => FALSE);
    htp.p('Content-Disposition:  attachment; filename="' || lr_pdf.filename || '"');
    owa_util.http_header_close;
    wpg_docload.download_file(lr_pdf.blob_file);
  END IF;
  CLOSE c_pdf;
END download_report;

I also decided to make sure I could generate a simple PDF using a Word Template and the plpdf_reporter_pdf.genCode function. Again this is simple to do (there are tutorials available online) and I had no issues generating it in the ATP Cloud environment.

Step four – Create an APEX application

The last step was to create a new application within the DEMO Workspace to call my two PDF generating packages to test everything worked.

On the home page, I added the following to generate and download my two test PDFs.

1. A text item for the text to print on the first sample PDF.
2. A submit button for each report.
3. A branch for each submit button to redirect back to the home page with a request.
4. A pre-header process to fire for each request to call the download_report procedure.

The first procedure generated a basic PDF with the text I entered into the “Sample Text” text item.

The second created using a Word template generated a PDF with the current date and time.

For more information, get in touch with our experts to find out more about our Oracle APEX Services, or book a meeting...

Author: Colin Archer 

Job Title: Senior Oracle APEX Development Consultant

Bio: Colin is a Senior Development Consultant at DSP-Explorer with 20 years’ experience of analysis, design, and development of bespoke Oracle applications for a wide variety of business functions. Building on his previous experience of Forms and PL/SQL he is now focusing on developing high quality fit for purpose solutions using APEX.