Type Your Title Here - Oracle



Integrating Forms, Reports and Discoverer into Portal

Chris Ostrowski, TUSC

Introduction

This paper and presentation will discuss the integration of existing Oracle Forms, Oracle Reports and Oracle Discoverer Worksheets into web pages that are served up by the Oracle Portal engine in Oracle9iAS Release 2. We will discuss the various methods for incorporating these components into Portal, the pieces that need to be in place before attempting to integrate these components (like creation and administration of Reports Servers and Discoverer connections) and the various methods of security we can apply to these components.

What is 9iAS?

Oracle9iAS Release 2 (hereafter referred to as 9iAS) is an integrated suite of products that allow developers to create web-based applications or move existing applications to the web with minimal effort. At the core of 9iAS is the Apache web server. Oracle has added numerous features on top of the Apache Web server such as:

• Forms server - which allows existing Oracle Forms to be deployed over the Web

• Reports Server - which allows Oracle Reports to be deployed over the web

• Discoverer server - which allows Discoverer worksheets and workbooks to be created and deployed over the web

• Web Cache - which increases performance by caching frequently accessed web pages (even dynamic pages)

• Single Sign-on - which gives developers and Portal administrators the ability to integrate numerous applications without requiring the user to maintain numerous logins

• OC4J – Oracle Containers For Java, which allows developers to deploy Java-based applications easily

• Wireless – which allows 9iAS-based web ages to be delivered easily to mobile devices

and, of course, Portal. This paper and presentation will focus on moving existing applications to the web via Portal.

A quick note on terminology: Portal has the ability to create forms and reports. These are not the same as forms and reports created with the Oracle Forms and Oracle Reports products that are part of the 9iDS development suite. Any reference to an Oracle Form or an Oracle Report in this paper refers to a form or report generated in the Oracle Forms and Oracle Reports products. This paper focuses on integrating the .fmx and .rdf files generated from those products, along with Discoverer Worksheets, into Portal.

What is Portal?

Oracle Portal version 9.0.2. has evolved into a robust development environment. It facilitates the rapid development of web-based components (forms, reports, graphs, etc.) through the use of wizards, which generate PL/SQL packages that are stored in the database. Much of the effort spent maintaining a consistent look and feel between pages and developing security for an application is handled seamlessly in Portal, allowing developers and content managers to focus on the features of the application. Since the development environment is itself a Portal application, developers can see the results of their efforts immediately.

Why do we want to integrate?

The benefits of developing in Portal are numerous: the elimination of fat-client based applications and maintenance, the simple implementation of security and a consistent visual interface via the use of user interface templates, just to name a few. There are, however, significant reasons for not developing in Portal. First, and foremost, a great deal of time and effort may have been spent using Oracle's other development tools (Forms, Reports, Discoverer) to build existing applications. Secondly, Portal requires a new skill set and a new way of thinking – it may not be feasible to train all of your development staff. Finally, the forms and reports components in Portal do not have the full feature set of the Oracle Forms and Oracle Reports products.

Native support

Portal has native support for two of the three components mentioned in this paper: Oracle Reports and Discoverer Worksheets. Since these components are supported natively in Portal, there are certain Portal-specific features associated with them. For example, we have the ability to set up a calendar in Portal that determines when a particular Oracle Report can be run or when a particular printer can be accessed by that report.

Just because Portal has native support for Reports, it doesn’t mean we have to use those features. If we choose not to, we lose out on using any Portal-specific functionality. To integrate Discoverer Worksheets, however, we will have to use Portal-specific methods. We can also integrate Oracle Forms into Portal, but since there is no native support, there are no Portal-specific features associated with the integration of Oracle Forms.

Component #1: Oracle Reports

During the installation of 9iAS, a component called a Reports Server was automatically created for you, if you chose the Business Intelligence and Forms option. The Reports Server component allows you to take an existing Oracle Report (either an .rep or .rdf file) and view it over the web via a web browser. To view the report in Portal, at least one Report Server must be up and running.

As we’ll see, we are not limited to just one Reports Server. We can have numerous servers running to handle the load of reports being generated, or Reports Servers with specific functions, security and priorities.

Upon installation on 9iAS, a Reports Server called rep_ was created. Most of the administration duties of 9iAS can be handled graphically through the Enterprise Manager Web Site. To view the status of this Reports Server, enter the Enterprise Manager Web site for your installation. Point your browser to:



where the hostname is the name of the machine where the middle-tier (also referred to as the Application Server) is installed.

The login user for the Enterprise Manager web site is ias_admin. The password is set during installation of 9iAS. If the Infrastructure and Middle-Tier are installed on the same machine, you will see a “Farm” page similar to Figure 1. If the Infrastructure and Middle-Tiers are on different servers, you will see a screen similar to the one in Figure 2:

[pic]

Figure 1

In this example, I have two instances of 9iAS installed on my laptop. One is called IS_HOME (for InfraStructure home) and one called AS_HOME (for Application Server home). The names of these 9iAS instances are arbitrary and are set during installation of 9iAS. The Forms, Reports and Discoverer servers are deployed in the Application Server, so click the Application Server link (AS_Home.oski-mobile. in the above example). You will be taken to a screen similar to the one in figure 2.

[pic]

Figure 2

This screen lists all of the components that make up the Middle-tier (Application Server) for my installation. In the above example, there is a component called “Reports:rep_oski-mobile”. This is the Reports Server that was installed automatically during the 9iAS installation on my laptop.

If you, or the administrator at your site, have not read Metalink Document 212551.1, you will see a status of down (the red arrow pointing down) for this component, even though the Reports Server may be up. The Metalink document gives instruction on how to fix this.

If you want to test to see if the Reports Server is, in fact, up, point your browser to:



If the infrastructure and middle-tier are on the same machine, the port number is probably 7778. If they are on separate machines, the port number is probably 7777. You should see a page similar to this:

[pic]

Figure 3

To test that all of the pieces are in place to actually serve up Oracle Reports in your browser, point your browser to the “Welcome” page for the application server:

http://:

[pic]

Figure 4

Click on the “Demonstrations” tab, then the “Business Intelligence and Forms” link, then the “Reports Services” link, then the “Test A Paper Report On The Web” link. You should see screen similar to Figure 5.

[pic]Figure 5

Replace the Reports Server name with the name of the Reports Server you want to test. In my example, it’s rep_oski-mobile. Replace the connect string with any valid connect string and change DesFormat to HTML 4.0. Leave the report name as test.rdf. Click “Run Report”. If you see a report in your web browser, the Reports Server is working.

The reports component in Oracle Portal is a very powerful way to develop web-based reports in a very short period of time. It does, however, have serious limitations. Since the major components of Portal (Forms, Reports, Charts) are built using wizards, a significant amount of control is handled by the code generator. In many cases, this is sufficient for your development needs. If, for whatever reason, this is not sufficient, you can integrate existing reports developed in Oracle Reports into Portal.

Reports Integration Method #1: Using the Portal URL component

The simplest way to incorporate an Oracle Report into Portal is by way of a URL. This is not the native way of supporting Oracle Reports in Portal, but it is the quickest way to get an Oracle Report on a Portal page.

If you successfully ran the report from the “Getting Started with Oracle9i Reports” screen in Figure 5, you noticed that a new window popped up with a URL similar to:

server=rep_oski-mobile&report=test.rdf&userid=system/manager@iasdb

This URL can be used to create a Portal component than can then be placed on a Portal page. Assuming you’ve already created a Database Provider in Portal, go into that provider and create a new URL component. Give it a name that is easily remembered. On the second step of the wizard, enter the URL from above. I called mine test_reports_url. When we get to Figure 7, we’ll use his URL and place it on a page:

[pic]

Figure 6

Click “Finish”, then “Run As Portlet” on the component summary page. You should see the report in your web browser. Click the “Access” tab on the component summary page and make sure “Publish To Portal” is checked.

Securing the Reports URL

If you looked closely at the URL to run our test report over the Web, you noticed that there was sensitive information in it; information we certainly don’t want exposed in a URL for the entire world to see. How can we hide this information? The Reports Server can make use of something called a keymap file. This file hides the details of the report from the user’s eyes by referencing a key in a file on the server. The keymap file for the Reports Server is called cgicmd.dat and is located in /reports/conf. Here’s an example of some of the entries in that file:

orqa: report=breakb.rdf destype=cache desformat=html server=repserver

breakbparam: report=breakb.rdf destype=cache desformat=html server=repserver userid=scott/tiger@mydb

The key name is to the left of the colon. The parameters associated with that key are to the right of the colon. Note that in the keymap file, the parameters are separated by spaces (in the URL they were separated by ampersands). Our URL from above looks like this:

server=rep_oski-mobile&report=test.rdf&userid=system/manager@iasdb

We want to hide everything after the question mark, so would could create a key like this:

test_report: destype=cache desformat=HTMLCSS server=rep_oski-mobile report=test.rdf userid=system/manager@iasdb

We could then reference the keymap file with the following URL:



To see all of the parameters that can be specified in the keymap file, look at the syntax for rwclient in Appendix A of “Oracle9iAS Reports Services Publishing Reports to the Web” (Part # A92102-01).

This serves to hide the implementation details from the end-user. The cgicmd.dat file is not read dynamically, however, so every change to it requires you to stop and start the OC4J_BI_FORMS component in the Middle-Tier. (Personal note: there is a “Restart” button on the Enterprise Manager web page for all components, but it acts inconsistently. I always formally stop the component, then start it up again).

To put this component on a page, go back to the Navigator and click on the “Page Groups” tab. If you don’t have page group defined, create one now. Edit the root page for your page group. You can edit the page one of three ways: Graphical Mode, Layout Mode or List Mode. I prefer Layout mode, since it’s closest to Portal Release 1 and that’s the way I’m used to editing Portal pages. Click “Add Portlets” in the main region of the page:

[pic]

Figure 7

then “New”, then the name of your database provider. You should see Test_Report_URL (or whatever you named your URL) as one of the portlets that can be placed on this region. Click the link and it should appear in the selected portlets window on the right-hand side of the page. Your Portal page now has an Oracle Report integrated into it. Back on the Edit Page screen, you can click the “Graphical” link in the top left of the page. You should see the report (albeit clumsily placed) on the page.

Reports Integration Method #2: Using the native Portal method

Using the URL component is a quick way to get an Oracle Report on a Portal page, but we can use Portal’s native support for Oracle Reports to enhance our functionality. If you’re logged into Portal as a user with administration privileges, you’ll see an “Administer” tab on the builder page:

[pic]

Figure 8

If you click on “Administer”, on the bottom right, you will see a link that says “Oracle Reports Security Settings”. Clicking that link will take you to a page that looks like this:

[pic]

Figure 9

On this screen (which, oddly, has no “Back” button or link to return us to the Builder page), we can define access to Reports Servers, access to .rdf files, access to printers and access to report calendars. By using these features of Portal, we can use Portal’s security mechanisms and added functionality to restrict access to the various components of Oracle Reports. For example, a Portal Calendar Access object can be created that limits access to non-business hours, so that no report can be run during business hours consuming resources. That calendar object can then be associated to any combination of reports servers, .rdf files and reports printers. If an attempt is made to access a reports server, .rdf file or reports printer outside of the reports calendar access period (i.e. during working hours), the request is then queued for execution during an acceptable time. We also have the ability to restrict access to any of these components based on the user and group privileges for the Portal user.

When an .rdf file is defined in the Reports Definition File Access portlet, it must be associated with a Portal DB Provider. If that report has “Publish to Portal” checked in the “Access” tab of the component summary screen, then that report will show up as an available portlet to be placed on a Portal page. Depending on when the page is viewed, the report will then displayed (if it falls within the calendar parameters associated with the reports server and .rdf file) with absolutely no developer or system administration intervention whatsoever.

You can use keymap file entries when you define entries in the Reports Definition File Access portlet, but it’s tricky to find and a little confusing to use. Click on “Create Reports Definition File Access”. In step 1 of the wizard, give the report a meaningful name. On the second step of the wizard, you’ll start entering characteristics of the report: its name on disk, the report server you want to use, etc. Here’s the tricky part: when we get to the final step, we can specify a keymap entry to use; any parameters specified in that keymap entry will override anything you enter in these screens. To prove this, leave the “Oracle Reports File Name” field blank. Click “Finish” to generate the report definition file access for Portal.

Wait a minute – if we’re finished, how do we specify what keymap file entry we want to use? If you click on either the “Run” or “Run As Portlet” link on the component summary screen, you’ll get the following error message:

REP-50004: No report specified on the command line

which makes sense, since we left the “Oracle Reports File Name” field blank. On the component summary screen, you’ll see a link that says “Customize”. Clicking this link will take you to the following screen:

[pic]

Figure 10

In the CGI/Servlet Command Key field enter: test_report (or a suitable name for a key in your cgicmd.dat file) and click “Save Parameters”. Click the “x” in the top right to close the window and return to the Component Summary screen. Now click either “Run” or “Run as Portlet” and you should see your report. By saving the parameter, the key specified in the CGI/Servlet field will always be used when this report is run in Portal.

Reports Server Administration

As mentioned earlier, we are not limited to just one reports server. There is an executable called rwserver located in the /bin directory. This program can be used to stop, start or create a Reports Server and, if running on the Windows platform, create a Reports Server as a Windows service.

To start a Reports Server:

rwserver server=

Note: If the Reports Sserver you specify does not exist, a new one will be created. By default, a new Reports Server will not show up on the Enterprise Manager web page for your middle-tier. Metalink document 212551.1 details instructions on how to do this.

To stop a Reports Server:

rwserver server= shutdown=immediate

To create a Reports Server as a Windows service, use the install keyword:

rwserver server= install autostart=yes

Component #2: Oracle Forms

There is no native way to integrate Oracle Forms into Oracle Portal. We have to use a technique similar to method #1 for integrating Reports (i.e. the URL component in Portal). First, we need to make sure the Forms server is up and running. If you look back on Figure 2, you’ll see a component called “Forms”. If the status arrow is a check mark, we’re up and running. One thing to notice about the Forms component: the radio button net to the Forms component is grayed out. Normally, to stop, start or restart a component, we select its radio button and then click “Start”, “Stop”, or “Restart” in the right middle of the page. Why can’t we do that with the Forms component?

The forms component is dependant on the OC4J_BI_FORMS component. When it’s up, the Forms component is up, when it’s down, the Forms component is down. If they’re dependant on each other, why are there two of them? There are many reasons, but the most important one is that there are performance metrics associated with the Forms server that allows you to see how efficiently the Forms server is working. These metrics are viewable by clicking the “Forms” link. There is also a screen after you click the “Forms” link to view and modify the Forms Server configuration files.

To test your Forms Server, we’re going to follow steps similar to when we tested the Reports Server. Point your browser to he welcome page for the application server. If the infrastructure and middle-tier are on the same machine, the port number is probably 7778. If they are on separate machines, the port number is probably 7777:

http://:

You should see a page similar to Figure 4. Click on the “Demonstrations” tab, then the “Business Intelligence and Forms” link, then the “Forms Services” link next to the text that starts, “Demonstrates a test form…”. If this is the first time you’re attempting to run an Oracle Form, you will be prompted to install a browser plug-in called Jinitiator. From Oracle’s documentation:

Oracle JInitiator enables users to run Oracle9i Forms applications using Netscape Navigator or Internet Explorer. It provides the ability to specify the use of a specific Java Virtual Machine (JVM) on the client, rather than using the browser's default JVM.

Oracle JInitiator runs as a plug-in for Netscape Navigator and as an ActiveX component for Internet Explorer. Oracle JInitiator does not replace or modify the default JVM provided by the browser. Rather, it provides an alternative JVM in the form of a plug-in.

To get more information about JInitiator, refer to the Oracle9iAS Forms Services Deployment Guide, part # A92175-01, Appendix A.

After JInitiator installs, you should see a new browser window similar to this:

[pic]Figure 11

Securing the Forms URL

Unlike the URL we used to run our first report, the URL to run our form seems pretty innocuous. Not much sensitive information you can get from the one in Figure 11, is there? But how do we specify what form we want to run and what are the characteristics we want to specify? Here’s where Oracle is throwing you a bit of a curve ball. The demo to run a test report does not use a keymap entry (we saw all of the report’s details in the URL), but the demo to run a form does use a keymap entry.

Forms, like Reports, uses a keymap file, only for Forms, it’s called the Forms Servlet Configuration File. It serves the exact same purpose as the Reports keymap file: namely, it hides sensitive details about the form from the user’s eyes in the URL of the browser. The Forms Servlet Configuration File is called formsweb.cfg and is located in /forms90/server. The formats of the files are different, but the concept is the same: a key, followed by attributes. In Oracle’s documentation, the “keys” in the formsweb.cfg file are called “named configurations”.

In the cgicmd.dat file for the reports server, the entries had this format:

key: attribute1 attribute2 etc.

In the formsweb.cfg file for the forms server, the entries have this format:

[named configuration]

attribute1

attribute2

etc

See chapter 3 of the Oracle9iAS Forms Services Deployment Guide for a complete list of the parameters that can be specified.

The URL for the reports server to read the cgicmd.dat file looks like this:



The URL for the forms server to read the formsweb.cfg file looks like this:

configuration

Wait a minute – the demo form that displayed in Figure 11 didn’t have a “config=” at the end of the URL. How did the Forms Server know what form to display? At the beginning of the formsweb.cfg file, there is a default configuration section. These parameters are used unless they are overridden by any named configurations referenced in the file. Since we didn’t reference any, the default parameters were used. One of the default parameters is:

Form=test.fmx

We could have specified a named configuration like this:

[test_form]

form=test.fmx

and then specified the URL like this:



Here’s a strange quirk. Try this:



bogusbogusbogus does not exist in the formsweb.cfg file, yet the form gets displayed with no error message. Why? Remember that the named configurations override any parameters specified in the default configuration section. Since bogusbogusbogus isn’t in the formsweb.cfg file, the default values are used. This is why it’s a good idea to comment out the form= parameter in the default section or re-direct users and developers to a help page.

Like the cgicmd.dat file, if you make any changes to the formsweb.cfg file, you must bounce the OC4J_BI_FORMS component before any changes will take effect.

Integrating the form into Portal is as simple as creating a URL object and performing the steps we did for method #1 of integrating an Oracle Report on to a Portal page.

Component #3: Oracle Discoverer

Using either the URL or native methods to integrate Oracle Forms and Oracle Reports is a tricky, though relatively simple process. Incorporating Discoverer Workbooks into Portal requires a significantly greater number of steps. To start with, there is native support for Discoverer in Portal, but we have to “tell” Portal about it first. In Oracle’s terms, it’s called “Registering the Discoverer Portlet Provider”.

The Discoverer Portlet Provider is a program that runs on the server and facilitates communication between the Discoverer Server and Portal. Thankfully, the first part of this process only has to be performed once – once the Discoverer Portlet Provider is registered with Portal, it remains there. The other steps in the process, however, must be repeated for each Discoverer Worksheet we wish to incorporate into Portal.

Before attempting to register the Discoverer Portlet Provider, we need to make sure all the pieces for Portal and Discoverer are in place and can “speak” to each other. Type the following in your browser:



Where is the middle-tier server and is the default port number of the middle-tier instance. You should see a screen similar to this:

[pic]

Figure 12

If not, check Metalink docs 217185.1, 202268.1 and 236088.1 for suggestions on solutions.

Next, we need to register the Discoverer Portlet Provider in Portal. On the Portal Build page, make sure the “Build” tab is selected and click on “Register A Portlet Provider” in the middle of the page:

[pic]Figure 13

The “Name” and “Display Name” fields in step 1 of the “Register a Portlet Provider” wizard can contain anything, but the Display Name will be displayed to developers when they add a Discoverer Portlet to a page, so make it meaningful. Make sure “Web” is chosen for implementation style. Click next to get to the General Properties page.

In the URL field, enter the URL we used to test the Discoverer portlet provider above:



Make sure the radio button starting with “The user has the same identity…” is selected.

[pic]

Figure 14

Click “Next”. Unless you want to change the default grant access settings, you do not need to enter details in the control access page. Click “Finish”. The Discoverer Portlet Provider is now registered with Portal.

Before we can add Discoverer portlets to a page however, we have to establish a Discoverer connection for 9iAS. Back in Figure 2, the first system component listed for the Middle-Tier was one called “AS_Home.oski-mobile._Discoverer. Clicking that link will take us to the screen where we can configure Discoverer connections for 9iAS. Click on the pencil icon next to the line that says “General Discoverer”. You will see a screen that looks like this:

[pic]Figure 15

Click “Create Connection” and enter connection information for a user that has a Discoverer EUL (End-User Layer) built. The connect string entered here must match an entry in the tnsnames.ora file in the /rdbms/admin directory on the server. When it comes time to place a Discoverer portlet onto a Portal page, we will be asked to enter the name of the Discoverer connection we define on this page. Different workbooks and different portal pages can use the same Discoverer connection.

To place a Discoverer portlet on a page, go to the Page Groups tab and select a page group. Click the Layout link on the top of the page. In a region on the page, click the Add Portlets icon, like we did in Figure 7. Click on the “New” link. You should see a new folder with the name you gave the Discoverer Portlet Provider when you registered it in the steps right after Figure 13 (in my example, it’s called “Discoverer Portlet”):

[pic]Figure 16

If we click the Discoverer Portlet link, we only have 2 choices: adding a Worksheet or a List of Database Workbooks. Click Worksheet, so that it appears on the right hand side of the page under “Selected Portlets”. We will specify which Worksheet we want to display in the next step. Clicking “OK” on the top right part of the screen will take us back to the Page Layout screen. Click “Graphical” in the top-left part of the screen to display the page graphically. You should see something like this:

[pic]

Figure 17

This makes sense since we haven’t defined which Discoverer Worksheet we want to display yet. We can’t do that editing the page in Graphical mode; we can only do it by editing the page in “Layout” mode, so click the “Layout” link on the top left side of the page. In the middle of the page, in the region where you added the Discoverer portlet, you should see a link that says “Edit Defaults”. Clicking that link will take you to the “Edit Worksheet Portlet Defaults” wizard. This wizard will step you through the definition of the Discoverer portlet.

[pic]

Figure 18

This wizard asks us to enter the connection we created in created in Figure 15. Leave the other selections with their default values. Click “Next” on the bottom right of the page. On the second page of the wizard, we specify the Workbook we want to display. A Workbook can be composed of numerous worksheets, so on the third page of the wizard, specify the worksheet in the workbook you want to display. If there are any parameters associated with the worksheet, we are taken to the parameters page, where we can specify parameter values. If there are no parameters associated with the worksheet, we are taken directly to the “Portlet Settings” page where we can specify the title of the portlet.

The “Refresh Options” page takes a little explaining. When the page is constructed and the Discoverer portlet is rendered, it is cached so that subsequent calls to display the page occur quickly. This may be a problem if the information on the Discoverer portlet is dynamic and needs to be refreshed on a regular basis. You can specify a specific time or an interval to refresh the data in the Discoverer portlet. The most frequent time that can be specified is 1 hour.

Finally, there is a review page to check all of your selections. After clicking “Finish” on the review screen, you can see the results of your work. You will automatically be taken back to the page layout screen. Click “Graphical” in the top right hand part of the screen. You should now see the Discoverer report displayed:

[pic]

Figure 19

Summary

As we have seen, the designers of Portal left us with enough flexibility to incorporate non-Portal items into our Portal sites relatively easily. There is native support for Oracle Reports and Oracle Discoverer worksheets and even without native support for Oracle Forms, there are still methods for incorporating these forms into Portal. Integrating these existing components gives us the benefit of not having to duplicate any existing development work, while gaining all of the benefits Portal provides for us.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download