RE: Parameterized Views

Mir,

You implement this functionality with the aid of a pipelined table function.

Basically you create a pipelined table function with one IN parameter that accepts your 'SOMEUSER' value. Say we call this function f_my_function(p_user in varchar2).

You can then write SQL like this:

SELECT delegate_userid,orgid
from TABLE(f_my_function('SOMEUSER'))
/

Inside this function you have a cursor as follows.

select delegate_userid, orgid
  from table1
 where loginid = p_user
union
select delegate_userid, orgid
  from table2
 where loginid = p_user
union
select delegate_userid, orgid
  from table3
 where loginid = p_user

Where p_user is the IN parameter name of the function that accepts the 'SOMEUSER' value. You fetch the rows from this cursor inside f_my_function and use PIPE ROW to give back the rows fetched.

Check the manuals (or use Google) to find out how exactly to create pipelined table functions (you have to create two auxiliary TYPE's too).

Toon

-----Oorspronkelijk bericht-----
Van: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]Namens Mir M. Mirhashimali Verzonden: woensdag 19 september 2007 19:38 Aan: Alvaro Jose Fernandez
CC: oracle-l_at_freelists.org
Onderwerp: Re: Parameterized Views

Hi Alvaro,

I mean i have a SQL statement something like this

select delegate_userid, orgid
  from table1
 where loginid = 'SOMEUSER'
union
select delegate_userid, orgid
  from table2
 where loginid = 'SOMEUSER'
union
select delegate_userid, orgid
  from table3
 where loginid = 'SOMEUSER'

I want to create the above sql as a view and want to pass the 'SOMEUSER' as a parameter to the view.

Thanks

-- 
Mir M. Mirhashimali
Oracle Systems Manager
Database Architecture, Enterprise Applications
Rice University
(713) 348 6365



Alvaro Jose Fernandez wrote:


> Hello Mir,

>

> I don't understand exactly the meaning of a parametrized view. Could you elaborate more on this?

>

> regards

> alvaro

>

> -----Mensaje original-----

> De: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] En nombre de Mir M. Mirhashimali

> Enviado el: miércoles, 19 de septiembre de 2007 18:47

> Para: oracle-l_at_freelists.org

> Asunto: Parameterized Views

>

> I was wondering if it is possible to parameterizes a view. did some 

> googling but did not find any satisfactory results.

>

>   

--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l