Oracle Support

I'm hoping someone at MS is monitoring this and can give us some hope.
Another thread in this forum suggests that Reporting Services Oracle support
is not all there yet. Is this true? If so, what's the plan?
My specific need is to execute Oracle [stored] procedures that take
parameters to build my reports. Can I use RS or not?
Oracle's supposed to be supported, and executing a stored procedure to get
data seems like pretty basic stuff, so I'm surprised it doesn't seem to do
it. I'm still trying, but so far I haven't read that it can't be done.
PeterIt can definitely be done. Oracle is not supported for Report Models but if
you are using regular reports then yes, you can use Oracle stored
procedures.
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:<u$GEp3hNFHA.3512@.TK2MSFTNGP15.phx.gbl>...
> Check this 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) and not 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. 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 this newsgroup. You may want to search for these in case you
> are running into issues.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Peter Manse" <PeterManse@.discussions.microsoft.com> wrote in message
news:97A4321D-60D3-42F5-8F2E-AC1A6A2C171A@.microsoft.com...
> I'm hoping someone at MS is monitoring this and can give us some hope.
> Another thread in this forum suggests that Reporting Services Oracle
> support
> is not all there yet. Is this true? If so, what's the plan?
> My specific need is to execute Oracle [stored] procedures that take
> parameters to build my reports. Can I use RS or not?
> Oracle's supposed to be supported, and executing a stored procedure to get
> data seems like pretty basic stuff, so I'm surprised it doesn't seem to do
> it. I'm still trying, but so far I haven't read that it can't be done.
> Peter|||Here's a post from a MS employee. Note that it is for RS 2003, still, even
with 2005 it should help you;
"Robert Bruckner [MSFT]" <robruc@.online.microsoft.com> wrote in message
news:<u$GEp3hNFHA.3512@.TK2MSFTNGP15.phx.gbl>...
> Check this 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) and not 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. 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 this newsgroup. You may want to search for these in case you
> are running into issues.
>
> -- Robert
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Peter Manse" <PeterManse@.discussions.microsoft.com> wrote in message
news:97A4321D-60D3-42F5-8F2E-AC1A6A2C171A@.microsoft.com...
> I'm hoping someone at MS is monitoring this and can give us some hope.
> Another thread in this forum suggests that Reporting Services Oracle
> support
> is not all there yet. Is this true? If so, what's the plan?
> My specific need is to execute Oracle [stored] procedures that take
> parameters to build my reports. Can I use RS or not?
> Oracle's supposed to be supported, and executing a stored procedure to get
> data seems like pretty basic stuff, so I'm surprised it doesn't seem to do
> it. I'm still trying, but so far I haven't read that it can't be done.
> Peter|||Thanks, Bruce. The links you provided clarified a few things, but I must
still be missing something.
In Oracle I have written the following fairly simple procedure called
SP_HFM_EPRO_NEG_NUM_REPORT:
AS
BEGIN
DECLARE
CURSOR report_cursor IS
SELECT business_unit
FROM sysadm.ps_req_hdr;
REPORT_RECORD report_cursor%rowtype;
BEGIN
OPEN report_cursor;
LOOP
FETCH report_cursor INTO report_record;
EXIT WHEN report_cursor%notfound;
END LOOP;
END;
END;
I've created a datasource that correctly points to the Oracle instance and a
dataset the contains only the name of the procedure. When I run it I get no
errors, but I get no results, either. My next step would be to populate a
table with data, but with nothing being returned I don't know how.