Oracle Stored Procedure flakiness...

I'm on a deadline so any help would be much appreciated.
I am building a Reporting Services report that takes a number of
parameters with which to call a stored procedure. I am using the
Microsoft OLEDB provider for Oracle. (For some reason, the Oracle OLEDB
provider that I have manually installed does not show up as a choice,
but that's for another day.)
Ultimately, this report will be rendered from an ASP.NET front-end Web
application. But in order to do the formatting etc. I had it working in
MSRS's HTML Viewer. Thing were fine. I had about 15 parameters working
for a while. A mixture of dates, floats, varchar2s and numbers.
Now, I'm not sure exactly what happened, but to the best of my
recollection:
When calling the report programmtically, it was trying to do a query
for the first parameter and was failing. I didn't need it to do the
query any longer (the front end was handling that). I modified the
report parameters such that it no longer used a Query.
Now I get "PLS-00306 Wrong number or types of arguments..." when I
attempt to render the report either in MSRS HTML Viewer or through my
ASP.NET application. I've tried narrowing it down and it seems when I
add a third parameter, it fails.
I'm sorry for the long post and lack of details, but has anyone else
experienced flakiness with Report Parameters, Stored Procedures and/or
Oracle?
Regards,
PeterMy guess here is that there is a mismatch between your query parameters and
report parameters. They are two different things but it isn't obvious since
RS creates the report parameters for you. Make sure the report work from
Report Manager.
Another possibility is that the report parameter is a string and your stored
procedure is an integer or something like that. When you add a parameter it
defaults to string. Another area to check.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"psparago" <psparago@.yahoo.com> wrote in message
news:1107367305.890651.99870@.o13g2000cwo.googlegroups.com...
> I'm on a deadline so any help would be much appreciated.
> I am building a Reporting Services report that takes a number of
> parameters with which to call a stored procedure. I am using the
> Microsoft OLEDB provider for Oracle. (For some reason, the Oracle OLEDB
> provider that I have manually installed does not show up as a choice,
> but that's for another day.)
> Ultimately, this report will be rendered from an ASP.NET front-end Web
> application. But in order to do the formatting etc. I had it working in
> MSRS's HTML Viewer. Thing were fine. I had about 15 parameters working
> for a while. A mixture of dates, floats, varchar2s and numbers.
> Now, I'm not sure exactly what happened, but to the best of my
> recollection:
> When calling the report programmtically, it was trying to do a query
> for the first parameter and was failing. I didn't need it to do the
> query any longer (the front end was handling that). I modified the
> report parameters such that it no longer used a Query.
> Now I get "PLS-00306 Wrong number or types of arguments..." when I
> attempt to render the report either in MSRS HTML Viewer or through my
> ASP.NET application. I've tried narrowing it down and it seems when I
> add a third parameter, it fails.
> I'm sorry for the long post and lack of details, but has anyone else
> experienced flakiness with Report Parameters, Stored Procedures and/or
> Oracle?
> Regards,
> Peter
>|||Thanks for the quick reply Bruce.
I wish that were the case :-). I've dumbed it down so I'm passing a
VARCHAR2 and two NUMBERS.
Is there any way to see the command generated by MSRS to be sent to
Oracle? That would really help.
I am in class today, more details later. Gotta run.|||I have had some issues with SP going to Sybase. What helped me was to use
the generic query (there is a button to switch to that). Think of this as
passthrough. Put it in the format that you would if you were using a query
tool from Oracle. For instance I put in this for Sybase:
pr_test ?,?,?
Be sure to map it to the report parameters by clicking on the ... and going
to the parameter tab. I sometimes have to put in the ? on the left column
and then put in expression and then select the report parameter.
I'm not sure if ? is the placeholder for the parameter when doing this with
Oracle, it might not be. When you are in the generic query tab you are using
the managed provider for Oracle.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"psparago" <psparago@.yahoo.com> wrote in message
news:1107372894.354513.125000@.o13g2000cwo.googlegroups.com...
> Thanks for the quick reply Bruce.
> I wish that were the case :-). I've dumbed it down so I'm passing a
> VARCHAR2 and two NUMBERS.
> Is there any way to see the command generated by MSRS to be sent to
> Oracle? That would really help.
> I am in class today, more details later. Gotta run.
>|||It won't work through the OleDB provider at design time. 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.
BTW: it is possible to switch from the managed Oracle to the OleDB provider
once the entire report is designed and done. E.g. after you published the
report to the production environment you can switch to the OleDB provider
using the steps discussed in detail in this related newsgroup thread:
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs/browse_thread/thread/782c9647e34cb1f5/8b45a452801e7b77?q=Oracle+REF+CURSORS&_done=%2Fgroup%2Fmicrosoft.public.sqlserver.reportingsvcs%2Fsearch%3Fgroup%3Dmicrosoft.public.sqlserver.reportingsvcs%26q%3DOracle+REF+CURSORS%26qt_g%3D1%26searchnow%3DSearch+this+group%26&_doneTitle=Back+to+Search&&d#8b45a452801e7b77
--
Robert M. Bruckner
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"psparago" <psparago@.yahoo.com> wrote in message
news:1107367305.890651.99870@.o13g2000cwo.googlegroups.com...
> I'm on a deadline so any help would be much appreciated.
> I am building a Reporting Services report that takes a number of
> parameters with which to call a stored procedure. I am using the
> Microsoft OLEDB provider for Oracle. (For some reason, the Oracle OLEDB
> provider that I have manually installed does not show up as a choice,
> but that's for another day.)
> Ultimately, this report will be rendered from an ASP.NET front-end Web
> application. But in order to do the formatting etc. I had it working in
> MSRS's HTML Viewer. Thing were fine. I had about 15 parameters working
> for a while. A mixture of dates, floats, varchar2s and numbers.
> Now, I'm not sure exactly what happened, but to the best of my
> recollection:
> When calling the report programmtically, it was trying to do a query
> for the first parameter and was failing. I didn't need it to do the
> query any longer (the front end was handling that). I modified the
> report parameters such that it no longer used a Query.
> Now I get "PLS-00306 Wrong number or types of arguments..." when I
> attempt to render the report either in MSRS HTML Viewer or through my
> ASP.NET application. I've tried narrowing it down and it seems when I
> add a third parameter, it fails.
> I'm sorry for the long post and lack of details, but has anyone else
> experienced flakiness with Report Parameters, Stored Procedures and/or
> Oracle?
> Regards,
> Peter
>|||Thank you for the response Robert.
My stored procedure is being called when I run the Dataset that
references it from the Data pane in the Designer. It's when the stored
procedure is called from the preview pane that I get an error.
Since posting this issue, I've worked around it (somewhat) by passing
my stored procedure only one parameter: an Xml Document containing the
actual 20 or so parameters. I was getting tired of rebuilding the
parameter list (which occasionally fixed the problem).
However, please see my more recent post concerning HTML output caching.
I'm wondering if my single ~1500 character parameter is causing issues
with MSRS determining when to return cached versions of the HTML output
(even though the parameter string differes).
Once again thank you for your help. My company is evaluating moving
from an expensive and really buggy high-end reporting system to MSRS. I
am doing the proof-of-concept work and the HTML issue is causing some
concern about adopting MSRS. I'd really love to whole-heartedly endorse
it! :-)
peter|||Here is an idea. Have a parameter table that you fill with the 20 parameters
and then pass that primary key as the single parameter to RS. The parameter
will definitely be different and your caching problem will go away.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"psparago" <psparago@.yahoo.com> wrote in message
news:1107784198.997079.73660@.c13g2000cwb.googlegroups.com...
> Thank you for the response Robert.
> My stored procedure is being called when I run the Dataset that
> references it from the Data pane in the Designer. It's when the stored
> procedure is called from the preview pane that I get an error.
> Since posting this issue, I've worked around it (somewhat) by passing
> my stored procedure only one parameter: an Xml Document containing the
> actual 20 or so parameters. I was getting tired of rebuilding the
> parameter list (which occasionally fixed the problem).
> However, please see my more recent post concerning HTML output caching.
> I'm wondering if my single ~1500 character parameter is causing issues
> with MSRS determining when to return cached versions of the HTML output
> (even though the parameter string differes).
> Once again thank you for your help. My company is evaluating moving
> from an expensive and really buggy high-end reporting system to MSRS. I
> am doing the proof-of-concept work and the HTML issue is causing some
> concern about adopting MSRS. I'd really love to whole-heartedly endorse
> it! :-)
> peter
>|||Thank you Bruce, I might just give that a try. I am still hoping for a
MSFT solution though.|||My feeling is that this will improve in the next version. I just got an
email from Oracle about additional Oracle design tools for VS beta 2. So it
looks like the next version of VS will have better support for Oracle from
Oracle. Plus MS had to work around some tools limitations for this release.
The design tools were designed around oledb, not dotnet. I expect this to
improve dramatically with the next version (hopefully late summer).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"psparago" <psparago@.yahoo.com> wrote in message
news:1107800057.304345.89930@.z14g2000cwz.googlegroups.com...
> Thank you Bruce, I might just give that a try. I am still hoping for a
> MSFT solution though.
>