I'm very new to Oracle so this may be a very simple issue. I've used SQL Server and Crystal extensively so I have that "handicap". I have a package with a stored procedure that does a lot of work and stores the results to a global temporary table. I then need to select that information from the table and return it to Crystal to report.
Here is what I've tried:
I have added a SYS_REFCURSOR as an output parameter to the stored procedure and at the bottom of the stored procedure I have opened the cursor with the select of the global table.
I connect the stored procedure to crystal using a JDBC connection. When I do this, everything looks as I would expect it within Crystal. I see the field names and all expected field information. The problem comes when I try to run the report. I receive the error "Failed to retrieve data from the database". I have granted execute to public even though I'm connecting as the database user.
I have also tried creating a new package that calls the original stored procedure and then calls the select and returns the cursor. Kind of a wrapper stored procedure. However, I receive the same error message with that.
I'm sure there is something I'm missing that is pretty straight forward. All help would be greatly appreciated.
Thank you
khughes1101
Thank you for all of your help.