Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: use of bind variables in oracle view

Re: use of bind variables in oracle view

From: Connor McDonald <connor_mcdon..._at_yahoo.com>
Date: Thu, 17 Nov 2005 20:43:47 +0800
Message-ID: <437C7B03.72E5@yahoo.com>


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."

------------------------------------------------------------
Received on Thu Nov 17 2005 - 06:43:47 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US