convert high value to julian date

Posted by Roshan on Jun 13, 2017; 4:21pm
URL: http://erman-arslan-s-oracle-forum.124.s1.nabble.com/convert-high-value-to-julian-date-tp2805.html

Hi,

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,
Roshan