Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

View based on stored proc output 1

Status
Not open for further replies.

TyzA

Programmer
Jan 7, 2002
86
BE
Hi,

Is it possible to create a view based on the output of a stored procedure? this output will be a recordset

This stored procedure will access a few tables, do some transformations on the data and this data should then be returned to Business Objects. Since Business Objects doesn't support PL/SQL, I was wondering if it is possible to create a view based on the stored proc's output? This view can then be used in Business Objects.
In the near-future, this information will be extracted via a Java app instead of Business Objects so we want to re-use this stored proc

Thanks a lot for your help,
-T
 
Yes, you can create a procedure which returns a table or a pipeline table. Here's a very basic example:

Code:
DROP TYPE TMP_TAB
/

DROP TYPE TMP_REC
/

create or replace TYPE TMP_REC IS OBJECT (DUMMY_ID INTEGER)
/

CREATE OR REPLACE TYPE TMP_TAB AS TABLE OF TMP_REC
/

CREATE OR REPLACE FUNCTION GET_TAB RETURN TMP_TAB is
v_tmp_tab   tmp_tab:= 	tmp_tab();
BEGIN
   v_tmp_tab.delete;
   v_tmp_tab.extend;
   v_tmp_tab(1) := TMP_rec(1);
   return v_tmp_tab;
END;
/

select * from table(get_tab)

More details in the Oracle manuals.
 
Hi,
Also Crystal can use the SP directly as a report data source as long as it outputs a Ref Cursor..




[profile]

To Paraphrase:"The Help you get is proportional to the Help you give.."
 
Thanks Dagon.
I think this is what I'm looking for.

I'll give it a try.

So with

create or replace TYPE TMP_REC IS OBJECT (DUMMY_ID INTEGER)

I can basically define a complete structure like?

create or replace TYPE TMP_REC IS OBJECT (DUMMY_ID INTEGER,
name varchar2(25), street varchar2(100), .....)
 
One more question:
Is it possible to provide input parameters to the function/stored procedure in question?

if yes, how does it work?
as follows?

Code:
select * from table(get_tab(param1, param2))
 
Yes, you just create your stored function with parameters and then pass them in through the select in exactly the way you described.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top