Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Convert LONG to INT or VARCHAR

834137Jun 15 2011 — edited Jun 15 2011
Hi,

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;
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 13 2011
Added on Jun 15 2011
3 comments
2,145 views