SQL & PL/SQL

No.

What is MV? It is not a stored SQL like a normal view. In MV the data itself is stored. So it does not makes sence to me when you say MV with parameters.

Can you clarify? What are you trying to achieve?

One thought that comes to mind is, that you could have a separate table holding your "parameter values", and use that table in your MV defining query. Then whenever you change parameters (in that separate table), the MV would become stale and would require refreshing.

May be......then what will i have to do to change the parameters of MV through table.
can you explain with an example.



Regards
ND

Well suppose you would like to have an MV that has all the employees of a certain (parameterized) department.

You could have a table holding the deptno that the MV should use:

create table deptno_for_mv
(deptno number not null);

insert into deptno_for_mv values (10);
commit;

And then define your MV something like this:

create materialized view ...
...
...
as
select emp.*
from emp
where emp.deptno = (select deptno from deptno_for_mv);

The MV now holds deptno 10 employees. If you want to change that, then:

update deptno_for_mv set deptno=20;
commit;

And then refresh the MV.

I have below mentioned query to be used in MV :

SELECT ivr_vw.event_type, ivr_vw.event_sub_type,
ivr_vw.channel_cd,
ivr_vw.csg_event_id,
ivr_vw.csg_title,
ivr_vw.itv_title,
ivr_vw.program_id,
ivr_vw.price,
ivr_vw.addt_flag,
(SELECT FROM_TZ(TO_TIMESTAMP (TO_CHAR (to_date(ivr_vw.start_date,'MM/DD/YYYY HH24:MI:SS'), 'MM/DD/YYYY HH24:MI:SS'),
'MM/DD/YYYY HH24:MI:SS' ), 'EST')
AT TIME ZONE 'MST' FROM DUAL) start_date,
(SELECT FROM_TZ(TO_TIMESTAMP (TO_CHAR (to_date(ivr_vw.end_date,'MM/DD/YYYY HH24:MI:SS'), 'MM/DD/YYYY HH24:MI:SS'),
'MM/DD/YYYY HH24:MI:SS' ), 'EST')
AT TIME ZONE 'MST' FROM DUAL) end_date,
(SELECT FROM_TZ(TO_TIMESTAMP (TO_CHAR (to_date(ivr_vw.start_order_window,'MM/DD/YYYY HH24:MI:SS'), 'MM/DD/YYYY HH24:MI:SS'),
'MM/DD/YYYY HH24:MI:SS' ), 'EST')
AT TIME ZONE 'MST' FROM DUAL) start_order_window,
(SELECT FROM_TZ(TO_TIMESTAMP (TO_CHAR (to_date(ivr_vw.end_order_window,'MM/DD/YYYY HH24:MI:SS'), 'MM/DD/YYYY HH24:MI:SS'),
'MM/DD/YYYY HH24:MI:SS' ), 'EST')
AT TIME ZONE 'MST' FROM DUAL) end_order_window,
ivr_vw.dnis_cd, ivr_vw.packaged_event_id, ivr_vw.no_instalments,
ivr_vw.disclaimer_flag, ivr_vw.english_voice_file_name,
ivr_vw.spanish_voice_file_name, ivr_vw.package_flag,
ivr_vw.is_all_day_event, ivr_vw.upsell_data, ivr_vw.dur_guaran_flag,
ivr_vw.cp_profile_id, ivr_vw.high_def_flag
FROM ppv_ivr_vw ivr_vw,
(Select time_diff,
use_daylight
From ppv_zip_timezones ztz
Where ztz.zip_code = 80014
) timeOffset
WHERE NOT EXISTS (
SELECT 'x'
FROM ppv_channels_blackout_vw blk_vw
WHERE ivr_vw.channel_cd = blk_vw.channel_cd
AND blk_vw.zone_type_code = 'Area Code'
AND blk_vw.zone_code = '435');


Here, the column 'ztz.zip_code = 80014' should change dynamically as per the request of the user.
Is it possible. If yes, what is the process of doing so.?


Regards
ND

You can parameterize with »global« parameters (like sys_context()): But then again you have to refresh the mv to see the new values:

SQL> create materialized view mv_test
as
select sys_context('userenv', 'client_info') cl from dual
/
Materialized View created.

SQL> select * from mv_test
/
CL                                                                              
--------------------------------------------------------------------------------
                                                                                
1 row selected.

SQL> exec dbms_application_info.set_client_info('some value')
PL/SQL procedure successfully completed.

SQL> exec dbms_snapshot.refresh('mv_test')
PL/SQL procedure successfully completed.

SQL> select * from mv_test
/
CL                                                                              
--------------------------------------------------------------------------------
some value                                                                      
1 row selected.

All,

Please see the query which I have mentioned above.



Regards
ND

The values in the timeoffset inline view play no part in the query at all so it's difficult to see what you're trying to achieve here.

If you did change that value then the MV would have to be refreshed, and that would affect all users. I don't think that a materialized view is what you want here.

Perhaps you can explain what problem you're trying to solve, without assuming that passing a value to an MV is the way to solve it.

Seen it. But the point still stands that others have already mentioned. The data is materialized and only changes when you have chosen for it to do a refresh. It will not change dynamically based on input from a user. You could have the matialized view hold all of the data and then when the data is queried, the users requirement is just part of the where clause; that would be quite normal, but if you want the materialized view itself to only contain the data the user has filtered with a parameter then it will require the data to be refreshed and that will effect the who materialized view for that user and all other users (assuming it's a multi-user application).

In business terms, what are you trying to achieve? It sounds as if a materialized view may not be the best thing for what you are trying to achieve and that maybe a regular view, or even some use of global temporary tables would be better, or maybe even partitioned data if it's a performance issue you're trying to resolve.

Without suitable information it's difficult for us to advice the best route for you to take.