Tap into Oracle Databases with SQL Server Reporting Services
The data-retrieval layer of Microsoft SQL Server Reporting Services (SSRS) supports a variety of data sources, including MS SQL Server, Oracle, ODCB, OLE DB, as well as several custom ones. The product has been optimized for MS SQL Server, so SSRS interacts seamlessly with that data source option. Connecting to other data sources, however, involves some additional subtleties.
This article examines SSRS interaction with Oracle databases. Under the hood, SSRS uses ADO.NET classes. Hence, to display data from the data source onto the report, you need to create a dataset that fetches the data from the data source. The data source contains the information required to connect to the database, including its name and credentials. A dataset, which contains the required query information and parameters, refers to such a data source to connect and retrieve information.
Reporting Services, Go Fetch!
Fetching data from an Oracle data source with a select query is not very complicated. In fact, it is similar to the process you would follow for Microsoft SQL Server. However, using a stored procedure for data retrieval makes the job a bit more complex.
Internally, SSRS uses the Oracle Data Provider for .NET (ODP.NET) to interact with Oracle databases. Hence, the ODP.NET's restrictions apply to SSRS as well. As an example, consider a simple solution that fetches data from an Oracle stored procedure. This section demonstrates the solution and then examines the subtleties involved as compared with the processes for databases that SSRS primarily targets.
To start, create a new reporting services project in Visual Studio. Use the Report Project option and not the wizard (this will help you understand things better). Next, right-click the reports folder and, from the Add Menu, select "New Item" and add a report (.rdl) to it as "TestOracle.rdl".
Now, add a report file (.rdl) named "TestOracle.rdl" to the report.
Open the rdl file in the Visual Studio editor. On the data tab, select <New Dataset> from the dataset dropdown. This should open up the property pages for the connection.
On the Provider tab, select Microsoft OLE DB for Oracle (see Figure 1) and click "Next." On the Connection tab, enter the Oracle service name as the server name and the username and password. (You can opt for saving the password here.)
Figure 1. Provider tab of the Data Link Properties Dialog
When done, test the connection with the test connection button at the bottom and click OK.
This should create a new dataset as DataSet1 in the dataset dropdown. Select the new dataset and click the ellipse button beside it. This brings up the property pages for the Dataset configuration. Click the ellipse besides the data source to verify that the data source type is set to "Oracle." This ensures that SSRS uses the ODP.NET for underlying connectivity. The data source type is normally defaulted to OLE DB.
On the query tab of the dataset property pages, select the Command Type as - Stored Procedure. Compared with ADO.NET, this step is the same as the Command type parameter you would specify while creating a command object for fetching data from a stored procedure. The query string text would contain only the stored procedure name, possibly qualified by its package name in Oracle. Click OK on this screen to fetch the available fields.
Oracle Database Details
Although simple, the preceding process involves certain things that need to be handled dutifully.
Unlike SQL Server, Oracle returns resultant rows in a cursor. To use a stored procedure for data retrieval within SSRS, consider the following things:
- The rows must be returned with an OUT REF CURSOR.
- Only one OUT REF CURSOR can be returned from a stored procedure. In case of multiple OUT REF CURSORs, SSRS considers only the first one and simply ignores the rest.
Note: ADO.NET provides the capability to interact with multiple OUT CURSORs. However, SSRS abstracts a lot of the required plumbing code and hence is not able to provide this facility.
- By default, the data source type is set to OLE DB. You should change this to Oracle to use ODP.NET features.
- Once the fields are retrieved, SSRS automatically creates report parameters that match the stored procedure parameters. You should map these to the appropriate values based on your business logic. You can map these stored procedure parameters to the report parameters in the expression editor.
Once you've taken care of these things, SSRS generates a dataset that can be used to format a report. You can utilize other features, such as manipulation of the dataset fields and report formatting, just as you would with any other data source.
About the Author
Sanket Bakshi is a Senior Programmer/Analyst for Mahindra British Telecom, where he has executed projects with Microsoft .NET and BizTalk Server 2004. Sanket has extensive experience with designing and development in ASP.NET, C#, VB, ASP, C, and VB.NET.