Free Hand SQL Report based on Procedure ???

[system]

Hi Team
I have to create a Free hand sql reports based the customized views created by DBA and on some requirement, DBA has created a Stored procedure to generate the data and stored in the table. From Business Objects point of view, i need to call the procedure and then based the data set generated by the procedure, I have to build the report.

So whenever the user runs a report with parameters, based on that the report should call stored procedure and generat the data in the table based on the inputs, and as well in the report to be executed based on the same inputs given to procedure.

Is it possible ?. If its possible can any please tell me the steps to be done.
Thanks for your time,
–Ellis

• please let me know if i’m not clear on the issue.

---

ellis27 (BOB member since 2004-06-22)

[system]

You can call stored procedures from free hand sql and pass parameters to the data base using a user defined function to start the procedure and another user defined function to retrieve the data.

These threads are useful

120×120 10.4 KB

Hi I’ve written a plsql stored procedure that populates a temporary table then queries the table and returns the data through a ref cursor. This all works fine. However, when I try to base a report on the procedure in BO, I often get the following error message “ora-08103 object no longer exists”. I know that the procedure does still exists, I can call the same procedure via other sql interfaces and it works fine. It does actually works some of the time in BO. I thought it had something to…

120×120 10.4 KB

Good answer Beck! Thats almost exactly the way we have used stored functions and procedures here. The only difference that is obvious to me is that we use TWO data providers, one (the first) contains the call to the function, which will initiate the stored procedure and pass any required parameters to it. The second simply retrieves data from the Global Temporary table populated as a result of firing off the first Data Provider. Using two data providers does guarantee the order of processing…

---

Paul Shovlar (BOB member since 2002-09-05)