Re: Use bind variables in a view

On 16 Nov 2005 16:43:23 -0800, "Chelsea" <mtbgirl_at_gmail.com> 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