Home » SQL & PL/SQL » SQL & PL/SQL » creating a view at run-time by using pl/sql procedure
creating a view at run-time by using pl/sql procedure [message #284152] Thu, 29 November 2007 01:03 Go to next message
M.Shakeel Azeem
Messages: 226
Registered: September 2006
Senior Member
Dear All,
I want to create a view at run-time like

create procedure my_proc(p_date varchar2)
is

create or replace view emp_vw
as select ename,depno
from emp,dept
where hire_date=p_date;

p_date is run-time variable which should be passed from a procedure
and now the view will be refreshed according to passed date

Regards,
M.Shakeel
Re: creating a view at run-time by using pl/sql procedure [message #284158 is a reply to message #284152] Thu, 29 November 2007 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This is really a bad way to do it and after more than 150 posts you still don't know the basic forum rules.

Use a parametrized view.

Regards
Michel
Re: creating a view at run-time by using pl/sql procedure [message #284273 is a reply to message #284152] Thu, 29 November 2007 05:04 Go to previous messageGo to next message
shwetamber.kaushik
Messages: 2
Registered: November 2007
Junior Member
hii shakeel,
i think the best possible solution for your problem would be dynamic sql.you can easily create that view within your procedure.


thanks
shwetamber
Re: creating a view at run-time by using pl/sql procedure [message #284277 is a reply to message #284273] Thu, 29 November 2007 05:14 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
@shwetamber.kaushik :
Quote:

i think the best possible solution for your problem would be dynamic sql.you can easily create that view within your procedure.



what benefit do you see by creating a view within a procedure.just refer to michel's post.

regards,
Re: creating a view at run-time by using pl/sql procedure [message #284474 is a reply to message #284277] Thu, 29 November 2007 17:13 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Remember that creating a view is DDL and so will cause a commit as soon as you create the view. Creating views on the fly is bad for scalability too (if that's a concern). If only your app needs to use the view and you have control of it, then a cursor should do the trick just as well. I'm assuming structure (table, joins etc not just the predicate) needs to change dynamically, so a parameterized view solution may not meet your requirement.

Other links of interest.
http://laurentschneider.com/wordpress/2007/04/variable-in-a-view.html
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1288401763279

Edit: After reading the original question again - Michel is right.

[Updated on: Fri, 30 November 2007 00:02]

Report message to a moderator

Re: creating a view at run-time by using pl/sql procedure [message #284488 is a reply to message #284152] Thu, 29 November 2007 21:11 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
M.Shakeel Azeem

Some people are confused. Can you please tell us what it is you really want to do. Why do you think you need to create a view dynamically in the first place?

Kevin
Re: creating a view at run-time by using pl/sql procedure [message #284521 is a reply to message #284488] Thu, 29 November 2007 23:49 Go to previous messageGo to next message
shwetamber.kaushik
Messages: 2
Registered: November 2007
Junior Member
actully you are not getting my point, the question was how one can create a view dynamically.
so i have just tried to answer that only rather then delving into it. may be his bissiness model requires that kind of process.

thank you.
Re: creating a view at run-time by using pl/sql procedure [message #284524 is a reply to message #284521] Fri, 30 November 2007 00:00 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Something like this...
v_tname := 'my_table';
v_sql := 'create or replace view V1 as select * from '||v_tname;
execute immediate v_sql;
Re: creating a view at run-time by using pl/sql procedure [message #284538 is a reply to message #284521] Fri, 30 November 2007 00:32 Go to previous message
Michel Cadot
Messages: 68711
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:

may be his bissiness model requires that kind of process.


No business need requires that, only bad implementation.
And we try to help him to avoid this.
He posted in Experts forum. Experts forum implies experts answer and not newbie one on syntax.

Regards
Michel

[Updated on: Fri, 30 November 2007 00:33]

Report message to a moderator

Previous Topic: Regarding Aggregate conditions ..
Next Topic: 3rd maximum salary of an employee query
Goto Forum:
  


Current Time: Sat Nov 09 13:14:39 CST 2024