Oracle Portal and Oracle BI Publisher

If you are using Oracle Portal and are used to using the Portal Forms Builder, you may want to create parameter forms for your BI reports in the Portal Forms Builder. This article provides a methodology for generating and redirecting to the BI Publisher URL from a Portal Form.

Step 1: Create a package that will do the redirect for you given the appropriate parameters.

--
--
create or replace
package bip1 as
g_url varchar2(32767);

-- add additional parameters as required
procedure bip_r
(
p_xf IN VARCHAR2 DEFAULT NULL,
p_custom1name IN VARCHAR2 DEFAULT NULL,
p_custom1val IN VARCHAR2 DEFAULT NULL,
p_custom2name IN VARCHAR2 DEFAULT NULL,
p_custom2val IN VARCHAR2 DEFAULT NULL);
end;
/

--
create or replace
package body bip1 as

procedure bip_r
(
p_xf IN VARCHAR2 DEFAULT NULL,
p_custom1name IN VARCHAR2 DEFAULT NULL,
p_custom1val IN VARCHAR2 DEFAULT NULL,
p_custom2name IN VARCHAR2 DEFAULT NULL,
p_custom2val IN VARCHAR2 DEFAULT NULL)
as
-- for information on determining the URL below, see
-- http://blogs.oracle.com/xmlpublisher/2006/07/accessing_xmlp_enterprise_repo.html
l_url varchar2(32767) := 'http://[Your_BIP_Server]:9704/xmlpserver/[your_folder]/[your_report]/[your_report].xdo?_xpf=&_xpt=1&...';
begin
l_url := l_url || '&_xf=' || p_xf || '&'
|| p_custom1name || '=' || p_custom1val || '&'
|| p_custom2name || '=' || p_custom2val ;

bip1.g_url := l_url;
end;
end;
/

Step 2: Create a Portal Form against a procedure and choose the bip1.bip_r procedure.

Step 3: Modify the Portal Form

a. p_xf is the allowable output formats: html, pdf, csv, etc.
b. p_custom1name (and all p_custom#name items) should be hidden and should be the name of your BI Publisher report variables
c. p_custom1val (and all p_custom#val items) are the values you want your users to enter, associated with the corresponding p_custom#name
d. At the Portal Form level, where you see the text area following

On successful submission of a form, execute this PL/SQL block or PL/SQL procedure:
Hint:
You can redirect your browser to a PL/SQL procedure, for example a procedure that creates a Web page, using either of these methods:

1. call('', '');
Redirects the browser to the procedure and passes a parameter containing the URL back to the form.
2. go('');
Redirects the browser to the procedure but does not pass a URL to return to the form.

Enter: go(bip1.g_url);

Running this form will redirect you to the correct BI Publisher URL.