Oracle Database 12c
Red Hat Linux 7
Is there a way I can convert the highdate for each partition to Julian date?
Declare
c_table_name varchar2(30):='MOBILE_INOCS_CBP_DAILYPART'; --specify name of table
c_table_owner varchar2(30):='ARCHICOM'; --you can specify owner
v_highvalue varchar2(8000);
v_highdate date;
v_newname varchar2(30);
Begin
--DBMS_OUTPUT.ENABLE(1000000);
for r1 in (
select partition_name
from all_tab_partitions
where table_name=c_table_name and table_owner=c_table_owner
and partition_name like 'P%'
) LOOP
select high_value into v_highvalue from all_tab_partitions
where table_name=c_table_name and table_owner=c_table_owner
and partition_name=r1.partition_name;
execute immediate 'select '||v_highvalue||' from dual' into v_highdate;
v_newname:='MOBILE_INOCS_CBP_'||to_char(v_highdate-1,'YYYYMMDD'); -- or another notation
--DBMS_OUTPUT.PUT_LINE(v_newname);
execute immediate 'Alter table '||c_table_owner||'.'||c_table_name
||' rename partition '||r1.partition_name||' to '||v_newname;
end LOOP;
End;
/
Thanks,
Joe