How to: Retrieve Data from an Oracle Data Source
Reporting Services provides two ways to connect to an Oracle relational database: the .NET Framework Managed Provider for Oracle data processing extension or the OLE DB data processing extension that uses the Oracle data provider. For more information, see Data Sources Supported by Reporting Services.
To use data from an Oracle database in your report, you must define an Oracle data source and one or more report datasets. When you define the data source, you must specify a connection string and credentials so that you can access the data source from your client computer.
You can create an embedded data source definition for use by a single report or a shared data source definition that can be used by multiple reports. The procedures in this topic describe how to create an embedded data source. For more information about shared data sources, see Shared Data Sources and Embedded Data Sources and Creating, Modifying, and Deleting Shared Data Sources.
After you create an Oracle data source definition, you can create one or more datasets. After you create a dataset, the name of the dataset appears as a node in the Report Data pane under its data source. For more information, see Working with Report Designer in Business Intelligence Development Studio.
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.
To create an embedded data source that uses the .NET Managed Provider for Oracle data processing extension
On the toolbar in the Report Data pane, click New, and then click Data Source.
In the Data Source Properties dialog box, type a name in the Name text box or accept the default name.
Verify that Embedded connection is selected.
From the Type drop-down list, select Oracle.
Specify a connection string that works with your Oracle data source.
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"Click Credentials.
Set the credentials to use to connect to the data source.
Click OK.
The data source appears in the Report Data pane. For more information, see Using the .NET Framework Data Provider for Oracle on MSDN.
To create an embedded data source that uses the OLE DB data processing extension
On the toolbar in the Report Data pane, click New, and then Data Source. The Data Source Properties dialog box opens.
In Name, type a name or accept the default name.
Verify that Embedded connection is selected.
From the Type drop-down list, select OLE DB.
Provide a connection string that specifies the Microsoft OLE DB Provider for Oracle and works with your Oracle data source.
Contact your database administrator for connection information. The following connection string example specifies using the Microsoft OLE DB Provider for Oracle for an Oracle database on a server named "Oracle9". The server name must match what is defined in the tnsnames.ora configuration file as the Oracle server instance name:
Provider="MSDAORA.1";Data Source="Oracle9"Click Credentials.
Set the credentials to use to connect to the data source. For more information, see Specifying Credential and Connection Information for Report Data Sources.
Note
To test the data source connection, click Edit. In the Connection Properties dialog box, click Test Connection. If the test is successful, you will see the informational message "Test connection succeeded". If the test fails, you will see a warning message with more information about why the test was not successful.
Click OK.
The data source appears in the Report Data pane. For more information, see Using the .NET Framework Data Provider for Oracle on MSDN.
To create a dataset for an Oracle data source
In the Report Data pane, right-click the name of the data source that connects to an Oracle data source, and then click Add Dataset.
In the Query page of the Dataset Properties dialog box, type a name in the Name text box or accept the default name.
In the Data source text box, verify that the name of the data source you right-clicked appears.
Verify that Text is selected in the Query type box. Click Query Designer to open the graphical query designer to build a query interactively. For more information, see Graphical Query Designer User Interface.
Alternatively, you can paste a query from the clipboard directly into the Query pane, or import an existing SQL query from a file or from another report. For more information, see Text-based Query Designer User Interface.
Click OK.
The dataset and its field collection appear in the Report Data pane under the data source node.
See Also
Concepts
Creating Report Datasets from an Oracle Database
Connecting to a Data Source (Reporting Services)
Working with Fields in a Report Dataset
Connecting To Report Data How-to Topics
Using Query Parameters with Specific Data Sources (Reporting Services)