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)
Oracle does not support parameters with Views, but we can always find a workaround. In the case of parameterized views there could be various workarounds. In this post I am giving a neat and simple example to create a parameterized view, it is my personal favorite workaround. I am using SCOTT schema for this example.
A table-valued function in SQL Server is a user-defined function that accepts zero or more parameters and returns a table variable. Moreover, it also allows users to query the result of the function. On the other hand, a view is just a SQL statement with a name.
Table functions are functions that produce a collection of rows (either a nested table or a varray) that can be queried like a physical database table. You use a table function like the name of a database table, in the FROM clause of a query. A table function can take a collection of rows as input.
With collections and the table() function, a function can return a table that can be queried in an SQL statement.
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));
It is possible to define a kind of "parametrized" views in Oracle. The steps are:
To use this mechanism one user should:
SELECT
data from the view,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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With