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