RE: Parameterized Views

From: "Koppelaars, Toon" <T.Koppelaars@xxxxxxxxxxxxxxxxxxxx>
To: <mhyder@xxxxxxxx>
Date: Wed, 19 Sep 2007 20:14:40 +0200
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]Namens Mir M. Mirhashimali
Verzonden: woensdag 19 september 2007 19:38
Aan: Alvaro Jose Fernandez
CC: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] En 
> nombre de Mir M. Mirhashimali
> Enviado el: miércoles, 19 de septiembre de 2007 18:47
> Para: oracle-l@xxxxxxxxxxxxx
> Asunto: Parameterized Views
>
> I was wondering if it is possible to parameterizes a view. did some 
> googling but did not find any satisfactory results.
>
>   
--
//www.freelists.org/webpage/oracle-l



--
//www.freelists.org/webpage/oracle-l
RE: Parameterized Views

Other related posts:
