Stored Procedure; can not verify db, but can SET db successfully.

[Former Member]

I have a report that is referring to a stored procedure.

When I run the report, it's fine.

When I do a set datasource location, and set the DB location to the same stored procedure, or to an identical stored procedure, it's fine.

When I attempt to verify database, and pass identical parameters as in the set datasource location, it fails with this error message:

Database Connector Error: 'ORA-24338: statement handle not executed [database vendor code: 24338 ]

I can run the report in Crystal, because I don't need to verify DB. However, when I put the report into our application that uses the HTML viewer, I get the DB connection error message (presumably because we are updating/verifying the DB connection before running the report)

If I create a new report, and use the stored procedure as the datasource, it's fine... until I verify database, (same params) then it fails with the error message.

Oddly enough, it worked earlier, and the only change that I made was to the report file, and not to the stored procedure.

The stored procedure runs with identical parameters in oracle without a problem.

Thoughts? I can't even start my report over because it fails on verify.

[Former Member]

Figured out what the problem was, with some help from support...

I had changed the stored procedure to accept new params. When I set the datasource location the params appended to the bottom of my param list.

When I set location, because Crystal was pulling the param list from the stored procedure, it was asking for them in the correct order.

When I did a verify database, it passed them in the order in which they appeared in the report, which was incorrect.

Therefore, incorrect values were being passed to my parameter, and the error was the result.

solution was to right click on the param list, and select re-order parameters, and ensure that the param list in CR matches the param list in the stored procedure.

[Former Member]

I can change locations without a problem.

I can even set the location to the location it is currently pointing to.

I can even run the report...

but using the 'verify database' operation fails.

[Former Member]

Hi Ryan,

As a test, have you tried creating a new report? Does that new report work?

Some additional questions for you: -

1. What type of connectivity is being used?

2. What was the change made to the report?

3. What is the version of Oracle client on your machine?

Once we have this information, we will be able to suggest you some troubleshooting steps.

Warm regards,

Abhishek.

[former_member292966]

Hi Ryan,

Is your application successfully logging the report onto the proper Oracle Server? The account you logon to Oracle with, does it have permissions to run the Stored Procedure?

These are the common reasons for not being able to change locations.

Good luck,

Brian