variable in a view [message #273720] |
Thu, 11 October 2007 08:53 |
sandhyaa
Messages: 79 Registered: February 2007
|
Member |
|
|
Hi,
I wanted to create a view where data supplied to the view is dynamic.
For example
The view syntax is this:
CREATE OR REPLACE VIEW viewA
select col1, col2, col3 from tableA
WHERE someDate BETWEEN START_DATE AND END_DATE
Here somedate can be any date value ('10-APR-07', '20-JUN-07')
So if somedate is '10-APR-2007' and when you do a
select * from viewA
it should actually execute
select col1, col2, col3 from tableA where '10-APR-07' BETWEEN START_DATE AND END_DATE
Is this possible to accomplish this? How? Please help.
Thanks
sandi
|
|
|
|
|
|
|
|
Re: variable in a view [message #316345 is a reply to message #273724] |
Thu, 24 April 2008 12:57 |
mailias
Messages: 3 Registered: April 2008
|
Junior Member |
|
|
context variables:
aren't they shared between sessions? What about different users of a webapp accessing the view at the same time? Wouldn't this mess up the results if you use this approach?
|
|
|
|
Re: variable in a view [message #318958 is a reply to message #316348] |
Thu, 08 May 2008 09:19 |
mailias
Messages: 3 Registered: April 2008
|
Junior Member |
|
|
There is another way how to implement views with parameters which is IMHO a whole lot simpler:
Setup:
-- global temp. table for storing the parameters
CREATE GLOBAL TEMPORARY TABLE tbl_params (
pname varchar2(255),
psvalue varchar2(255)
) ON COMMIT DELETE ROWS;
create or replace view v_test_paramview as
(
select * from mytable t
where
-- if parameter is not set the view is executed unparameterized
(t.myfield like nvl((select max(psvalue) from tbl_params p
where p.pname = 'v_test_paramview.pname' ), t.myfield))
);
Test:
-- Just in order to show you that the param table is empty.
-- Will be emptied by Oracle on commit, see definition of temp. table above.
select * from mfi_test_params;
-- set the parameter(s)
insert into tbl_params values ('v_test_paramview.pname', '<MY PATTERN>');
-- if the following select is executed before commit in the same
-- transaction then the view parameter is being used
select * from v_test_paramview;
commit;
-- now empty again...
select * from tbl_test_params;
Has anybody ever used this approach?
Kind regards from Germany
Matthias
[Updated on: Tue, 20 May 2008 01:07] by Moderator Report message to a moderator
|
|
|
|
|
Re: variable in a view [message #321297 is a reply to message #318975] |
Mon, 19 May 2008 16:41 |
mailias
Messages: 3 Registered: April 2008
|
Junior Member |
|
|
Hi Michel,
thanks for your reply. But is my approach really so much slower? I tried both solutions but I did not notice any significant performance difference (just my impression, I did not actually measure performance). Well, probably the join is more expensive than the PL/SQL function call but it is a join with a table that contains hardly any data. Furthermore in my case a few milliseconds do not matter anyways: My approach already improves performace a hundred times (60 sec -> 0,6 sec). I guess both solutions have their justification. I'm just trying to find the best solution for my situation. The issues mentioned below keep me from using the session variable approach which is why I came up with the temporary table thing. If I am wrong on these issues please let me know.
Advantages of my approach over the session variable solution:
1) As far as I understand things the session variable approach does not work for me as it might cause problems with connection pooling, won't it? My approach definitely does not. If I am wrong on that issue, please correct me.
2) Furthermore with the session variable approach you need a CallableStatement in order to set the variable. This is not necessary with my solution. You can simply set the view parameter using ordinary SQL. Only the temporary table is Oracle specific. However my approach could even be implemented completely independent from Oracle. You could just use an ordinary table in which you write the parameter values. The only thing you would have to do manually here in contrast to global temporary tables is to make sure that the table is being emptied again before commit.
Kind regards from Germany
Matthias
|
|
|