asked on

Oracle Syntax

I have a table with year/quarter partitions like '2010Q1, 2010Q2....'

I want to truncate all Q2 partitions in this table across all years.

I know I can do it by "alter table <table_name> truncate partition 2010Q2, 2011Q2, 2012Q2 etc.

But, I want to write a dynamic sql like:

alter table <table_name> truncate partitions in (SELECT partition_name FROM ALL_TAB_PARTITIONS
where table_owner = 'abcd'
and table_name = 'bcdef'
and partition_name like '%Q2'

I know the above query doesnt work, but is there a way to write this kind of query?

Thanks

flow01

In a 2 step approach :
create the sql
execute it

You can combine them in an oracle procedure

create or replace
procedure truncate_qn (p_table_name varchar2, p_qn varchar2)
is
v_statement varchar2(32767);
begin
for r1 in (
SELECT partition_name FROM ALL_TAB_PARTITIONS
where table_owner = 'abcd'
and table_name = 'bcdef'
and partition_name like '%' || p_qn
) loop
if v_statement is not null then
v_statement := v_statement || ',';
end if;
v_statement := v_statement || r1.partition_name;
end loop;

v_statement := 'alter table ' || p_table_name || ' truncate partitions in ' ||
v_statement;

dbms_output.put_line(v_statement);
--execute immediate v_statement; -- uncomment if you see the right statement
end;
/

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MRPT

ASKER

Thanks a lot.

awking00

You're welcome.