An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.
One of a set of tools we're offering as a way of saying thank you for being a part of the community.
Solved
Crystal Reports Developer XI and Oracle 10g Stored Procedure results
Posted on 2007-11-30
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
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
5 Comments
Active today
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
mlmcc
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.
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.
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Introduction
A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface. I made a script to download the Alert Log, look for errors, and email me the trace files. In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month10 days, 8 hours left to enroll
719 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.