Re: use of bind variables in oracle view

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.

check out application contexts, you'll end up with a view like:

create view XXX as
select ...
where col = sys_context('MYCTX','ATTRIB1');

-- 
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions"
Co-author: "Oracle Insight - Tales of the OakTable"

web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com


"Semper in excremento, sole profundum qui variat."

------------------------------------------------------------