CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
user881703
user881703

Reputation: 1199

How to create a parameterized Materialized View in PL-SQL

I need to pass month(string) with every manual MV(Materalised View) refresh action for the view below.

if that is possible please let me know how I can achieve this? If this is not possible, do I have any other options?

CREATE MATERIALIZED VIEW my_warranty_orders
   AS SELECT w.order_id, w.line_item_id, o.order_date
   FROM warranty_orders w, orders o
   WHERE o.order_id = o.order_id
   AND o.sales_rep_id = 165
   AND O.order_month = p_argument --pass the month
   ;

Upvotes: 2

Views: 8658

Answers (2)

Praneeth Gudumasu
Praneeth Gudumasu

Reputation: 177

How about using dbms_application_info.set_client_info or context variable value as the parameter to the mview and reset the context/client info every time the mview is going to be refreshed

Upvotes: 1

MT0
MT0

Reputation: 167822

Either use a constant value and re-create the materialized view each time with a different constant rather than refreshing it:

CREATE OR REPLACE MATERIALIZED VIEW my_warranty_orders
   AS SELECT w.order_id, w.line_item_id, o.order_date
      FROM   warranty_orders w
             INNER JOIN orders o
             ON ( o.order_id = o.order_id )
      WHERE  o.sales_rep_id = 165
      AND    o.order_month  = DATE '2016-06-01';

or create another table and join that into the materialized view:

CREATE TABLE my_warranty_orders_month (
  month DATE PRIMARY KEY
);

INSERT INTO my_warranty_orders_month VALUES ( DATE '2016-06-01' );

CREATE MATERIALIZED VIEW my_warranty_orders
   AS SELECT w.order_id, w.line_item_id, o.order_date
      FROM   warranty_orders w
             INNER JOIN orders o
             ON ( o.order_id = o.order_id )
             INNER JOIN my_warranty_orders_month m
             ON ( o.order_month = m.month )
      WHERE  o.sales_rep_id = 165;

then when you want to change it:

UPDATE my_warranty_orders_month
SET month = DATE '2016-07-01';

and refresh the materialized view.

Upvotes: 3

Related Questions