Crystal Rpts; Problems trying to call Oracle Stored Proc with In and Out Parameters

[Statey603]

I am still wrestling with implementation of a reprot that calls an Oracle Stored Procedure to update a table with the last run date and time for the report. The Stored Procedure takes a string argument (report name) and returns a string (pass/fail) because somewhere I read that Crystal Stored Proc calls were expecting something be returned.

When I attempt to add the Stored Procedure to a new report using the Design wizard, I get prompted by Crystal for the input parameter. When I click OK, I get the following error message: wrong number or types of arguments. I never get a prompt for the return parameter. I am not sure if I have to assign the Stored Procedure to a report field? I am getting very confused.

Crystal Reports 2011 version 14

thanks

 

[TeyBrady]

I'm using crytal reports 2008 SP3. I am not using an ODBC connection to Oracle. I am using a native Oracle connection. So if I create a new report I select Oracle Server as the connection type. I enter the server, userid and password. Then I drill to the user/schema, stored procedures and finally click sp_test and send it to the right side of the box and go with it from there.

I do not have the database entry in my registry. Mine is 12.0...
HKEY_CURRENT_USER\Software\Business Objects\Suite 12.0\Crystal Reports\Database

I set up three different types of ODBC drivers. The one called Microsoft ODBC for oracle did not work. I was able to get the Merant Oracle and native Oracle ODBC drivers to work.

 

[Statey603]

That was it !!! I changed from using an ODBC connection to a native connection per your instructions and no longer receive the Read Only Transaction Error. I appreciate your persistence helping me.

 

[TeyBrady]

I'm glad that worked! I try to avoid using ODBC connections with Oracle because when the report is scheduled on the server it has to have the same ODBC set up as the one the developer used. When native connections are used nothing special is needed on the server(s) other than a valid tnsnames.ora file.

 

[Statey603]

UPDATE.................
Now my report is configured using native connectivity and calling a stored procedure that performs a database update and returns a recordset [ref cursor]. Crystal no longer complains about insert/update and the database update works when I initially create the report, however, subsequent executions do not perform the update. Any ideas?
This is turning into a career.
lol

 

[TeyBrady]

I'm not sure why it is not rerunning your update. In my example where I keep adding 7 days to the date it initially ran when I first told it the stored procedure to use and crystal inspected it. Then every time I hit refresh on the report in crystal it would rerun sp_test and the update statement which would add 7 more days. My gut is telling me there is something at issue with your update.

 

[Statey603]

SOLUTION:

Use Native Connection [not ODBC]
Put Formula in main report containing Shared variable
Assign Stored Procedure Input Parameter value into Shared Variable
Add subReport with Data Source = Stored Procedure
Link subReport to main report by joining the Shared Variable to the Stored Proc Input Param