Forms-Apex Integration (3)

In previous posts I gave a simple example how to display a stored PDF document from a Forms 6i client/server application using Apex. In this case it is not really necessary to create an Apex page, since you can call the stored procedure directly from the mod_plsql gateway. If you are not using an Apex page, you need to program some extra lines in your code for error handling.

If you are using an external gateway, it is just like any other call via mod_plsql to a stored procedure. If you are using the embedded gateway (XE or Oracle 11g) you need an extra step. Luckily Dietmar Aust has described the steps. You can also RTFM.

The Apex page called two procedures: check_session and showReport. You have to combine these two into one procedure stored in the database.

showreport(i_id            in number
          ,i_user_id       in varchar2
          ,i_sessionid_id  in varchar2)

Instead of raising an error that can be handled by an Apex page, you have to return your own error page if the session is not valid.

exception
   when others then
      -- Invalid session_id
      owa_util.mime_header ();
      htp.htmlopen;
      htp.bodyopen;
      htp.p('Access denied.');
      htp.bodyclose;
      htp.htmlclose;

The host command in Forms will now be a direct URL to showReport.
One little trick you need here: you have to escape the ampersand character with a caret (^) since an ampersand is a special character in DOS.

host('cmd /c start http://server:port/apex/showReport?'  
      ||'i_id='||:id
      ||'^&i_user_id='||user
      ||'^&i_session_id='||v_sessionid
    );

Forms-Apex Integration (2)

(PS: I am using SyntaxHighlighter for the first time. It works for IE, but not for Firefox, yet.
PS2: I fixed the Firefox issue. Apparently Firefox cannot interpret a css file on googlecode. The trick is to include the css in the blogger template.)

Yesterday I posted a simple solution for viewing stored PDF documents in a Forms6i client/server application using Apex. There was one catch: it was not secured in any way yet.

The security I have added relies on a simple principle: every call made to the Apex URL needs a unique id that can be used only once. The form generates this unique id, stores it in the database and passes it to Apex in the URL.

The Apex page checks if the unique id exists in the database. If so, it displays the page. If not, it returns an error message.
The Apex page immediately deletes the id from the database, so it cannot be reused in a modified URL.

Here is what I did.
First, create table user_sessions and a new procedure:

create or replace procedure check_session(i_user_id    in varchar2
                                         ,i_session_id in varchar2)
is
  v_dummy   varchar2(1);
begin
  select 'x'
  into   v_dummy
  from   user_sessions
  where  user_id  = i_user_id
  and    session_id = i_session_id;

  -- Session is valid. Delete the one time session id
  delete
  from   user_sessions
  where  user_id  = i_user_id
  and    session_id = i_session_id;
  commit;
exception
  when others then
    raise_application_error(-20000,'Permission denied');
end;

Next, add two application items to the Apex application to hold the user and session id. E.g. AI_USERID and AI_SESSIONID.

Now modify the when-button-pressed trigger:

declare
  v_sessionid    varchar2(32);
begin
  if :id is not null then
    v_sessionid :=  dbms_random.string('x', 32);
    insert into user_sessions (user_id, session_id)
    values(user,v_sessionid);
    commit;
    host('cmd /c start http://server:port/apex/'
        ||'f?p=111:100:::::P100_ID,AI_USERID,AI_SESSIONID:'
        ||:id||','||user||','||v_sessionid);
  end if;
end;

The last step is to modify the process:

check_session(v('AI_USERID'), v('AI_SESSIONID'));
showReport(nv('P100_ID'));

Add #SQLERRM_TEXT# to the Process Error Message.

Actually I do not really need the user id in this example, but you could use this parameter for other purposes, like logging who visited the page.

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.