Calling Oracle Reports from Oracle 10g Forms
Introduction
Oracle Reports can add some great reporting functionality to your 10g Forms applications making data available in a wide variety of output formats such as XML and PDF. In this document I will explain how to configure 10g Oracle Reports on your PC, how to call Oracle Reports from 10g Forms applications, and application server deployment considerations. This information should advance your usage of Oracle Reports and speed adaptation in your applications.
Configuring the OC4J Runtime Environment for Oracle Reports
Assuming that Oracle Reports 10g is installed on your PC, the following steps will ensure that you are able to successfully use Oracle Reports in your OC4J runtime environment.
Defining the Report Server
The first step is to define a report server. This server will run in your OC4J runtime environment handling all Oracle Report execution requests. You will not be able to run reports in a 10g application unless you have defined and started a Report server. To define a report server do the following:
1. Start your OC4J runtime environment
2. Open a Command window (Start Menu, Run option, type in CMD)
3. Type this command and press enter: rwserver server=rep_fsprod
a. It is important to name the Reports server rep_fsprod. This is the name of the Report server on all of the Application server machines and will greatly simply your deployment if you keep the names synchronized.
Congratulations you just defined a report server! You should now see a dialog box indicating the Report Server is initializing.
Configuring the Report Server
The second step is to configure the Report Server created in step 1. Specifically you need to specify the Source Directory of the report files as a search path for the Report Server to use when individual report requests are made. Here is what you need to do:
1. Open Windows Explorer and navigate to c:\
2. Open rep_fsprod.conf using a text editor.
3. Using the Find or Search tool (depending on the editor used), look for sourceDir
a. You do not need to do a case sensitive search.
b. This variable is typically located near the top of the file. In mine, it is the 14th line of the file.
4. Edit the sourceDir variable to include the path of each directory where you wish the Report server to search for reports. Below is an example with two directory paths specified. One is required, but you can have as many as you need.
(property name="sourceDir" value="c:\env\aap\reports;c:\env\eu_asc\reports;)
5. Save and close the file.
Modifying the Application .env file
The third and final step is modifying each application .env file including a variable specifying the reports path. Here is what you need to do:
1. Open Windows Explorer and navigate to c:\
2. Open one of the application.env files that you have created using a text editor.
3. Add the REPORTS_PATH variable, setting its' value to the location of that applications report directory as shown in the example below:
REPORTS_PATH=c:\env\aap\reports
4. Save and close the file.
5. Repeat for each remaining application.env file.
Calling Reports from 10g Forms
Now that the OC4J environment is configured, we now can focus on programming a solution to call Oracle Reports from Oracle Forms. I'll walk you through the process using a reporting solution that I have used in 5 applications deployed at the Forest Service.
In the applications I have developed, end users typically call reports by selecting a report from a drop list.
A report specific form is display which allows report parameters to be specified.
When the end user has entered the desired parameter values a button is pressed initiating the report.
The WHEN-BUTTON-PRESSED trigger calls a program unit to gather the report parameters and
build the string that is eventually executed by the WEB.SHOW_DOCUMENT command below:
DECLARE
v_host varchar2(100);
v_port varchar2(10);
v_parameter_string varchar2(4000);
v_username varchar2(50);
v_password varchar2(50);
v_database varchar2(50);
BEGIN
-- Get the full domain name of the server from the formsweb.cfg
tool_env.getvar('SERVER_URL',v_host);
-- Get the username/password and database from the .env file
tool_env.getvar('APPLICATION_USERNAME',v_username);
tool_env.getvar('APPLICATION_PASSWORD',v_password);
tool_env.getvar('APPLICATION_DATABASE',v_database);
-- If it is localhost then we are running on windows and need to use the windows port, otherwise we
-- are on AIX and need to use the AIX port
IF v_host = 'localhost' THEN
v_port := '8889';
ELSE
v_port := '7778';
END IF;
-- Now build the parameter string for running the report
v_parameter_string := 'server=rep_fsprod';
v_parameter_string := v_parameter_string||'&report=AAP_TOTAL_CONTRACT_VALUE_RPT.rdf';
v_parameter_string := v_parameter_string||'&userid='||v_username||'/'||v_password||'@'||v_database;
v_parameter_string := v_parameter_string||'&execution_mode=batch';
v_parameter_string := v_parameter_string||'&comm_mode=synchronous';
v_parameter_string := v_parameter_string||'&destype=cache';
v_parameter_string := v_parameter_string||'&desformat=pdf';
v_parameter_string := v_parameter_string||'&p_region='||:rpt_parms.region;
v_parameter_string := v_parameter_string||'&p_province='||:rpt_parms.province;
v_parameter_string := v_parameter_string||'&p_forest='||:rpt_parms.forest;
v_parameter_string := v_parameter_string||'&p_district='||:rpt_parms.district;
v_parameter_string := v_parameter_string||'&p_fiscal_year='||:rpt_parms.fiscal_year;
-- Now run the report
web.show_document('http://'||v_host||':'||v_port||'/reports/rwservlet?'||v_parameter_string,'_blank');
END;
There are a couple of important items to note when reviewing this program unit:
1. Important data elements are not hard coded! Instead, they are stored in the application.env file and referenced at runtime using tool_env.get_var. The username, password, database and server are all referenced this way.
2. The program unit supports execution on Windows (my development platform) and AIX (the deployment platform) without requiring any code modifications.
a. By referencing the SERVER_URL environment variable in the application.env file, I am able to specify the server and port dynamically.
3. When building the string of parameters to be passed you need to:
a. Include the report name as a parameter
b. Prepend an '&' to second parameter and any additional parameters there after.
c. Except for the report name, be sure that all additional parameters specified match a parameter name (system or user) in the Report definition.
4. Mandatory parameters for each report include:
a. Server, report, userid, execution_mode, comm_mode, destype and desformat.
i. Report is the only parameter that will change. The others (likely) will remain constant for all reports in your application.
When the WEB.SHOW_DOCUMENT command is issued from the Form, a new browser window is opened and the request is executed by the Report Server.
Application Server Deployment
When you have completed development in OC4J and are ready to deploy to the Application Server there is a short list of activities that need to be completed to insure success.
First, it is important to note that the Oracle reports in your application need to be deployed in the /reports subdirectory such as /fsapps/fsprod/leimars/reports.
Be sure to have your application.env file modified accordingly by having the REPORTS_PATH variable specify the proper location.
The second and most important item to note is that the rep_fsprod.conf file will not be modified to include each applications /reports directory. Instead the reports in each applications /reports directory are linked to /fsapps/fsprod/fs_reports by an eDBA using a standard utility that has been distributed on each application server. Each time the script is run on a particular /reports directory, symbolic links are created for each report. If you add a new report, you need to have the links created for your application before it will be available.
The third and final thing to note is that Report Definitions do not need to be compiled when you deploy to the application server. Simply move the .rdf file to you application /reports directory, have the links recreated and you are good to go.
Conclusion
You now have the information you need to include Oracle Reports in your 10g Forms applications.
It is a fairly painless process to complete if your application calls for this functionality.
I hope that you found the contents of this paper useful and wish you good luck.
Calling Oracle Reports from Oracle 10g Forms
