Skip to Content
0
Former Member
Aug 12, 2016 at 02:55 PM

Crystal Reports XI Oracle Stored Procedure not running

308 Views

Hello,

We are using Crystal Reports XI latest version and service pack were verified by SAP Chat. Purchasing a newer version is not an option at this time.

We have a couple of reports that are connected to Oracle 11g release 11.2.0.3.0 - 64 bit, using SQL Developer 4.0.1.14 Build MAIN-14.48, and SAP Crystal Reports Runtime Engine for >net Framework (32-bit) version 13.0.13.1597. We are connecting using Oracle Provider for OLEDB.

When we run the Crystal Report from Application or Crystal Reports Designer, it is not calling the stored procedure. Again, the stored procedure is very complex and it calls around 10 other stored procedures.

We have found that we can't use a Ref Cursor in the stored procedure under Oracle Provider for OLEDB, and the application is setup for Oracle Provider for OLEDB only (We don't have control over this option).

To overcome the Ref Cursor, the data is generated into a view with an id. We setup the Crystal Report to call the stored procedure in the Main report, passing in the needed parameter, and a subreport in the report footer that uses the parameters as a link and uses the view we created to return the data.

If we manually run the stored procedure in SQL Developer, it will generate the view and if we run it from the application or Crystal Report Designer using the ID we see the data.

We can tell the stored procedure is not running because one of the first things done in the logic is insert data, based on the ID generated by the application which is also used as the parameter, into one of the tables. After running the Crystal Report from application or Crystal Reports designer, we don't see any data added to the initial table which suggests that the stored procedure is not getting ran from the report. Have tried running the stored procedure as stand alone and through a package.

Looking for suggestions on what to check, or an alternate way to retrieve the data; maybe through the command in the Database Expert using a PL/SQL statement but not sure how to set one up correctly. I've managed to get some commands built. My thought, for the Command, was to try calling the stored procedure first passing in the parameter, then call a Ref Cursor in the Command (tried Select but it wanted an INTO added).

I did come across one person that said he had ran a PL/SQL from the Command line but it had to be entered as all one line. I've attached my command line as reference.

Please remember, I have to work with the object I mentioned at the beginning, I have no control over what is used, only the Oracle and Crystal Reports development are in my control.

Thank you,

Bruce Edgar

Bruce.Edgar@fisglobal.com

Attachments

Command.txt.zip (340 B)