I have daily wise view created having around 30 columns in my system, view name like 'CDR_Details_20170712'. I need to create customize view with required 5 columns.
So need to create proc which run daily basis and create view sysdate -1 means if today is 20170713 then it will create view from 20170701 to 20170712 same like for other days - ex - CDR_Details_20170701 .... CDR_Details_2017074 is sytem generated view , so once proc will rum its should customize view from 20170701 to 20170713, same like it will run daily and craete sysdate - 1 view. I Have issue to pass the date increment value . Urgent help really helpful here. Thanks in adavance.
CREATE OR REPLACE PROCEDURE test2_proc as
str1 varchar(4000);
v_year varchar2(10);
v_month varchar2(5);
v_day varchar2(5);
cdr_date varchar2(5);
x number := 0;
y number := 2;
begin
for v_all in (select to_char(trunc(sysdate-1),'YYYYMMDD') cdr_date from dual)
loop
select substr(v_all.cdr_date, 0, 4)
, substr(v_all.cdr_date, 5, 2)
, substr(v_all.cdr_date, 7, 2)
into v_year
, v_month
, v_day
from dual;
loop
x:=x+1;
if x<=y then
str1 := 'CREATE View '||'TZ'||'_CDR_DETAIL_'||v_all.cdr_date||' AS';
str1 := str1||' SELECT /*+ full(sd) parallel(sd,4) use_nl(sd,sc) use_nl(sc,ss) */ CDR_FILE_NO,EVENT_START_DATE,EVENT_START_TIME,PRODUCT_GROUP,BILLED_PRODUCT,EVENT_DIRECTION ,ANUM,BNUM,INCOMING_PATH,OUTGOING_PATH,EVENT_DURATION,AMOUNT,CURRENCY ,BILLING_OPERATOR ';
str1 := str1||' FROM ' ||' CDR_DETAIL_'||v_year||v_month||v_day||'_view ';
str1 := str1||' WHERE'|| 'franchise = '|| 'AIRGB';
Dbms_Output.put_line (str1||';');
end if;
exit when x =2;
end loop;
end loop;
END;
/