Re: Use bind variables in a view
- From: Sybrand Bakker <postbus@xxxxxxxxxxxxxxxxx>
- Date: Thu, 17 Nov 2005 06:23:53 +0100
On 16 Nov 2005 16:43:23 -0800, "Chelsea" <mtbgirl@xxxxxxxxx> wrote:
>- Hi
>Ideally I would love to use variables defined and assigned inside a
>view.
>
>create or replace view as rates
>as
>declare
> d_sysdate date;
> d_start_date date;
> d_end_date date;
>
>begin
>d_sysdate := ADD_MONTHS(current_date,-1);
>d_start_date :=
>TO_DATE('01-'||TO_CHAR(d_sysdate,'MON')||'-'||TO_CHAR(d_sysdate,'YYYY'));
>d_end_date := LAST_DAY(d_sysdate);
>
>When I try to create the view - it is unhappy because the select is not
>where it expects to see it. ( I am assuming that I simply can not do
>that.. yes I know what happens when you assume)
>
>SOOO I was reading somewhere .. and I can not seem to find it again ..
>Is to basically wrap the view creation with a stored procedure, create
>and assign the variables in the stored procedure, then do create the
>view using execute immediate (I think)
>
>This is procedure that would have to be run once a month, to refresh
>the dates to the current date.
>
>Does any one have any other ideas, or any comments about this approach?
>
>Thanks
>C.M.
Basically what you need to do (and it works like a charm) is
- set up a package
- define the variables in the package spec
- have one procedure per variable (in the package) to set the variable
- have one function per variable (in the package) to get it's value
- use the function in the view definition
Alternatively (courtesy of Tom Kyte) you could set up a context, and
use the sys_context function to get the value of your variable.
According to Tom, that approach works better. Refer to
http://asktom.oracle.com
--
Sybrand Bakker, Senior Oracle DBA
.
- Follow-Ups:
- Re: Use bind variables in a view
- From: Gunter Herrmann
- Re: Use bind variables in a view
- References:
- Use bind variables in a view
- From: Chelsea
- Use bind variables in a view
- Prev by Date: Re: use of bind variables in oracle view
- Next by Date: Re: No logging during delete
- Previous by thread: Use bind variables in a view
- Next by thread: Re: Use bind variables in a view
- Index(es):
Relevant Pages
|
