• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 356
  • Last Modified:

How to create MV by passing parameter?

Hi

Can we create a materialized view by passing parameters to it. so that we change its value dynamically.
If yes, plese provide me the eample also.

We are suing oracle 11G.

Thanks
0
titanium0203
Asked:
titanium0203
1 Solution
 
johanntagleCommented:
Poor oracle 11G if you are suing it =)

Anyway, I don't think you use materialized views that way.  They are meant to take use parameters defined upon creation - the most dynamic it can be is something that has to do with the current time or something it can compute on its own.  Just create a "regular" table then maybe populate is using a stored procedure.  Something like:

create table fake_mv_table (
  column1 varchar2(20);
  column2 number;
)

create procedure populate_fake_mv (date_start in date, date_end in date)
begin
  delete from fake_mv_table;
  insert into fake_mv_table (column1, column2)
    select something, max(something_else)
    from some_table
    where record_date between date_start and date_end;
  commit;
end

0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now