Oracle Stored Procedure Design limits with MSRS

What are the Oracle Stored Procedure Design limits with MSRS'
I have a complex SP that is using temp table in Oracle.
Frist deleting the data and doing two insert queriess from select queries to
gather and transform data and place in the temp table.
Then a final select query on the temp table to return the data in the oracle
reference cursor.
This works fine in Oracle and tools like PL/SQL Developer and Crystal Report
and Enterprise.
But in the VS.NET IDE I get nothing but an empty result set back in the MSRS
report writing.
No error messages etc...
Quite puzzling...
Does anyone know why this is happening?
Is this a design issue related to a problem iwth the way Oracle and the MS
Data Provider works'
Is there a work around?
HELP'?
ThanksWhen I tried it, I was never able to make PL/SQL package calls directly from
RS. Straight T-SQL code works fine, but calling packages never returned any
result sets for me. I tried both the MS and ORA data providers.
The workaround I used was to redirect the user to an .aspx page, then make
the package call using ADO.NET. When the package is done processing, the
page then redirects to the final report which then renders in the browser.
James
"Ronald Garlit" <RonaldGarlit@.discussions.microsoft.com> wrote in message
news:2991E4AE-CE76-4AEC-8E17-9B15E4D711DF@.microsoft.com...
> What are the Oracle Stored Procedure Design limits with MSRS'
> I have a complex SP that is using temp table in Oracle.
> Frist deleting the data and doing two insert queriess from select queries
> to
> gather and transform data and place in the temp table.
> Then a final select query on the temp table to return the data in the
> oracle
> reference cursor.
> This works fine in Oracle and tools like PL/SQL Developer and Crystal
> Report
> and Enterprise.
> But in the VS.NET IDE I get nothing but an empty result set back in the
> MSRS
> report writing.|||I don't use Oracle, so might be talking out of my arse!
But (or Butt if I am!), could you not use a query on a MS SQL server
with the Oracle server as a linked server, or use an 'openrowset' query
again on an MS SQL Server querying the Oracle server. I think I'm right
in saying that this latter option would do all the processing on the
Oracle server (and hopefully use all it's functionality) just returning
the dataset.
I have my suspicion though it all boils down to the quality of the
OLEDB/ODBC provider used. And most non-MS based systems have a vested
interest in providing poor interconnectivity with MS.
Chris
James Counihan wrote:
> When I tried it, I was never able to make PL/SQL package calls
> directly from RS. Straight T-SQL code works fine, but calling
> packages never returned any result sets for me. I tried both the MS
> and ORA data providers.
> The workaround I used was to redirect the user to an .aspx page, then
> make the package call using ADO.NET. When the package is done
> processing, the page then redirects to the final report which then
> renders in the browser.
> James
> "Ronald Garlit" <RonaldGarlit@.discussions.microsoft.com> wrote in
> message news:2991E4AE-CE76-4AEC-8E17-9B15E4D711DF@.microsoft.com...
> > What are the Oracle Stored Procedure Design limits with MSRS'
> >
> > I have a complex SP that is using temp table in Oracle.
> >
> > Frist deleting the data and doing two insert queriess from select
> > queries to
> > gather and transform data and place in the temp table.
> >
> > Then a final select query on the temp table to return the data in
> > the oracle
> > reference cursor.
> >
> > This works fine in Oracle and tools like PL/SQL Developer and
> > Crystal Report
> > and Enterprise.
> >
> > But in the VS.NET IDE I get nothing but an empty result set back in
> > the MSRS
> > report writing.|||I know that it is possible to use Oracle Stored procedures. I have not done
it myself but I have seen people work through issues and be successfull.
Have you searched the newsgroup for any posting that refers to Oracle.
One suggestion I do have is to use the generic query window, this makes sure
that you are using the managed provider. Also, start out without parameters
and get that working. I know some people had trouble with some parameters to
Oracle Stored Procedures but they got that to work eventually. That is why I
suggest starting out with no parameters and then go forward from there.
There are certain design limits you have to follow for it to work but if you
do that you should be able to do this. Temp tables should be OK.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Ronald Garlit" <RonaldGarlit@.discussions.microsoft.com> wrote in message
news:2991E4AE-CE76-4AEC-8E17-9B15E4D711DF@.microsoft.com...
> What are the Oracle Stored Procedure Design limits with MSRS'
> I have a complex SP that is using temp table in Oracle.
> Frist deleting the data and doing two insert queriess from select queries
to
> gather and transform data and place in the temp table.
> Then a final select query on the temp table to return the data in the
oracle
> reference cursor.
> This works fine in Oracle and tools like PL/SQL Developer and Crystal
Report
> and Enterprise.
> But in the VS.NET IDE I get nothing but an empty result set back in the
MSRS
> report writing.
> No error messages etc...
> Quite puzzling...
> Does anyone know why this is happening?
> Is this a design issue related to a problem iwth the way Oracle and the MS
> Data Provider works'
> Is there a work around?
> HELP'?
> Thanks
>