Solved

Crystal Reports Developer XI and Oracle 10g Stored Procedure results

Posted on 2007-11-30
5
1,957 Views
Last Modified: 2013-12-18
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
0
Comment
Question by:khughes1101
  • 3
5 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 20385268
I don't use Oracle but from what I have read here, the ref cursor must be the first parameter in the stored procedure.

mlmcc
0
 

Author Comment

by:khughes1101
ID: 20385307
I've tried it as the first and last parameter.  No difference in the resulting error message.   Any other ideas I might try?

I have been able to put together a package with a stored procedure with just a straight select on a "regular" table - not a global temporary table and report on that fine.   I'm not sure if the difference is between a temporary table verses a standard table or the fact that I'm running the stored procedure prior to accessing the temporary table.

I'm also able to report against the temporary table, but as you would expect I do not have data in it.   At least the report runs without errors.
0
 

Accepted Solution

by:
khughes1101 earned 0 total points
ID: 20385360
I believe I determined the problem.   I had to change the table definition to include "ON COMMIT PRESERVE ROWS".   Originally it was "ON COMMIT DELETE ROWS".   In the test I ran this resolved the problem.  I will try it out and make sure it works.

Thank you for all of your help.
0
 

Author Comment

by:khughes1101
ID: 20385500
The change worked.

Can I request here that this question be closed as I have resolved my own question?

Thank you.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
exp/imp 25 87
Can't Access My Database 57 77
Sum not calculating correctly 22 40
Count If in Crystal Reports 2 19
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question