OraFAQ Forum: SQL & PL/SQL » Want to create View with parameter

Home » SQL & PL/SQL » SQL & PL/SQL » Want to create View with parameter (Oracle 11G)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Want to create View with parameter [message #623004]

Wed, 03 September 2014 05:23

hemant_khandare
Messages: 72
Registered: November 2011

Member

Hi Experts,

I want to create view with accepting parameter. ie dynamic view.

eg. Create view v1 as
select * from emp e where e.deptno=var;

I want pass var inside view so that my view will become dynamic.

Can we create such view???

please help

Thanks in Advance...........

Report message to a moderator

Re: Want to create View with parameter [message #623005 is a reply to message #623004]

Wed, 03 September 2014 05:25

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Oh no, again this one, we have this question every week now, please do search before posting.

Report message to a moderator

Re: Want to create View with parameter [message #623031 is a reply to message #623004]

Wed, 03 September 2014 08:51

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

hemant_khandare wrote on Wed, 03 September 2014 06:23

I want to create view with accepting parameter. ie dynamic view.

You can use context variable or you can use pipelined function instead of the view.

SY.

Report message to a moderator

Re: Want to create View with parameter [message #623061 is a reply to message #623031]

Wed, 03 September 2014 16:02

Bill B
Messages: 1971
Registered: December 2004

Senior Member

simply use var in your where clause

Create view v1 as
select col1,col2,deptno from emp e;

select col1,col2 from v1 where deptno = var;

Report message to a moderator

Re: Want to create View with parameter [message #623147 is a reply to message #623061]

Fri, 05 September 2014 03:59

hemant_khandare
Messages: 72
Registered: November 2011

Member

Can you please post some example using syscontext and pipeline ??

Report message to a moderator

Re: Want to create View with parameter [message #623183 is a reply to message #623147]

Fri, 05 September 2014 10:28

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Can you please try to search for this FAQ???

Report message to a moderator

Previous Topic:	Round values to 0,5,10,100 ..
Next Topic:	Insertion logic

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 22:08:52 CDT 2024