Re: Convert Long to Date
From: Tony Adolph <tony.adolph.dba_at_gmail.com>
Date: Fri, 27 Jun 2008 16:17:12 +1200
Message-ID: <4a38d9060806262117k550d0419jfa6c07af90c05122@mail.gmail.com>
archive_older_than date;
swap_partition_name varchar2(30);
swap_high_value varchar2(1000);
-- >>>>
where p.table_name = upper(v_tablename) and p.partition_position = 1;
Date: Fri, 27 Jun 2008 16:17:12 +1200
Message-ID: <4a38d9060806262117k550d0419jfa6c07af90c05122@mail.gmail.com>
Hi there,
Here's an extract from a procedure I knocked up this week that does just what you need,.......
procedure archive(v_tablename varchar, v_arch_tablename varchar default null, v_temp_tablename varchar default null, v_do boolean default false, v_days number default 30)is
archive_older_than date;
swap_partition_name varchar2(30);
swap_high_value varchar2(1000);
-- >>>>
swap_date date;
-- <<<<
begin
archive_older_than := trunc(sysdate) - v_days;
select p.partition_name, p.high_value
into swap_partition_name, swap_high_value from user_tab_partitions p
where p.table_name = upper(v_tablename) and p.partition_position = 1;
- >>>> execute immediate 'select '||swap_high_value||' from dual' into swap_date;
- <<<<
HTH
Cheers
Tony
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Jun 26 2008 - 23:17:12 CDT