6 Oracle Express Pluggable Data Source

This chapter describes how to configure and use the Express Pluggable Data Source with Oracle9iAS Reports Services.

6.1 Before You Begin

To use the Express Pluggable Data Source within Oracle9iAS Reports Services, you must connect to a supported Express Server version, which are versions 6.2.x and 6.3.x. When you install Reports9i, SNAPI 9.0.1 is automatically installed to enable connections to these Express Server versions.

Before creating reports using Express data, verify that you have completed all the necessary configuration steps. See the configuration topics in the Help system by searching for "configuring the Express data source."

In the Help topic called "About Configuring the Express Data Source," it mentions that the Express Connection Editor is installed with Reports. This is no longer the case, and you must take the appropriate steps to ensure that you can connect to Express Server, either by using the Express Connection Editor to create connection files or by creating them manually. See the section "Preparing for Express Connections" later in this document.

6.2 Preparing for Express Connections

This section outlines the necessary information that you need to keep in mind when you go about preparing the express connections.

6.2.1 Creating connection files

To create reports of Express data, you must be able to connect to an appropriate Express Server instance. To connect to an instance of Express Server, you use a connection file, which is a simple text file with the XCF extension. Each connection file defines a single connection to Express Server. A file called xconnect.ini specifies where connection files are located.

You can create connection files manually or you can use a utility called the Express Connection Editor to assist you in creating these files. The following list outlines the ways to create and use connection files, which are explained in detail below.

Use an already installed version of the Express Connection Editor.
Download a file from the Oracle Technology Network with which you can install just the Express Connection Editor.
Install just the Express Connection Editor from the Express Client CD.
Create the files manually using a text editor.

6.2.2 Related information

This document includes samples of three types of connection files in a later section.For complete information on connecting to Express Server and on the Express Connection Editor, consult the following sources:

The Oracle Express Database Administration Guide (part number A82800_02).
The Help system for the Express Connection Editor.

6.2.3 Using an already installed version of the Express Connection Editor

If you already have the Express Connection Editor installed, then you can simply use that version to create an XCF with which you can connect to Express Server. You might not have to create any XCF files, if the appropriate ones have already been created. Use the following procedure to use the already installed Express Connection Editor with Oracle9i Reports.

To use an existing Express Connection Editor installation:

Navigate to the /olap subdirectory in the Oracle home directory for Oracle9i Reports Developer.
Within the /olap subdirectory, create a subdirectory called ecf901.
To the newly created ecf901 subdirectory, copy the xconnect.ini file from the installation directory of the Express Connection Editor.
Open the xconnect.ini from the ecf901 subdirectory in a text editor. Ensure that the ConnectionPath setting points to whatever directory or directories hold the connection files that you plan to use for connecting to Express Server from Oracle9i Reports Developer. Use these files or use the Express Connection Editor to create other connection files.

Tip:

Use semicolons to separate multiple directory specifications for ConnectionPath, if you want to store connection files in multiple locations.

6.2.4 Downloading a file from OTN to install just the Express Connection Editor

If you do not already have the Express Connection Editor installed, then you can download the necessary files. Use the following procedure to download the file and install the Express Connection Editor.

To download a file and install the Express Connection Editor:

In a Web browser, access the Oracle Technology Network (http://otn.oracle.com).
Navigate to the Oracle9i Reports Developer area.
Download the file with which you can install the Express Connection Editor.
Unzip the downloaded file and use the setup.exe file to run the installation program to install just the Express Connection Editor.

Important:

Ensure that you install the Express Connection Editor in the same Oracle home directory into which Oracle9i Reports Developer is installed.

Once the Express Connection Editor is installed, you can use it to create connection files.

6.2.5 Install just the Express Connection Editor from the Express Client CD

If you do not already have the Express Connection Editor installed, then you can use the Express Connection Editor that is supplied with the Express Client products. You can obtain a CD for the Express Client products or download a file that contains them. Use the following procedure to install just the Express Connection Editor.

To install just the Express Connection Editor:

Run the installation program by using the Express Client CD or by accessing the Express Server area of the Oracle Technology Network (http://otn.oracle.com/software/products/exp_server/htdocs/winsoft.html) and following the directions there.
In the appropriate location in the installation program, specify the same Oracle home directory into which Oracle9i Reports is installed.
Select a custom installation.
Select the Oracle Express Connection Editor in the Available Product Components page.Complete the other pages of the installation as appropriate.

Once the Express Connection Editor is installed, you can use it to create connection files.

6.2.6 Create the files manually using a text editor

If you do not already have the Express Connection Editor installed and you do not want to install it, then you can create XCF files manually using a text editor. You must also create the xconnect.ini file, which specifies the location of the XCF files.

To create an XCF file manually:

Navigate to the /olap subdirectory in the Oracle home directory for Oracle9i Reports Developer.
Within the /olap subdirectory, create a subdirectory called ecf901.
In this document, locate the sample file that is closest to the XCF file that you need to create.
Open a text editor such as Microsoft Notepad.
Either cut and paste the text from this document or type the text of the XCF file into the text editor.
Edit the settings of the file as appropriate, using the information that is provided in the section "XCF file settings," later in this document.
Save your changes, giving the file any name that you want and including the XCF extension. Ensure that you save the file into the /olap/ecf901 subdirectory in the Oracle home directory for Oracle9i Reports Developer.
Begin the process of creating the xconnect.ini file by creating an empty document in a text editor such as Microsoft Notepad.
Edit the file to contain the following contents, substituting the name of the Oracle home directory for Oracle9i Reports Developer for ORACLE_HOME:
```
[General]
ConnectionPath=ORACLE_HOME\olap\ecf901;
```
Tip:
Use semicolons to separate multiple directory specifications for ConnectionPath, if you want to store connection files in multiple locations.
Save your changes, naming the file xconnect.ini. Ensure that you save the file into the /olap/ecf901 subdirectory in the Oracle home directory for Oracle9i Reports Developer.

6.2.7 XCF file settings

The following table briefly describes the main settings in any XCF file. The next table describes the settings that apply only to connections through Oracle Express Relational Access Manager. All these settings are described in more detail in the Help system for the Express Connection Editor and in the Oracle Express Database Administration Guide.

Table 6-1 XCF File Settings

Setting	Description
ConnectionType	Enter the type of connection: 1 for connections for Oracle Express Relational Access Manager. 0 for all other connections.
ServerDescription	Enter the description for this connection. The description is visible to users when they choose which connection to make to Express Server. Therefore, you should make the description no more than approximately 40 characters.
ServerVersion	Enter 1 for Express Server 6.x. No other values are applicable to connections from Oracle9i Reports Developer.
ServerType	Enter 1 for Express Server 6.x. No other values are applicable to connections from Oracle9i Reports Developer.
ServerLogin	See the list that follows this table for a description of this setting.
ServerString	Enter the name of the server machine on which Express Server 6.x is running. The Express Connection Editor includes more than just the server machine name in the connection file, which is unnecessary for connections from Oracle9i Reports Developer.

The following list describes the values for the ServerLogin setting. For this setting, you enter the authentication type for this connection:

0 for None, no authentication is required.
-1 for Host (Server Login), which specifies host authentication with a machine login. Use host authentication when the client machine is not on the Microsoft network. Use this setting when the server machine is not in the domain (and/or if there is no domain account) and the user has a server machine account. Only the user ID and password are required for logging into the server machine.
-2 for Host (Domain Login), which specifies host authentication with a domain login. Use host authentication when the client machine is not on the Microsoft network. Use this setting when the server machine is within the domain and the user has a domain account. The domain name, user ID, and password are required for logging into the server machine.

The following settings apply to domain authentication with a domain login. Domain authentication is the preferred way of authenticating clients for Windows NT Server and for Express Server 6.x. If you want to use domain authentication, then the client and the server must be on the Microsoft network. Also, the user has to have a domain account within the same domain that the server machine is on. The domain name, user ID, and password are required for logging into the server machine.
- 1 for domain authentication with connection, which specifies that a user's identification is checked when connecting to Express Server. A user must have logged into the domain with a valid user name and password.
- 2 for domain authentication with call, which specifies that a user's identification is checked on every call to Express Server.
- 3 for domain authentication with packet, which specifies that a user's identification is checked on every packet sent to Express Server.
- 4 for domain authentication with integrity, which specifies that a calculated cryptographic checksum is attached to every packet so that any tampering with the packet can be detected. A user's identification and the checksum is checked on every packet sent to Express Server. If the checksum received does not match the checksum sent, then the packet is discarded.
- 5 for domain authentication with privacy, which specifies that the packet is encrypted. This option also uses the security measures that are available with the integrity option.

The following table briefly describes the Oracle Express Relational Access Manager settings in a connection file, which appear after the main settings that are described in the previous table.

Table 6-2 Oracle Express Relational Access Manager Settings

Setting	Description
ConnectionType	Enter 0. No other values are applicable to connections from Oracle9i Reports.
MasterDB	Enter the name of the Relational Access Manager database to attach initially. You must specify only the database file name. You can get the database name in either of the following ways: In the Express Relational Access Administrator, it is the Express Database Name that is displayed in the Database tab. In the `.RDC` file, it is specified as the DBName in the `[DBInfo]` section. This database must reside in a folder that is included in the path list in ServerDBPath. You can check the ServerDBPath in the I/O Management sheet of the Express Instance Manager.
PromptforExpressID	Enter 1 to prompt for an Express user ID before making the connection or 0 to not prompt. This setting applies only when PersonalConfig is set to 1.
ServerScript	Enter the complete file name (including the full path) of the remote database configuration file on the server. This file specifies information such as the location of code and data databases. Using UNC (Universal Naming Convention) syntax allows multiple users to use the same connection to access the data without having to map the same drive letter to that location. UNC syntax is `\\ServerName\ShareName\` followed by any subfolders and/or files.
PersonalConfig	Enter 1 to create and attach a personal database with read/write access so that you can use many Oracle Sales Analyzer features such as custom measures. Enter 0 to not create this database. To enable this setting, you must have a account on the Express Server system. If this setting is 0, then you log in as a guest. This setting applies only with direct connections to Express Server 6.x. This setting does not applies when ServerLogin is set to 0.

6.2.8 Sample connection files

This section provides samples of three types of connection files. You can use these samples as the basis for creating connection files manually.

Sample for connecting without using authentication

The following text shows a sample XCF file for connecting to Express Server without using authentication.

[Express]
ConnectionType=0
ServerDescription=expservername ExpSrv6.3.0.2 without authentication
ServerVersion=1
ServerType=1
ServerLogin=0
ServerString=expservername

Sample for connecting with using authentication

The following text shows a sample XCF file for connecting to Express Server using authentication.

[Express] 
ConnectionType=0
ServerDescription= expservername ExpSrv6.3.0.2 with authentication
ServerVersion=1
ServerType=1
ServerLogin=-1
ServerString=expservername

Sample for connecting with Oracle Express Relational Access Manager

The following text shows a sample XCF file for connecting through Oracle Express Relational Access Manager (RAM).

[Express]
ConnectionType=1
ServerDescription=expservername ExpSrv6.3.0 with RAM
ServerVersion=1
ServerType=1
ServerLogin=-1
ServerString= expservername
[Relational Access Manager]
ConnectionType=0
MasterDB=d:\RAM\dram.db
PromptForExpressID=0
ServerScript=d:\RAM\ram.rdc
PersonalConfig=0

6.3 Known Issues

The Express Pluggable Data Source does not support using password-protected Express databases files.
The maximum number of dimensions for an Express query is 10.
You might see the following message: "XR-2005: Express Server cannot execute a command that is specified by the query. The command is invalid." This message can be generated for very large queries (row counts in the millions) if the SNAPI buffer is not sufficiently large. To increase the buffer size from the default of 2MB, create or edit the xrpdsprefs.ora file in the ORACLE_HOME/reports/jlib directory and add the following line:
```
options.snapi.FetchAlloc=size
```
where size is a value greater that 2M (or 2000K, or 2,000,000), as shown in the following example:
```
options.snapi.FetchAlloc=10M
```
See the "Specifying a buffer size for Express data" topic in the online help system for complete information on this option.
When Oracle8 and Express Server are installed on the same Solaris machine and the Express connection specifies an Authentication Type of "None", you may see the following error when running a report that contains Express data:
```
ORA-20004: Failed to attach to an Express database.
```
This error indicates that there is an access problem with the Express database. When the Authentication type is set to "None" and Oracle8 and Express Server are on the same machine, the SNAPI connection uses the user of the process that is establishing the connection. In this configuration, it is the user that started the Oracle8 database. Refer to the Oracle Express Server Installation and Configuration Guide for Solaris for information on accessing Express databases.
If you suspect language incompatibilities between Oracle9iAS Reports Services and the data from Express Server, then whenever possible, ensure that the following settings all specify the same language/character set:
- The operating system's regional or locale setting for the machine on which you are running Oracle9iAS Reports Services.
- The actual data in the Express database.
  
  If your environment does not permit these settings to match, then you should add the options.data.XPCharSet line to the xrpdsprefs.ora file and specify the language/character set for the data in the Express database. The format of this setting matches that of the NLS_LANG setting and a sample setting is shown here:
```
options.data.XPCharSet=JAPANESE_JAPAN.JA16SJIS
```
  See the Help system for more information on the xrpdsprefs.ora file.