use of bind variables in oracle view

• 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. Received on Wed Nov 16 2005 - 18:28:37 CST