CREATE OR REPLACE PROCEDURE DROP_PART_SUBPART_ (
v_period IN  VARCHAR2,
 t_name            IN  VARCHAR2,
 v_part_no         IN  NUMBER)
IS
v_count             NUMBER;

-- oeripek 04.04.2013

BEGIN
IF t_name='*'  THEN
FOR i IN (SELECT TABLE_NAME AS v_t_name  FROM USER_TABLES
 WHERE  TABLE_NAME IN ( 'TABLO1', 'TABLO2'))
LOOP
IF i.v_t_name ='TABLO1' THEN
FOR v_last_digit IN 0 .. 9
LOOP
SELECT COUNT(1) INTO v_count FROM USER_TAB_PARTITIONS
 WHERE   PARTITION_NAME='PART_'||v_period||'_'||v_last_digit||'_'||TO_CHAR
 (v_part_no)||'';

IF   v_count > 0 THEN
 EXECUTE IMMEDIATE 'ALTER TABLE TABLO1 DROP PARTITION PART_'||v_period||'_'||
v_last_digit||'_'||TO_CHAR (v_part_no)||'';

ELSE
raise_application_error (-20002, 'TABLO1 tablosunun PART_'||v_period||'_'||
v_last_digit||'_'||TO_CHAR (v_part_no)||'  partition''ı yok!');

END IF;
END LOOP;
ELSIF  i.v_t_name ='TABLO2' THEN

SELECT COUNT(1) INTO v_count FROM USER_TAB_PARTITIONS
 WHERE  TABLE_NAME='TABLO2' and PARTITION_NAME='PART_'||v_period||'_'||
TO_CHAR (v_part_no)||'';

IF   v_count > 0 THEN
EXECUTE IMMEDIATE 'ALTER TABLE TABLO2 DROP PARTITION PART_'||v_period||'_'||
TO_CHAR (v_part_no)||'';

ELSE
raise_application_error (-20002, 'TABLO2 tablosunun PART_'||v_period||'_'||
 TO_CHAR (v_part_no)||' partition''ı yok!');
END IF;

END IF;
END LOOP;

DBMS_OUTPUT.put_line ('Tüm Partition''lar silinmiştir.');
 ELSE
 raise_application_error (
 -20002,
 'Partition''ı silinecek tablo ismi girilmedi!');
 END IF;
 EXCEPTION
 WHEN OTHERS
 THEN
 raise_application_error ('-20009', SQLERRM);

END;
 /

Reklamlar