CODEWITHSUNDEEP
oracle-databaseplsqloracle-sqldeveloperplsqldeveloperplsql-package
Vinay
Vinay

Reputation: 11

How to write select plSQL script by filtering the value in Partition and then subpartition

I have a table which has Partitions and subpartitions. I have to first filter the results of Partition's High_Value (TO_DATE(' 2020-03-29 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) with less than some date value and then in the result set filter with Subpartion High value with some text value and if the condition satisfy's then drop that sub partition. I have written the code until given below, but not sure how to proceed further. Can someone please help on how to declare d_tmp and then loop through each subpartion and check

DECLARE
CURSOR get_parts IS
select partition_name, high_value
from ALL_TAB_PARTITIONS
where table_name = 'TempTable';
l_tmp LONG;
d_tmp DATE;
BEGIN
  FOR part_rec IN get_parts
  LOOP
    l_tmp := part_rec.high_value;
    EXECUTE IMMEDIATE 'SELECT ' || SUBSTR(l_tmp, 1, 90) || ' FROM DUAL' INTO d_tmp;
    DBMS_OUTPUT.PUT_LINE( to_char(d_tmp, 'DD-MM-YYYY'));
       
  END LOOP;
END;

Upvotes: 1

Views: 679

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11591

You are most of the way there. You just loop around the sub partitions for that partition

DECLARE
CURSOR get_parts IS
select partition_name, high_value
from ALL_TAB_PARTITIONS
where table_name = 'TEMPTABLE';
l_tmp LONG;
d_tmp DATE;

l_tmp2 LONG;

BEGIN
  FOR part_rec IN get_parts
  LOOP
    l_tmp := part_rec.high_value;
    EXECUTE IMMEDIATE 'SELECT ' || SUBSTR(l_tmp, 1, 90) || ' FROM DUAL' INTO d_tmp;
    DBMS_OUTPUT.PUT_LINE( to_char(d_tmp, 'DD-MM-YYYY'));
       
    for i in ( 
      select subpartition_name, high_value
      from ALL_TAB_SUBPARTITIONS
      where table_name = 'TEMPTABLE'
      and partition_name = get_parts.partition_name
      )
    loop
      l_tmp2 := i.high_value;
      --
      -- your checks
      --
      if [checks passed] then
        execute immediate 'alter table TEMPTABLE drop subpartition '||i.subpartition_name;
      
    end loop;
       
  END LOOP;
END;

Upvotes: 0

Related Questions