Home » SQL & PL/SQL » SQL & PL/SQL » variable in a view
variable in a view [message #273720] Thu, 11 October 2007 08:53 Go to next message
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 #273724 is a reply to message #273720] Thu, 11 October 2007 08:59 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First '10-APR-07' is not a date it is a STRING, so use TO_DATE function.

Then you can use a context variable to parametrize your view.
Search on AskTom I'm sure there is (at least) an example of this.

Regards
Michel

Re: variable in a view [message #273739 is a reply to message #273724] Thu, 11 October 2007 09:35 Go to previous messageGo to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
Thanks Michel,
I got the asktom URL.

Regards,
Sandi
Re: variable in a view [message #273751 is a reply to message #273739] Thu, 11 October 2007 10:12 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:

I got the asktom URL

You could post it then it will server others.

Regards
Michel
Re: variable in a view [message #273853 is a reply to message #273751] Fri, 12 October 2007 00:07 Go to previous messageGo to next message
sandhyaa
Messages: 79
Registered: February 2007
Member
The URL is:

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1448404423206

Regards,
Sandi
Re: variable in a view [message #273876 is a reply to message #273853] Fri, 12 October 2007 01:27 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks, this will prevent others from making the same search. Smile
AskTom thread is named "Parameterized View & Truncation of Table" (just to make it easier to find this topic with search).

Regards
Michel
Re: variable in a view [message #316345 is a reply to message #273724] Thu, 24 April 2008 12:57 Go to previous messageGo to next message
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 #316348 is a reply to message #316345] Thu, 24 April 2008 13:01 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
aren't they shared between sessions?

By default, no, they are private.

Regards
Michel
icon1.gif  Re: variable in a view [message #318958 is a reply to message #316348] Thu, 08 May 2008 09:19 Go to previous messageGo to next message
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 #318975 is a reply to message #318958] Thu, 08 May 2008 10:11 Go to previous messageGo to next message
Michel Cadot
Messages: 57650
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the difference between this and context variable?
Apart from being far slower.

Regards
Michel
Re: variable in a view [message #319016 is a reply to message #273720] Thu, 08 May 2008 13:37 Go to previous messageGo to next message
Kevin Meade
Messages: 1907
Registered: December 1999
Location: Connecticut USA
Senior Member
try this:

Kevin Meade's OraFAQ Blog

Dynamic Views, Creating Views based on Variables

Kevin
icon5.gif  Re: variable in a view [message #321297 is a reply to message #318975] Mon, 19 May 2008 16:41 Go to previous message
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
Previous Topic: How to Create user defined data types?
Next Topic: PL/SQL Collections and Conditional Deletes
Goto Forum:
  


Current Time: Thu Apr 24 19:35:45 CDT 2014

Total time taken to generate the page: 0.12395 seconds