Oracle Stored Procedure as datasource

I cannot get an Oracle stored procedure to work as my datasource to save my
life...
I purchased Professional SQL Server Reporting Services book from Wrox -
looking at p.115. Followed instructions exactly - but am obviously missing
something. It's not working.
In the Solution Explorer, I right-clicked "Reports" and chose "Add New
Item". Chose "Report" and named the report "Test_Parcel".
On the "Data" tab, I chose "New Dataset" from the Dataset dropdown. The
Dataset dialogue appeared.
1) I named the new dataset "rebaproc" (reba is the name of the Oracle
server),
2) chose rebauat (my existing ORACLE - not OLEdb - shared data source that
I've been using for other reports - that are built on simply queries
accessing Oracle views),
3) chose "StoredProcedure" in the Command Type dropdown, and
4) typed the name of the stored procedure - sp_Test_Parcel,
5) clicked "OK"
As soon as I click "OK", I get the error:
"Could not generate a list of fields for the query. Check the query syntax
or click Refresh Fields on the query toolbar. ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'SP_TEST_PARCEL',
ORA-06550: line 1, column 7: PL/SQL: Statement ignored."
The only two "IN" parameters are:
pnumUPCId IN NUMBER, /* UPC_ID to read
*/
pnumParcelId IN NUMBER, /* Parcel_Id to read
*/
When I click "Refresh Fields", RS pops up a dialog (the "Define Query
Parameters" dialog) which asks for parameter values for the pnumUPCId and
pnumParcelId. I enter values, and click OK, and get the same error
referenced above.
Not at all sure what I'm supposed to be doing... I even tried manually
adding "Report Parameters", but same issue...
Please help.
ThanksI had this same exact issue and finally got it to work. When you hit refresh
fields and it pops up with your two parameters try selecting...I think it is
the NULL at the top of the dropdown instead of actually typing in values.
Once you do that it will finally create your parameters in your report from
your stored procedure. I read the same book you did and followed
instructions on the internet just like the book and had the same results you
did - I too am using oracle. Once it created the parameters in the report it
got rid of the parameter error. If you can't get it to create them the first
time of doing this try it again...it seems to be a bit crazy and one time
took me 3 times of selecting the null to get it to work. Good Luck ;-)
Melissa
"Derek in Richmond" wrote:
> I cannot get an Oracle stored procedure to work as my datasource to save my
> life...
> I purchased Professional SQL Server Reporting Services book from Wrox -
> looking at p.115. Followed instructions exactly - but am obviously missing
> something. It's not working.
> In the Solution Explorer, I right-clicked "Reports" and chose "Add New
> Item". Chose "Report" and named the report "Test_Parcel".
> On the "Data" tab, I chose "New Dataset" from the Dataset dropdown. The
> Dataset dialogue appeared.
> 1) I named the new dataset "rebaproc" (reba is the name of the Oracle
> server),
> 2) chose rebauat (my existing ORACLE - not OLEdb - shared data source that
> I've been using for other reports - that are built on simply queries
> accessing Oracle views),
> 3) chose "StoredProcedure" in the Command Type dropdown, and
> 4) typed the name of the stored procedure - sp_Test_Parcel,
> 5) clicked "OK"
> As soon as I click "OK", I get the error:
> "Could not generate a list of fields for the query. Check the query syntax
> or click Refresh Fields on the query toolbar. ORA-06550: line 1, column 7:
> PLS-00306: wrong number or types of arguments in call to 'SP_TEST_PARCEL',
> ORA-06550: line 1, column 7: PL/SQL: Statement ignored."
> The only two "IN" parameters are:
> pnumUPCId IN NUMBER, /* UPC_ID to read
> */
> pnumParcelId IN NUMBER, /* Parcel_Id to read
> */
> When I click "Refresh Fields", RS pops up a dialog (the "Define Query
> Parameters" dialog) which asks for parameter values for the pnumUPCId and
> pnumParcelId. I enter values, and click OK, and get the same error
> referenced above.
> Not at all sure what I'm supposed to be doing... I even tried manually
> adding "Report Parameters", but same issue...
> Please help.
> Thanks
>|||Thanks for responding Melissa. I tried what you suggested, but it still
doesn't seem to work.
---
"Melissa" wrote:
> I had this same exact issue and finally got it to work. When you hit refresh
> fields and it pops up with your two parameters try selecting...I think it is
> the NULL at the top of the dropdown instead of actually typing in values.
> Once you do that it will finally create your parameters in your report from
> your stored procedure. I read the same book you did and followed
> instructions on the internet just like the book and had the same results you
> did - I too am using oracle. Once it created the parameters in the report it
> got rid of the parameter error. If you can't get it to create them the first
> time of doing this try it again...it seems to be a bit crazy and one time
> took me 3 times of selecting the null to get it to work. Good Luck ;-)
> Melissa
> "Derek in Richmond" wrote:
> > I cannot get an Oracle stored procedure to work as my datasource to save my
> > life...
> >
> > I purchased Professional SQL Server Reporting Services book from Wrox -
> > looking at p.115. Followed instructions exactly - but am obviously missing
> > something. It's not working.
> >
> > In the Solution Explorer, I right-clicked "Reports" and chose "Add New
> > Item". Chose "Report" and named the report "Test_Parcel".
> >
> > On the "Data" tab, I chose "New Dataset" from the Dataset dropdown. The
> > Dataset dialogue appeared.
> >
> > 1) I named the new dataset "rebaproc" (reba is the name of the Oracle
> > server),
> > 2) chose rebauat (my existing ORACLE - not OLEdb - shared data source that
> > I've been using for other reports - that are built on simply queries
> > accessing Oracle views),
> > 3) chose "StoredProcedure" in the Command Type dropdown, and
> > 4) typed the name of the stored procedure - sp_Test_Parcel,
> > 5) clicked "OK"
> >
> > As soon as I click "OK", I get the error:
> > "Could not generate a list of fields for the query. Check the query syntax
> > or click Refresh Fields on the query toolbar. ORA-06550: line 1, column 7:
> > PLS-00306: wrong number or types of arguments in call to 'SP_TEST_PARCEL',
> > ORA-06550: line 1, column 7: PL/SQL: Statement ignored."
> >
> > The only two "IN" parameters are:
> > pnumUPCId IN NUMBER, /* UPC_ID to read
> > */
> > pnumParcelId IN NUMBER, /* Parcel_Id to read
> > */
> >
> > When I click "Refresh Fields", RS pops up a dialog (the "Define Query
> > Parameters" dialog) which asks for parameter values for the pnumUPCId and
> > pnumParcelId. I enter values, and click OK, and get the same error
> > referenced above.
> >
> > Not at all sure what I'm supposed to be doing... I even tried manually
> > adding "Report Parameters", but same issue...
> >
> > Please help.
> >
> > Thanks
> >|||Sorry I wasn't any help. I am using the oracle OLE DB driver (not MS driver
for Oracle) so I guess that might have something to do with it...you
mentioned you are not. Here are my steps I went through which sound very
similar to yours:
1. Add existing datasource to project
2. Create New Report
3. New Dataset Name is test
4. Datasource hdbprod (same datasource I use in other reports)
5. Command Type StoredProcedure
6. Query String MAKE_ADDRESS_30DAY_TABLE
7. OK
8. OK on scary parameter error
9. Hit refresh fields button
10. Select NULL on two parameters (selecting empty won't work)
11. Report -> Report Parameters to verify two parameters were created
I'll look through my notes and see if there is anything else I can find.
Maybe someone else has some more experience with it.
Melissa
"Derek in Richmond" wrote:
> Thanks for responding Melissa. I tried what you suggested, but it still
> doesn't seem to work.
> ---
> "Melissa" wrote:
> > I had this same exact issue and finally got it to work. When you hit refresh
> > fields and it pops up with your two parameters try selecting...I think it is
> > the NULL at the top of the dropdown instead of actually typing in values.
> > Once you do that it will finally create your parameters in your report from
> > your stored procedure. I read the same book you did and followed
> > instructions on the internet just like the book and had the same results you
> > did - I too am using oracle. Once it created the parameters in the report it
> > got rid of the parameter error. If you can't get it to create them the first
> > time of doing this try it again...it seems to be a bit crazy and one time
> > took me 3 times of selecting the null to get it to work. Good Luck ;-)
> > Melissa
> >
> > "Derek in Richmond" wrote:
> >
> > > I cannot get an Oracle stored procedure to work as my datasource to save my
> > > life...
> > >
> > > I purchased Professional SQL Server Reporting Services book from Wrox -
> > > looking at p.115. Followed instructions exactly - but am obviously missing
> > > something. It's not working.
> > >
> > > In the Solution Explorer, I right-clicked "Reports" and chose "Add New
> > > Item". Chose "Report" and named the report "Test_Parcel".
> > >
> > > On the "Data" tab, I chose "New Dataset" from the Dataset dropdown. The
> > > Dataset dialogue appeared.
> > >
> > > 1) I named the new dataset "rebaproc" (reba is the name of the Oracle
> > > server),
> > > 2) chose rebauat (my existing ORACLE - not OLEdb - shared data source that
> > > I've been using for other reports - that are built on simply queries
> > > accessing Oracle views),
> > > 3) chose "StoredProcedure" in the Command Type dropdown, and
> > > 4) typed the name of the stored procedure - sp_Test_Parcel,
> > > 5) clicked "OK"
> > >
> > > As soon as I click "OK", I get the error:
> > > "Could not generate a list of fields for the query. Check the query syntax
> > > or click Refresh Fields on the query toolbar. ORA-06550: line 1, column 7:
> > > PLS-00306: wrong number or types of arguments in call to 'SP_TEST_PARCEL',
> > > ORA-06550: line 1, column 7: PL/SQL: Statement ignored."
> > >
> > > The only two "IN" parameters are:
> > > pnumUPCId IN NUMBER, /* UPC_ID to read
> > > */
> > > pnumParcelId IN NUMBER, /* Parcel_Id to read
> > > */
> > >
> > > When I click "Refresh Fields", RS pops up a dialog (the "Define Query
> > > Parameters" dialog) which asks for parameter values for the pnumUPCId and
> > > pnumParcelId. I enter values, and click OK, and get the same error
> > > referenced above.
> > >
> > > Not at all sure what I'm supposed to be doing... I even tried manually
> > > adding "Report Parameters", but same issue...
> > >
> > > Please help.
> > >
> > > Thanks
> > >