Convert LONG to INT or VARCHAR
834137Jun 15 2011 — edited Jun 15 2011Hi,
I have a PL/SQL to query all partition tables with value less than < MYVALUE. I need to convert a long data type column to char or int to satisfy my query. The one below shows partition tables with the date < 2008-08-01 but what i need right now is for converting to INT or CHAR because the value of HIGH_VALUE is not a date anymore.
DECLARE
cursor c_part is select table_name, partition_name, high_value
from dba_tab_partitions
where table_name in ('FACT_INBOUND_AGENT','FACT_INBOUND_SKILL','FACT_INBOUND_VDN') and table_owner='REPORTING';
BEGIN
for r_part in c_part
loop
if to_date(substr(r_part.high_value,11,10), 'YYYY-MM-DD') <= to_date('2008-08-01','YYYY-MM-DD') ------- This is a condition for a long column(HIGH_VALUE) with a date value
then
dbms_output.put_line('alter table REPORTING.'||r_part.table_name
|| ' truncate partition ' ||r_part.partition_name
|| ' drop storage update global indexes;'
|| ' -- ' || substr(r_part.high_value,11,10));
end if;
end loop;
END;
/