| Materialized view [message #318662] |
Wed, 07 May 2008 08:48  |
|
|
Hi
"we created a materlized view with input parameter and while running it for first time it asked for input parameter and consider these input parameter while refreshing that view by default. And we want to pass new parameters at refresh time. but it is considering the privious one parameter by default."
Please suggest
|
|
|
|
|
|
|
|
| Re: Materialized view [message #318766 is a reply to message #318668] |
Wed, 07 May 2008 22:33  |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Say you create an MV with
SELECT *
FROM my_table
WHERE blort > &min_blort
The &variable is a SQL Plus feature; you are prompted for a value which is then replaced in the SQL BEFORE it is executed on the database.
So it you specified a value of 10, then the query in the MV would be saved with > 10.
If you want to parameterise the contents of the MV, you need to save the variables in a table and join that table.
CREATE TABLE mv_vars(min_blort NUMBER);
INSERT INTO mv_vars VALUES (10);
SELECT *
FROM my_table
CROSS JOIN mv_vars
WHERE my_table.blort > mv_vars.min_blort
Then when you want to change the parameters, you update the MV_VARS tables and refresh the MV.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
