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

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
zmouserAsked:
Who is Participating?
 
Walter RitzelSenior Software EngineerCommented:
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
 
Walter RitzelSenior Software EngineerCommented:
Here is the link:
http://psoug.org/reference/pipelined.html

I have tested the examples, they work perfectly.
0
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
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
 
mlmccCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.