PQ Systems Knowledge Base

Revision Date: 13-Dec-2005

This article covers issues involved with using Oracle as a CHARTrunner data source.

On the Data definition tab of the chart definition the SQL Settings button allows you to specify whether to "Surround table names and column names with brackets…".  This option should always be unchecked for an Oracle database.  An Oracle database often returns an error such as "ORA-00936: missing expression" if this option is checked.

PQ knows of the following four data providers that can be used with Oracle:

1. Oracle Provider for OLE DB (recommended)
2. Oracle ODBC Driver
3. Microsoft OLE DB Provider for Oracle
4. Microsoft ODBC for Oracle

Oracle Provider for OLE DB

The "Oracle Provider for OLE DB" that Oracle supplies is installed when the "Oracle Client" software is installed.  

This provider is preferred by PQ Systems for charting data from an Oracle database.

You can download this provider from Oracle. Net8 (if you are using Oracle 8, or the appropriate Oracle client software for the version of your Oracle server) must be installed on the computer in order for this provider to function properly.

Here is a sample CHARTrunner connection string that uses the Oracle Provider for OLE DB:

Provider=oraoledb.oracle;Data Source=oracle8.empire;PLSQLRSet=1;User ID={{USER}};Password={{PASSWORD}};

The provider name oraoledb.oracle must be spelled exactly as shown.

You will need to substitute the appropriate name for your "Data Source" in place of oracle8.empire shown in the example above. The value for Data Source=?????? is determined by how your Oracle services were named when Net8 (or higher) was setup on the computer where CHARTrunner runs. It should be possible to find the proper service name to use by running "Net8 Assistant" (for Oracle 8) or "Net Manager" (for Oracle 9) and by looking in the "Service Naming" folder you should see the available service names. If you plan on using the same CHARTrunner chart definition (i.e. CRF file) on multiple computers, then each computer must have the same service name(s) so that the "Data Source" name specified in the chart definition will work on each computer.

In addition, if a stored procedure is going to be used as the data source you must include PLSQLRSet=1 in the connection string (as shown above) so that the driver binds automatically to the REF CURSOR variable that returns the rowset from the stored procedure.  You can omit PLSQLRSet=1 from the connection string if a stored procedure is not used.

For example:

Oracle ODBC Driver

The Oracle ODBC driver is installed when the "Oracle Client" software is installed. You can also download it from Oracle and install it separately. Net8 (if you are using Oracle 8, or the appropriate Oracle client software for the version of your Oracle server) must be installed on the computer in order for the ODBC driver to function properly.

Here is the CHARTrunner connection string used with the ODBC driver:

DSN=Oracle ODBC to oracle8.empire;Uid={{USER}};Pwd={{PASSWORD}};

You will need to substitute the name of your ODBC DSN (i.e. data source) in place of the text shown in italics above.

 For example:

Microsoft OLE DB Provider for Oracle

Microsoft provides an ADO provider named MSDAORA as part of MDAC (Microsoft Data Access Components).

This provider does not seem to work well in testing at PQ, so we recommend the "Oracle Provider for OLE DB" (described above) that is provided by Oracle instead.

A sample connection string is shown below:

Provider=MSDAORA;Data Source=knight1.oracle8;User ID={{USER}};Password={{PASSWORD}};

Microsoft ODBC Driver for Oracle

Microsoft provides an ODBC driver for Oracle as part of MDAC (Microsoft Data Access Components).

Two sample connection strings are shown below:

DRIVER={Microsoft ODBC for Oracle};SERVER=Your_Server_Name;Uid={{USER}};Pwd={{PASSWORD}}

   or

DSN=Your_Microsoft_ODBC_DSN_Name;Uid={{USER}};Pwd={{PASSWORD}}

User Name and Password

For an ADO/OLE DB or ODBC data source the User and Password fields on the CHARTrunner "Data source" tab can be used to substitute the user name and password (for authenticating to the Oracle database server) into the Connection string as shown in this example:

Provider=oraoledb.oracle;Data Source=knight1.oracle8;User ID={{USER}};Password={{PASSWORD}};

Wherever {{USER}} is found in the connection string CHARTrunner will at runtime substitute the current User as entered in the field above the Connection string field.  As an alternative, you can put the user's name in the connection string, such as:

Provider=oraoledb.oracle;Data Source=knight1.oracle8;User ID=scott;Password={{PASSWORD}};

Wherever {{PASSWORD}} is found in the connection string CHARTrunner will at runtime substitute the current Password as entered in the field above the Connection string field.  In this manner the password is stored in an encrypted form in the CRF file, thus providing a measure of security.  As an alternative, you can put the password in the connection string (the password will be stored in the CRF file in plain text), such as:

Provider=oraoledb.oracle;Data Source=knight1.oracle8;User ID=scott;Password=tiger;

Other Resources

Carl Prothman's excellent site has many examples of ADO/OLE DB and ODBC connection strings for Oracle.

Ref #766