Home » Categories » CHARTrunner » CR Data Source

CR: Oracle Data Source

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

Attachments Attachments
There are no attachments for this article.
Related Articles RSS Feed
CR: Using Custom Parameters in Your Custom Queries
Viewed 2185 times since Fri, Nov 9, 2007
CR: Using a Text File as a Data Source
Viewed 4995 times since Tue, Feb 5, 2008
CR: Minitab as a Data Source
Viewed 2381 times since Fri, Nov 9, 2007
CR: Using a Password Protected Microsoft Access Database
Viewed 2391 times since Fri, Nov 9, 2007
CR: SQL Server Data Source
Viewed 3766 times since Fri, Nov 9, 2007
CR: Charting from a FoxPro Data Source
Viewed 2026 times since Fri, Nov 9, 2007
CR: Chart refresh is not refreshing when data is added
Viewed 2772 times since Mon, Mar 3, 2008
GN: MDAC Information
Viewed 22026 times since Mon, Nov 29, 2010
CR: CHARTrunner locks data source
Viewed 1740 times since Fri, Nov 9, 2007
CR: Fetching Specification Limits from the Data Source
Viewed 1682 times since Fri, May 30, 2008