?
Solved

Is there a way to return data from a stored procedure in BusinessObjects XI R2

Posted on 2011-04-20
5
Medium Priority
?
674 Views
Last Modified: 2013-11-15
I have an existing stored procedure that was a part of a Crystal Report.  I would like to move this to a WebI report, which does not appear to support stored procedures out of the box.  I am using an Oracle database, which seems to have some limitations when it comes to views, namely, that you cannot perform DML operations from select statements.  That all means that, 1) in order to create my WebI report, I have to have a view (or table) to select from, and 2) I cannot use data manipulation operations to do this, because using a view would require those to be executed in a select statement.

My question is, what are my options?  It seems that, with the complexity of a data-warehouse, it's would be difficult to access and manipulate all data in cursors and views.  Is there something I am missing, and WebI can execute stored procedures?

Thanks a lot for the advice, I'm new to BusinessObjects, and could very well be missing something very obvious.

--Zach
0
Comment
Question by:zmouser
  • 2
4 Comments
 
LVL 16

Accepted Solution

by:
Walter Ritzel earned 252 total points
ID: 35437816
The best approach would be transform the procedure in a function that returns a table.
Then, you can create a view in the database that will call the function, pretty much like this:

create or replace view V1_Function as select * from table(function_name());

Then, the view can be mapped on BO Universe.

Let me look on some documentation regarding the function returning a table.
0
 
LVL 16

Expert Comment

by:Walter Ritzel
ID: 35437866
Here is the link:
http://psoug.org/reference/pipelined.html

I have tested the examples, they work perfectly.
0
 
LVL 26

Assisted Solution

by:Kurt Reinhardt
Kurt Reinhardt earned 248 total points
ID: 35516951
In general, the answer to your question is no - you cannot explicitly use a Stored Procedure as a data source for WebI.  WebI reports use Universes. That being said, wpcortex provided some great technical information.  You'll need to take the procedure and manipulate it in such a way that you can reference it's output from within your Universe.

~Kurt
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 35892737
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup
Suggested Courses

850 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