Home » SQL & PL/SQL » SQL & PL/SQL » Materialized view (oracle 10G)
Materialized view [message #318662] Wed, 07 May 2008 08:48 Go to next message
Messages: 35
Registered: January 2008
Location: INDIA


"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 #318667 is a reply to message #318662] Wed, 07 May 2008 09:02 Go to previous messageGo to next message
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
If it is not much effort, could you post how you created that materialized view?
Considering the information you provided I fear you just have to drop and recreate the MV. But that's my guess based upon your post.
Re: Materialized view [message #318668 is a reply to message #318667] Wed, 07 May 2008 09:03 Go to previous messageGo to next message
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I suspect that will be the case too.
Re: Materialized view [message #318766 is a reply to message #318668] Wed, 07 May 2008 22:33 Go to previous message
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Say you create an MV with
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);

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
Previous Topic: Loading data into excel sheet
Next Topic: ORA-22950: cannot ORDER objects without MAP or ORDER method
Goto Forum:

Current Time: Tue Oct 25 00:36:26 CDT 2016

Total time taken to generate the page: 0.06407 seconds