Apex Interactive Report Tip #1: Aggregating numbers in string columns

I have been working on an Interactive Report based on a collection recently, to work around the fact that the SQL query for Interactive Reports must be static, and my requirement was to run different queries based on user input.

One problem is that since all columns in a collection are varchars (strings), the built-in aggregation feature of Interactive Reports does not work (it only allows you to aggregate on numbers, which is quite sensible, I guess). Normally you could always convert the string column to a number directly in the underlying query, but since my queries were dynamic (the whole point of using collections), that was not an option.

However, after some fiddling around I discovered that you can aggregate based on computed columns.

So, let's say we have this "transaction amount" column in our report, which consists of numbers but which Apex interprets as a string since it is retrieved from a varchar2 column in the apex_collections view:



Use the "Compute" menu item of the Interactive Report to create a new column, using the TRUNC function (for some reason, the TO_NUMBER function is not available from the list of functions, but TRUNC works -- just remember to include the number of decimals as the second parameter if you need decimals) and setting the appropriate numeric format mask:



We can now see that the new column has been added as a numeric column, since it is right-aligned instead of left-aligned (and also with a nice format mask):



The new column is now available under the "Aggregate" menu item of the Interactive Report, so we can create a Sum of the numbers:




After adding a "Control Break" to the report, we can see that the aggregation works:



Did I mention that I really like Interactive Reports? :-)

How to integrate PL_FPDF with Apex

In my previous post about the free PL_FPDF package that allows you to produce PDF documents from PL/SQL, there was a comment asking for a step-by-step guide on how to integrate this with Oracle Application Express (Apex).

This is really quite simple. By default, the pl_fpdf.output procedure will "print" your PDF document to the web browser along with a header that instructs the browser that this is a downloadable document. All you need to do is to call your procedure that generates the PDF document from a page process within Apex.

Here are the steps:

1. Start by compiling the demo procedure from my previous post into the parsing schema of your Apex application.

2. In Apex, go to your application and add a new, blank page. Let's assume the new page number is 4.

3. Under the "Page Rendering" section, add a new Process of type "PL/SQL". The name of the process can be anything, but let's call it "Produce PDF". Make sure that the point is "On load - before header".



4. In the process source, add the following PL/SQL code:

begin

  -- call the procedure to generate the PDF document and send it to the browser
  test_pl_fpdf;

  -- stop the Apex engine from running the rest of the page
  apex_application.g_unrecoverable_error := true;

end;

5. Add a link to the new page (page number 4 if you follow my assumption in step 2) from any other page in the application.

6. Run the application. Now, whenever you navigate to page 4 the PDF file will be downloaded to the browser.

That's all you need to integrate PDF generation into your Apex application.

Free PDF package for PL/SQL

I'm sure you've heard about PL/PDF, the PL/SQL-based solution that allows you to create PDF documents directly from your database. PL/PDF is a good product, but it is not free (the current license price is USD 600 per database server).



However, I just came across a free alternative called PL_FPDF, which is a PL/SQL port of the PHP-based FPDF. The latest version of PL_PDF seems to have been released about a year ago, and I can't believe I haven't seen or heard about it before now... !

UPDATE, MARCH 2011: Also check out this alternative PDF generation package by Anton Scheffer. It seems simpler and more robust than the PL_FPDF package, but may lack certain features. Be sure to evaluate both!

PL_FPDF is just a single package, so installation is a snap. Note that it uses the ORDImage data type to place images in PDF documents, so if you are running Oracle XE (which doesn't include the ORDImage data type), you need to comment out the few procedures that deal with this data type (and obviously you will not be able to include images in your PDF documents...).

After a few hours of experimentation, I was able to produce a semi-complex PDF document that tests a number of features in PL_FPDF. My test script is included below for your convenience. Note that the default assumption is that output goes to the web browser via the HTP package, but it should be trivial to add a procedure that saves the resulting BLOB to a database table instead.

Note: Since PL_FPDF is based on FPDF, I found the online documentation for FPDF (see link above) very useful in order to find out how the API for PL_FPDF works.

create or replace procedure test_pl_fpdf
as
l_text varchar2(32000) := 'First, reduce actual complexity by eliminating unnecessary features and then hiding what you can''t eliminate. Secondly, reduce perceived complexity by minimizing visual noise and reusing elements. And finally, use the blank state to help orient users. Minimizing complexity in the user interface will help people learn your application more quickly, use it more effectively and be happier all the while.

As jazz musician Charles Mingus said, "Making the simple complicated is commonplace; making the complicated simple, awesomely simple, that''s creativity."';

begin

-- create document in portrait mode, use measurements in millimeters (mm), and use the A4 page format
pl_fpdf.FPDF('P','mm','A4');
pl_fpdf.openpdf;

-- display full page, two facing pages 
pl_fpdf.setdisplaymode ('fullpage', 'two');

-- a procedure that will be called at the end of each page
-- pl_fpdf.setfooterproc('demo.test_pdf_footer');  

pl_fpdf.AddPage();

-- set up some headers
pl_fpdf.SetFillColor(255,128,128);
pl_fpdf.SetFont('Arial','B',14);
--this header will be filled with the background color 
pl_fpdf.cell(40,7,'TABLESPACE', 1, 0, 'L', pfill => 1);
pl_fpdf.cell(40,7,'CONTENTS', 1);
pl_fpdf.cell(40,7,'INITIAL EXTENT', 1, 1);

pl_fpdf.SetFont('Arial','',14);

for l_rec in (select tablespace_name, contents, initial_extent from dba_tablespaces order by 1) loop

pl_fpdf.settextcolor(0,0,0);
pl_fpdf.cell(40,7,l_rec.tablespace_name,'B');
pl_fpdf.cell(40,7,l_rec.contents,'B');
-- some conditional formatting
if l_rec.initial_extent > 66000 then
pl_fpdf.settextcolor(255,0,0);
else
pl_fpdf.settextcolor(0,0,0);
end if;
pl_fpdf.cell(40,7,l_rec.initial_extent,'B', 1, 'R');

end loop;

-- a page that shows how to position chunks of text (with automatic line breaks) on the page

pl_fpdf.AddPage();

pl_fpdf.SetFont('Arial','B',16);
pl_fpdf.setxy (100, 20);
pl_fpdf.Cell(10,10,'Cool Quote 1:');

pl_fpdf.setxy (100, 50);
pl_fpdf.SetFont('Times','',12);
pl_fpdf.multicell(100,0,l_text);


pl_fpdf.SetFont('Arial','B',16);
pl_fpdf.setxy (10, 130);
pl_fpdf.Cell(10,10,'Cool Quote 2:');

pl_fpdf.setxy (10, 150);
pl_fpdf.SetFont('Times','',12);
pl_fpdf.multicell(100,0,l_text);

-- a page that demonstrates some simple drawing with lines and rectangles

pl_fpdf.AddPage();

pl_fpdf.SetFont('Arial','B',14);
pl_fpdf.Cell(0,0,'And now, some beautiful line art...',0,1,'C');

pl_fpdf.line (10,10, 50, 50);
pl_fpdf.rect (50,50, 50, 50);
pl_fpdf.setdrawcolor(0,255,0);
pl_fpdf.line (150,150, 50, 50);

pl_fpdf.setdrawcolor(0,0,0);

-- a simple table of employees, without headings

pl_fpdf.AddPage();

for l_rec in (select empno, ename from emp order by ename) loop

pl_fpdf.cell(40,7,l_rec.empno, 'B');
pl_fpdf.cell(40,7, l_rec.ename, 'B', 1);

end loop;


pl_fpdf.SetFont('Arial','B',48);
pl_fpdf.settextcolor(0,0,255);

pl_fpdf.setxy (100, 250);

pl_fpdf.cell(80,10,'THE END', palign => 'C');

pl_fpdf.Output();

end test_pl_fpdf;

Conclusion: While PL/PDF is more advanced in terms of features, PL_FPDF might be for you if you just need some simple PDF reports in your application.

Native PL/SQL support in DB2

As reported by Steven Feuerstein in his PL/SQL programming newsletter this month, it is now possible to compile and run PL/SQL applications in IBM's DB2 database! Check out this demonstration video and more details at IBM's Developerworks.

Of course, the list of supported built-in packages is rather short (for example, UTL_HTTP and the XML packages are missing), but they do have support for REF CURSORS, CONNECT BY, and other advanced Oracle features.

This is cool, because it opens up a new market for all us PL/SQL developers out here.

Worth mentioning, too, is that EnterpriseDB (based on PostgreSQL) also has PL/SQL support.