In a previous article, I discussed a deployment framework for
getting Forms on the Web. This article discusses the steps to get
your Reports on the Web. I'll cover the steps to use
Reports-related built-in's in your forms, how to "tell" your forms
the name of your report server, how to toss in some JavaScript so
an "Exit" button will exit a form and close the browser window,
and how to use and configure the in-process report server. As in
the Forms on the Web article, I will not discuss Reports
development.
Establishing a baseline example
Because there are so many variations of how to configure
various components, I'm going to use the following assumptions for
a Forms and Reports development-to-deployment environment.
- Your 6i forms have been successfully migrated to a 9i/10g
state ("10g" will refer to both versions)
- You are using Application Server 10g Forms and Reports
Services (the steps here work for the full scale version as
well) and everything will be on one server
- When Reports were used in your 6i client-server environment,
the reports were called via a "Run Report" button on a form or
via a report filter type of form (it also uses a "Run Report"
button after the user inputs parameter type of information)
- You want Reports on the Web to appear in a new browser
window, and that browser window has its "normal" buttons removed
(when the user is finished viewing, saving, or printing the
report, the user closes the browser window and returns to the
report-calling browser window)
- If a user wants to save a report, he has to click on the
Save button (reports appear as PDF documents), and reports
cached or saved on the Application Server machine are not
intended to be directly accessed by users
The above sounds like a lot of restrictions or assumptions, but
it really boils down to a fairly simple - and easy to
install/configure - environment. If your primary task is to get
things working as soon as possible, then you just bought into a
happy meal. Later on, when you have time to sit back and figure
out how you are going to spend the raise you got for turning your
Oracle-based application into a Web product, you can experiment
with ordering the big chicken dinner (SSO, a separate report
server machine, using the "other" report server, and so on).
The code to make a report run on the web
Let's clarify the difference between parameters and data.
Parameters tell the report how it is supposed to run or display.
Data is what the report shows. How you provide the data to make a
report run is up to you. How you provide the parameters falls into
two categories: those required by Oracle, and those you want or
need. Further, several items that you used to pass in as
parameters under 6i are now required to be set using a built-in
with 10g. The best way to describe the code is to show an example.
Some of what is shown below comes from an example Oracle has in a
note on MetaLink. The code below is what you can put behind a "Run
Report" button. I formatted some comments with a different font to
help point out what is taking place. There's nothing worse than
reading dry code, so that's another reason for all of the
comments.
PROCEDURE RUN_REPORT IS
-- Some variable you use to populate a report table named REPORT,
-- or call some procedure to generate data for a report
v_report_uui REPORT.report_uui%TYPE;
-- Parameter List used in call to Oracle Report "rpt_name"
p_rpt_param_list paramlist;
report_id REPORT_OBJECT;
ReportServerJob VARCHAR2(100);
v_jobID VARCHAR2(100);
rep_status VARCHAR2(20);
reportserver VARCHAR2(30);
v_url VARCHAR2(200);
v_url2 VARCHAR2(200);
-- However you keep track of reports; this is just one way
usr_sessionid NUMBER;
report_not_generated EXCEPTION;
BEGIN
--Get the user session ID
SELECT USERENV('sessionid') into usr_sessionid FROM DUAL;
-- Execute database stored package.procedure "some_package.some_procedure"
-- that populates a REPORT table
some_package.some_procedure
(
to_char(:block.some_item), -- IN
v_report_uui -- OUT
);
-- Destroy the parameter list if it already exists
IF NOT Id_Null(p_rpt_param_list) then
Destroy_Parameter_List(p_rpt_param_list);
END IF;
-- Create a Parameter List
p_rpt_param_list := Create_Parameter_List ('tmp');
-- Generate report - on the Web
-- You can add an ELSE clause to this to re-use your 6i code
IF (get_application_property(user_interface)='WEB') THEN
-- You need to provide the name of the report server
-- The "rpt_name" is a report object you create in the Form Builder Object
-- Navigator
reportserver := :parameter.reportserver;
report_id := find_report_object('rpt_name');
-- These five calls to the built-in cover what 10g requires
SET_REPORT_OBJECT_PROPERTY(report_id, REPORT_COMM_MODE, SYNCHRONOUS);
SET_REPORT_OBJECT_PROPERTY(report_id, REPORT_EXECUTION_MODE, BATCH);
SET_REPORT_OBJECT_PROPERTY(report_id, REPORT_DESTYPE, FILE);
SET_REPORT_OBJECT_PROPERTY(report_id, REPORT_DESFORMAT, 'pdf');
SET_REPORT_OBJECT_PROPERTY(report_id, REPORT_SERVER, reportserver);
-- Here is where you can add your own parameters to the report
Add_Parameter(p_rpt_param_list,'paramform',TEXT_PARAMETER,'no');
Add_Parameter(p_rpt_param_list,'p_report_uui',TEXT_PARAMETER, to_char(v_report_uui));
Add_Parameter(p_rpt_param_list,'PRINTJOB',TEXT_PARAMETER,'NO');
-- A common error message has to do with not being able to find the report object.
-- No report object means no report_id, and you'll get lots of errors.
ReportServerJob := run_report_object(report_id, p_rpt_param_list);
v_jobID := substr(ReportServerJob,length(reportserver)+2,length(ReportServerJob));
IF ReportServerJob is NOT NULL THEN
rep_status := report_object_status(ReportServerJob);
WHILE rep_status in ('RUNNING', 'OPENING_REPORT','ENQUEUED') LOOP
rep_status := report_object_status(ReportServerJob);
END LOOP;
IF rep_status != 'FINISHED' THEN
raise report_not_generated;
END IF;
-- The JavaScript command/string can be used to show a basic browser window
v_url := '/reports/rwservlet/getjobid'||v_jobID||''server='||reportserver;
-- The following is one long string, carriage returns used for formatting
v_url2 := 'javascript:window.open("'||v_url ||'", "", "fullscreen=no,
titlebar=no, location=no, toolbar=no, menubar=no, status=no, resizable=yes");
-- "self.close" shown below is continued from the line above, carriage return
-- here for formatting, but it is really one long string
self.close()';
-- This is the built-in that calls a new browser window
Web.Show_Document(v_url2,'_blank');
ELSE
raise report_not_generated;
END IF;
END IF;
-- Destroy the parameter list. Report has been generated - it's no longer needed
Destroy_Parameter_List(p_rpt_param_list);
EXCEPTION
WHEN report_not_generated THEN
-- "am" is a shortcut to call an alert message. If you are raising a lot of alert messages in
-- your forms, add a procedure in Program Units. It takes the string you pass in and will
-- save you lots of time by not having to keep writing set_alert_property(...).
am('There was an error in running the report.'||chr(10)||
'Contact the Application Server administrator for assistance.');
WHEN OTHERS THEN
user_show_error;
END;
The code above looks like a lot, but the good news is that the
procedure is virtually 100% reusable. If you standardize your
Forms and Reports development environment, all that needs to be
changed here is the name of the report, and even then, you can
pass that in to the procedure via the code behind
when-button-clicked for the "Run Report" button. Your calling Form
needs to have a report object added to it (that's covered in
Oracle's documentation) in addition to having a "reportserver"
parameter (the name of the report server).
Naming the report server and passing it to a
form
This part of the setup is very simple, but I
have seen it stump a lot of people. One way to pass the name of
the report server is to hard code it in the parent menu-type form
I discussed in the previous article. Hard coding is generally bad,
but at least it's contained in one place (the calling form).
Another way is to pass in the report server name via the URL you
use to call your menu form in the first place. You can use the "otherparams"
line in the formsweb.cfg file. Yes, you are hard coding the name
of the report server, but at least now you're using a
configuration file - but that's what they're for, so go ahead and
use it.
Tip: I have seen Forms fail to pass global
variables. If you are using a global to pass the report server
name into the report-calling form, and the global fails to pass
in, you'll see errors that don't look anything at all related to
not having a report server name.
Which report server to use?
Let's assume you just installed Application
Server, and your server's host name is prod. If you navigate to
the ORACLE_HOME/reports/conf directory, you will see a file named
rep_prod.conf. Copy that file and name it prod.conf. Technically,
there is no need to copy the file, as Application Server will
create it for you the first time you use the in-process report
server, but you are going to do it ahead of time so it can be
configured prior to first use.
Open the prod.conf file and what do you see?
Eeewww! It's an XML file. It's like Indiana Jones in "Raiders of
the Lost Ark," when Indiana opens the cover of the tomb and sees
all the snakes ("Why snakes? Why did it have to be snakes?"). If
that's how you feel about XML, the good news is you only have to
edit one line (around line 15, use sourceDir to tell Application
Server where your reports are located). The features and options
you can configure via this file are too numerous to be discussed
here, but the sourceDir property is required.
Edit the reports.sh (UNIX) file in the
ORACLE_HOME/bin directory. Around line 62, add your reports
location to the REPORTS_PATH variable.
To start the in-process report server on UNIX,
for example, issue the following command at the command prompt (as
the owner of the 10g installation):
ias@prod: rwserver.sh server=prod batch=yes &
This starts another process that should be
persistent. The report server itself, however, will idle out after
15 minutes of non-use (see idleTimeOut at line 48 if you want to
change the default value to something else). If the report server
has idled out (gone to sleep, so to speak), the report "waking up"
the report server will take a bit longer than normal to appear.
If you use the Enterprise Manager console, you
will see references to the rep_prod (rep_name-of-your-server)
report server, which is the stand-alone report server. You can
ignore it because you're using the in-process report server.
At this point, you should be able to call
reports. You can test this via the Application Server "home" page
at http://server_name:port_number. Click on Demonstrations and
follow the links to reports.
Closing your Forms main menu page
Oracle left out a feature for Web forms, one
that will close the browser window for you. Currently, if you just
exit the main form, you're left with a dull-colored applet window.
Thanks, what do we do with that? Add an "Exit" button to the main
form, and add this to a post-form trigger:
web.show_document('close.html','_self');
In the ORACLE_HOME/Apache/Apache/htdocs
directory on the Application Server machine, create a file called
close.html and add this to the file:
<html>
<body onload="closeit()">
<script>
function closeit() {
win = top;
win.opener = top;
win.close (); }
</script>
</body>
</html>
Clicking on "Exit" will send the browser to the
close.html file and the JavaScript "closeit" function will close
the browser window.
In closing
That was a lot of material to cover, but it
works. The steps shown in these two articles cut through literally
thousands of pages of Oracle documentation and boil down the
"making Forms and Reports run on the Web" process something you
can actually read and understand. Not only does it work, it's
simple and easy to follow. If you can get your forms and reports
working on the Web, you can turn any client-server Forms and
Reports-based application into a Web product.
