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.