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
    );