How to create MV by passing parameter?

Posted on 2011-04-22
Last Modified: 2012-06-21

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.

Question by:titanium0203
    1 Comment
    LVL 24

    Accepted Solution

    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)
      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;


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Suggested Solutions

    Title # Comments Views Activity
    Oracle date format issue 36 80
    Query with "like" last 7 digits 3 71
    Update a field datetime 3 30
    import data 5 47
    Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
    This post first appeared at Oracleinaction  ( Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
    This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
    This video shows how to recover a database from a user managed backup

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now