View based on stored proc output 1

[TyzA]

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

 

[Dagon]

Yes, you can create a procedure which returns a table or a pipeline table. Here's a very basic example:

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.

 

[Turkbear]

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






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[TyzA]

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), .....)

 

[TyzA]

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?

select * from table(get_tab(param1, param2))

 

[Dagon]

Yes, you just create your stored function with parameters and then pass them in through the select in exactly the way you described.