Connect to Oracle database.

Hi!

Recently I started to use Reporting Services for a project I am working on. I have a set of Oracle stored procedures that I have to use. All of them return the data I need in output variables.

Any idea about how to use this kind of procedures with Reporting Services.

Thanks in advance for your help!First, make sure that you are running at least RS 2000 with SP1 installed on report designer and report server.

Check the following article for general information about how to connect to Oracle from RS: http://support.microsoft.com/default.aspx?scid=kb;en-us;834305

Choose "Oracle" as data source type which will give you the managed provider for Oracle. Also make sure that you use the text-based generic query designer (2 panes !) instead of the visual query designer (4 panes) - you can switch between them through an icon on the toolbar in the data view of report designer.

In addition, how do you return the data from your stored procedure? Note: only an out ref cursor is supported (but not Output parameters!). Please follow the guidelines in the following article on MSDN (scroll down to the section where it talks about "Oracle REF CURSORs") on how to design the Oracle stored procedure:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp

To use a stored procedure with regular out parameters, you should either remove the parameter (if it is possible) or write a little wrapper around the original stored procedure which checks the result of the out parameter and just returns the out ref cursor but no out parameter.

Finally, in the generic query designer, just specify the name of the stored procedure without arguments and the parameters should get detected automatically.

There also have been several discussion threads about Oracle stored procedures on the following newsgroup:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlserver.reportingsvcs
You may want to search for these in case you are running into issues.
-- Robert|||

Hello Robert,

I have the problem to connect Oracle data for my Report designer.

1st I used ODBC, it does not work, and my reference book "MS SQL server 2000 Reporting service step by step" tells me that I must select Oracle data provide instead of the generic ODBC.

I created a new data source and after I enter the userId/pswd and click "Test Connection" button, it is OK. But when I click "run" icon to try, it gives me the following error messages:

An error occurred while executing the query, ORA-00936:Missing expression

Additiona information:

ORA-00936:missing expression(system.Data.OracleClient)

My version of reporting service is 2005. The setting of "Connection Properties" as follows:

Data Source: Oracle (OracleClient) Server name: creonpra Log On to the database: UserId/Pswd= qmprodviewer/*********

I have very less knowledge about Oracle. I do not know Oracle Client software's coverage. I checked my program and only find Oracle 8i of "Net8 Assistant", "Net8 Config Assistant" and Oracle ODBC test" on my desktop PC.

Please help me to resolve the problem

|||

Have you tried executing the stored procedure from your report designer? It may be that you are able to connect, but erroring out on something specific with the stored procedure.

I'd execute it from the Data tab and check if it returns data first and rule out that the stored procedure works or not.