9.3 OracleAS Reports Services

Oracle Reports, like Oracle Forms, is a part of the Oracle Developer Suite and has been a part of the Oracle technology stack for many years . We discuss the use of Oracle Reports in the context of business intelligence solutions in Chapter 12, but an in-depth discussion of the use and capabilities of Oracle Reports is beyond the scope of this chapter.

In brief, Oracle Reports enables a developer to combine data from many sources (including an Oracle database, XML data, JDBC data feeds, and even customizable data sources) into a report that can be distributed either on paper or within the web environment. Reports can be generated as HTML, XML, PDF files, RTF format, and many other formats as well.

This section concentrates on Oracle Application Server Reports Services, the component of Oracle Application Server that is responsible for providing a middle tier for deploying Oracle Reports. OracleAS Reports Services is responsible for creating Oracle Reports engines to generate reports, for distributing reports across multiple servers, and for caching reports for rapid reuse.

9.3.1 How It Works

OracleAS Reports Services can be used either inside or outside a web environment. The architecture of OracleAS Reports Services within a web environment is shown in simplified form in Figure 9-2.

Figure 9-2. Simplified OracleAS Reports Services architecture

Requests come into the Oracle HTTP Server, and are then passed to an OracleAS Reports servlet through the mod_oc4j module. The OracleAS Reports servlet is responsible for coordinating requests with the OracleAS Reports Server. The server handles the processing, scheduling, and distribution of reports. It also maintains the state of all current report requests.

The OracleAS Reports Server is monitored and controlled by the Oracle Process Management and Notification Server (described in Chapter 2), although you can start and stop the OracleAS Reports Server in other ways ”for example, with a command-line prompt or from a URL.

The actual creation of reports is handled by Reports engines, which are written in C. There can be many Reports engines for each OracleAS Reports Server. You can configure the maximum, minimum, and initial number of engines for a OracleAS Reports Server, as well as the amount of time a Reports engine can be idle before it is automatically shut down by OPMN.

Several additional components are included in the OracleAS Reports Services architecture but aren't displayed in the simplified version shown in the figure:

OracleAS Reports Cache: Used by the OracleAS Reports Server for caching reports for subsequent use, based on rules established for the report.
Custom Tag Handler: A part of the Reports servlet, used to call Oracle Reports from JSPs with special tags. You can use these tags to gain access to data models developed in reports.
OracleAS Single Sign-On: Because OracleAS Single Sign-On is a part of Oracle Application Server, you can use this security architecture with OracleAS Reports Services. If you choose to use OracleAS Single Sign-On, the Oracle Reports servlet interacts with the OracleAS Single Sign-On service to validate user credentials. If a user doesn't exist in the Oracle Internet Directory, the Oracle Delegated Administration Services, which allow a user to maintain his login credentials, prompts the user for connection information, which is stored in the Oracle Internet Directory. You can also use OracleAS Reports Services with its own security authentication, although OracleAS Reports Services is installed using OracleAS Single Sign-On by default.

Security can be implemented at three levels in OracleAS Reports Services ”on the application that calls a report, on the report and its destination resources, or on the underlying data sources for the report.

Pluggable engines: These are custom engines that can hook into the OracleAS Reports Server to use its capabilities, such as scheduling, distribution, and content. OracleAS Reports Services comes with a URL engine, which lets you distribute content from any publicly available URL using these features of the OracleAS Reports Server.
Reports CGI: Used to call reports dynamically from a browser using CGI, and included for backward compatibility.

You can invoke OracleAS Reports Services from outside a web environment. You can use the command-line argument rwclient to call the OracleAS Reports Server directly. You can also call the OracleAS Reports Server from the SRW built-in PL/SQL package or can expose OracleAS Reports Services as a Web Service.

9.3.2 Handling Requests

A request that comes into an OracleAS Reports Server can include parameters that shape the report. If the request is made from a browser, the parameters can be included in the calling URL, or that URL can use a key to identify a group of parameters. The parameter groups and values are stored by key name in the cgicmd.dat file.

When a request comes in to an OracleAS Reports Server, the server first determines if the request can be satisfied either by a currently running job or by a report in the OracleAS Report Cache, described in the next section. If a request can't be satisfied by one of these two methods , the request is placed in one of two queues:

C urrent job queue: For immediate requests
Scheduled job queue: For scheduled requests

OracleAS Reports Services also has two more queues:

Completed jobs queue: For job history; you can configure a limit to the number of jobs kept in the completed jobs queue
Failed jobs queue: For failed jobs

OracleAS Reports Server keeps requests that come from a browser in memory. In the event of a server crash, these requests will have to be resubmitted. Requests that are slated for a print queue, a file, or OracleAS Portal will be restarted in the event of an OracleAS Reports Server failure.

You can view the contents of either the current job queue or the scheduled job queue through the Application Server Control tool in Oracle Enterprise Manager. Both of these queues are persistent; if an OracleAS Reports Server crashes, only the currently running jobs are lost.

If a request requires authentication, OracleAS Reports Server calls the mod_osso module before placing the requests in either queue.

You configure the maximum number of Reports engines for an OracleAS Reports Server. If a request is made, and the maximum number of Reports engines aren't currently running, OracleAS Reports Server creates a new engine to run the request at the top of the current job queue. If the maximum number of engines are already running, the top job in the current job queue waits, and the first available engine takes the job.

Internal testing from Oracle has determined that the optimal number of Reports engines is two times the number of CPUs on a server.

With Oracle Application Server 10 g , several different Reports engines can exist with different environment conditions, such as the ability to handle reports with different languages. When a report request arrives with a particular configuration, OracleAS Reports Server directs it to an available Reports engine that is appropriate for the request.

Once a Reports engine has completed the creation of the report, the report is placed in the OracleAS Reports Cache, and the OracleAS Reports Server is notified of its availability. The OracleAS Reports Server then distributes the report.

The steps described here are valid for running reports with a paper layout, whether they are standard reports or JSP reports. If you want to display a report with a paper layout in a browser, the report can be sent as an Adobe Acrobat (PDF) file format. If you want to run a report with a web layout, you can save the report definition as a JSP in Oracle Forms Developer and then execute the report like a standard JSP.

9.3.3 OracleAS Reports Cache

A report is a static view of constantly changing data. There may be times when the same report can fulfill more than one request ”either by design or by the coincidences of usage patterns. Consequently, OracleAS Reports Services tries to optimize the use of reports and diminish the resource requirements for serving reports to a user community.

As mentioned earlier, the destination for reports created by a Reports engine is the OracleAS Reports Cache. When a report is put into that cache, the report is assigned a cache key. The cache key includes most of the parameters that shape the report, with the exception of destination information, server information, and the TOLERANCE parameter (described later in this section). You can configure an OracleAS Reports Server instance to ignore specified parameters in creating the cache key.

When a request comes in, the OracleAS Reports Server looks in the OracleAS Reports Cache for a report with the same cache key. A report in the cache with an identical cache key can sometimes be reused for another request.

The request itself can include the TOLERANCE parameter. This parameter has a value in minutes, hours, or day, or alternately contains a specific time. If a report has a TOLERANCE parameter, the OracleAS Reports Server searches the completed jobs queue for a report with an identical cache key that isn't older than allowed by the time value specified by the TOLERANCE parameter or earlier than the time specified by that parameter.

The size of the OracleAS Reports Cache is configurable, either by the total size of the cache or by the number of documents the cache is allowed to hold. As the cache is filled, the least-used reports are swapped out of the cache.

9.3.4 Distributing Reports

Once a report is created, it can be distributed to a variety of destinations ”for example, to the browser issuing a request, to one or more recipients via email, to a file or a printer, or to OracleAS Portal. You can also create your own custom destinations using the Oracle Application Server Reports Services API. That API supports the distribution of reports to other destinations, such as via FTP or a fax machine.

With Oracle Application Server 10 g , the creation of reports is done by the Reports engine, independent of the eventual destination of the report. The OracleAS Reports Server is responsible for distributing the report to its final destinations.

You can create complex types of distributions for reports. Each report has three distinct sections ”a header, a trailer, and a body. You can distribute each section separately, and you can also create individual reports based on groups specified in the body of the report. You can send each report or report section to one or more destinations. You can also create destinations based on one or more data values in a variable.

For example, if you have a report on employees by department, you can have a department summary section sent to the appropriate head of the department, as well as individual employee reports, sent to each employee. The department heads and employees can be indicated by data variables within the report. This scenario, sometimes referred to as bursting reports, allows a single report pass to generate a wide spectrum of reports.

An XML file is used to describe these complex distributions. You specify the distribution XML file as part of the report request. For more information on these complex distributions, please refer to the Oracle Application Server Reports Services documentation.

9.3.5 XML and Reports Services

The distribution file mentioned in the previous section is an XML file. XML (described in greater detail in Chapter 10), which can also be used as a format for reports or to modify reports with customization files.

Although you can build a report completely from scratch using XML, Oracle recommends following an alternate process whereby you save a report as XML. You can apply one or more XML customization files to the saved report; these files are used to modify characteristics in the report.

For example, you might want to format a particular field using its XML attributes in a customization file. Once you have perfected the XML customization file, you can use it to apply the same formatting to other reports that contain the same field, either by running each report or by applying the modifications as part of a batch process.

9.3.6 Clustering

You can cluster together any number of OracleAS Reports Servers. Each member of the cluster is aware of the other members of the cluster, and all members of the cluster work together to handle incoming requests.

When a request comes in to one OracleAS Reports Server in a cluster, either from a client or off the scheduled job queue, the request will go through the standard set of steps for determining how to handle the request, but will supplement each step with an examination of other members of the cluster. For example, the first step when receiving a request is to see if there is information in the OracleAS Reports Cache to handle the request. With a cluster, this step is followed by a determination of whether there is information in the OracleAS Reports Caches of any other members of the cluster to handle the request. The same process is followed when looking for currently running jobs, for an appropriate Reports engine that is available, and for checking the number of Reports engines running against the maximum number of engines configured for the OracleAS Reports Server.

Each member of a cluster maintains its own local OracleAS Reports Cache, so the failure of any member eliminates the contents of its cache and prevents access by the other members of the cluster to this information. Such failure also prevents access by the OracleAS Reports Server and its engines.

Clusters are formed by appending a cluster name onto the name of the OracleAS Reports Server. You can submit a request to the cluster, rather than to a server in the cluster, by specifying the cluster name as the server.

9.3.7 Calling OracleAS Reports Services

You can access OracleAS Reports Services directly from a browser or from the command line. But you may want to generate or schedule reports based on other interactions and interfaces. OracleAS Reports Services comes with an API that can make a request to the services based on a database event, such as a changed value in the database. Two other interfaces also are frequently used to access reports from OracleAS Reports Services ”through OracleAS Portal and as a web service. We describe both of these interfaces in subsequent sections.

9.3.7.1 Event-based reporting

In general, reports are the way that information in a database is disseminated to groups of users. Oracle Application Server Reports Services brings many helpful capabilities to this task ”for example, easily readable presentation, multiple copies, and ease of access. Historically, however, there has been an inevitable problem with reports and the data they represent. A report gives an accurate view of data ”at the time the report was generated. This data can change at any point in time after this generation. OracleAS Reports Services has a way to overcome this limitation and make reports more timely .

The OracleAS Reports Services API is a PL/SQL package that can be called from any other PL/SQL procedure. The package lets you submit a request to a OracleAS Reports Server and pass up to 255 parameter values with the request. Submitting a job returns a job identification, which can check the status of the job or cancel the job.

9.3.7.2 OracleAS Portal

OracleAS Portal is another component of Oracle Application Server, discussed in detail in Chapter 13. You can call reports from OracleAS Reports Services to be displayed within the OracleAS Portal framework, either within its own portlet or as an item link on another page.

The interaction between OracleAS Portal and OracleAS Reports Services requires a few additional considerations. If you are calling a report from the Portal environment:

You can limit access to the report through security restrictions on the calling OracleAS Portal page. Because OracleAS Portal uses OracleAS Single Sign-on, a user identity flows to OracleAS Reports Services, which imposes its own set of report and data access security restrictions.
You must register all OracleAS Reports Servers and reports in your OracleAS Portal environment
You can use availability calendars in OracleAS Portal to restrict the days and times that reports can be requested from OracleAS Reports Services.

Although you can publish a report to OracleAS Portal, you can't use OracleAS Portal as a destination when you are using an XML distribution file.

9.3.7.3 Web Services

Web Services are a way to access some kind of functionality or information over the Web, using XML to describe the information requested.

If you want to run OracleAS Reports Services as a Web Service, you run the rwwebservices OC4J servlet. This application provides endpoints for accessing an OracleAS Reports Server as a Web Service. Other applications can use this servlet to get the version of the API used to access the OracleAS Reports Server, get information about the server or any particular job, and submit and kill jobs for the OracleAS Reports Server.