Share via

Oracle Connection Type (SSRS)

  • Article

To use data from an Oracle database in your report, you must you must have a dataset that is based on a report data source of type Oracle. This built-in data source type is based on the .NET Framework Managed Provider for Oracle and requires an Oracle client software component.

Use the information in this topic to build a data source. For step-by-step instructions, see Add and Verify a Data Connection or Data Source (Report Builder and SSRS).

Connection String

Contact your database administrator for connection information and for the credentials to use to connect to the data source. The following connection string example specifies an Oracle database on the server named "Oracle9" using Unicode. The server name must match what is defined in the Tnsnames.ora configuration file as the Oracle server instance name.

Data Source="Oracle9"; Unicode="True"

For more information about connection string examples, see Data Connections, Data Sources, and Connection Strings in Report Builder.

Credentials

Credentials are required to run queries, to preview the report locally, and to preview the report from the report server.

After you publish your report, you may need to change the credentials for the data source so that when the report runs on the report server, the permissions to retrieve the data are valid.

For more information, see Data Connections, Data Sources, and Connection Strings in Reporting Services or Specify Credentials in Report Builder.

Queries

To create a dataset, you can either select a stored procedure from a drop-down list or create an SQL query. To build a query, you must use the text-based query designer. For more information, see Text-based Query Designer User Interface (Report Builder).

You can specify stored procedures that return only one result set. Using cursor-based queries are not supported.

Parameters

If the query includes query variables, corresponding report parameters are automatically generated. Named parameters are supported by this extension. For Oracle version 9 or later, multivalue parameters are supported.

Report parameters are created with default property values that you might need to modify. For example, each report parameter is data type Text. After the report parameters are created, you might have to change default values. For more information, see Report Parameters (Report Builder and Report Designer).

Remarks

Before you can connect an Oracle data source, the system administrator must have installed the version of the .NET Data Provider for Oracle that supports retrieving data from the Oracle database. This data provider must be installed on the same computer as Report Builder and also on the report server.

For more information, see the following:

Alternate Data Extensions

You can also retrieve data from an Oracle database by using an OLE DB data source type. For more information, see OLE DB Connection Type (SSRS).

Report Models

You can also create models based on an Oracle database.

Platform and Version Information

For more information about platform and version support, see Data Sources Supported by Reporting Services (SSRS) in the Reporting Services documentation in SQL Server Books Online.

How-To Topics

This section contains step-by-step instructions for working with data connections, data sources, and datasets.

Add and Verify a Data Connection or Data Source (Report Builder and SSRS)

Create a Shared Dataset or Embedded Dataset (Report Builder and SSRS)

Add a Filter to a Dataset (Report Builder and SSRS)

These sections of the documentation provide in-depth conceptual information about report data, as well as procedural information about how to define, customize, and use parts of a report that are related to data.

Add Data to a Report (Report Builder and SSRS)
Provides an overview of accessing data for your report.

Data Connections, Data Sources, and Connection Strings in Report Builder
Provides information about data connections and data sources.

Report Embedded Datasets and Shared Datasets (Report Builder and SSRS)
Provides information about embedded and shared datasets.

Dataset Fields Collection (Report Builder and SSRS)
Provides information about the dataset field collection generated by the query.

Data Sources Supported by Reporting Services (SSRS) in the Reporting Services documentation in SQL Server Books Online.
Provides in-depth information about platform and version support for each data extension.

See Also

Report Parameters (Report Builder and Report Designer)
Filter, Group, and Sort Data (Report Builder and SSRS)
Expressions (Report Builder and SSRS)