Table-Valued Functions in ORACLE 11g ? ( parameter

Question

I've seen discussions about this in the past, such as here. But I'm wondering if somewhere along the line, maybe 10g or 11g (we are using 11g), ORACLE has introduced any better support for "parameterized views", without needing to litter the database with all sorts of user-defined types and/or cursor definitions or sys_context variables all over.

I'm hoping maybe ORACLE's added support for something that simply "just works", as per the following example in T-SQL:

CREATE FUNCTION [dbo].[getSomeData] (@PRODID ROWID)  
RETURNS TABLE AS  
    RETURN SELECT PRODID, A, B, C, D, E  
    FROM MY_TABLE  
    WHERE PRODID = @PRODID

Then just selecting it as so:

SELECT * FROM dbo.getSomeData(23)

Answer 1

There are TWO types of table-valued functions in SQL SERVER:

1. Inline table-valued function: For an inline table-valued function, there is no function body; the table is the result set of a single SELECT statement. This type can be named as 'parameterized view' and it has no equivalent in ORACLE as I know.

2. Multistatement table-valued function: For a multistatement table-valued function, the function body, defined in a BEGIN...END block, contains a series of Transact-SQL statements that build and insert rows into the table that will be returned.

The above sample (By Gary Myers) creates a table function of the second type and it is NOT a 'parameterized view'.

Answer 2

No need for SYS_CONTEXT or cursor definitions. You do need a type so that, when the SQL is parsed, it can determine which columns are going to be returned. That said, you can easily write a script that will generate type and collection type definitions for one or more tables based on the data in user_tab_columns.

The closest is

create table my_table
(prodid number, a varchar2(1), b varchar2(1), 
  c varchar2(1), d varchar2(1), e varchar2(1));

create type my_tab_type is object
(prodid number, a varchar2(1), b varchar2(1), 
  c varchar2(1), d varchar2(1), e varchar2(1))
.
/

create type my_tab_type_coll is table of my_tab_type;
/

create or replace function get_some_data (p_val in number) 
return my_tab_type_coll pipelined is
begin
  FOR i in (select * from my_table where prodid=p_val) loop
    pipe row(my_tab_type(i.prodid,i.a,i.b,i.c,i.d,i.e));
  end loop;
  return;
end;
/

SELECT * FROM table(get_Some_Data(3));

Answer 3

It is possible to define a kind of "parametrized" views in Oracle. The steps are:

1. Define a package containing as public members that are in fact the needed parameters (there is no need for functions or procedures in that package),
2. Define a view that is based on that package members.

To use this mechanism one user should:

1. open a session,
2. assign the desired values to that package members,
3. SELECT data from the view,
4. do other stuff or close the session.

REMARK: it is essential for the user to do all the three steps in only one session as the package members scope is exactly a session.