Forms-Apex Integration

There are still plenty of companies that use client/server Forms6i. For one of those companies I was asked to make a form to display PDF documents that are stored in the database. Well, you can save the file to a public directory on the database server and open this file with Acrobat Reader.

I found it more challenging to see if I could integrate client/server Forms with Apex. Not surprisingly, it is surprisingly simple to achieve this.
Strictly speaking, I don't really need an Apex page for this. However, it is a simple example to show you how you can do this with any Apex page.

Start with a database procedure to retrieve the PDF from a BLOB column.

create or replace procedure showReport(i_id  in number )
is
  vBlob         blob;
begin
  select report_content
  into   vBlob
  from   demo_reports
  where  id = i_id;
  if dbms_lob.getlength(vBlob) >0 then
    owa_util.mime_header('application/pdf',false);
    htp.p('Content-length: ' || dbms_lob.getlength(vBlob));
    owa_util.http_header_close;
    wpg_docload.download_file(vBlob);
  end if;
end showReport;

Next, create an Apex application that uses no authentication to avoid the logon screen when calling Apex for the first time.

Create an empty page in this application, e.g. page 100.

Add one hidden page item P100_ID.

Create an On Load, Before Header PL/SQL process on this page:

showReport(nv('P100_ID'));

Now create a form based on your demo_documents table. Include the id (primary key) of the table, but not the BLOB content. Forms wouldn't know what to do with this.

It is nice to make some column look like a real hyperlink. So, remove the field bevel, make the background colour the same as the canvas colour, and choose underline from the Font option. The form will look something like this



On this "hyperlink" column you add a when-mouse-click trigger:

if :id is not null then
  host('cmd /c start http://server:port/apex/f?p=111:100:::::P100_ID:'||:id);
end if;

That's it!


Since there is no login needed, this is not very secure. I am working on a solution for that by passing a generated session id from forms to Apex. I hope to blog the solution soon. For the time being, this is a good solution for public reports.