Yassine Yassine - 6 months ago 21
SQL Question

Test existing oracle table partition before adding a new one

I've already searched in the Stackoverflow existing subject but couldn't find an answer,

I try to write a pl sql script to add a partition to my existing table,
BUT, BEFORE THIS, i need to verify if the partition to add is already existing in this table or not,
If exist, i have nothing to do,
If NOT, i have to create it with an alter table
here's my script that does not work :s :s

CREATE OR REPLACE PROCEDURE ADD_PARTITION AS

V_TEST_PARTITION INTEGER := 1;
REQUETE VARCHAR2(5000);

BEGIN

SELECT COUNT(*)
INTO V_TEST_PARTITION
FROM USER_TAB_PARTITIONS
WHERE TABLE_NAME = 'SALES'
AND PARTITION_NAME = 'MY_PARTITION';


IF V_TEST_PARTITION = 0


REQUETE := 'ALTER TABLE SALES ADD PARTITION'|| DEV || 'VALUES ('||MY_PARTITION'||')
TABLESPACE "STORE_DATA"';

EXECUTE IMMEDIATE REQUETE;


ELSE IF V_TEST_PARTITION = 0 THEN
DBMS_OUTPUT.PUT_LINE('MY_PARTITION' is already exist');
END IF;

END;


Thanks a lot you for helping

Answer

You have a mistake in your code:

IF V_TEST_PARTITION = 0

should be

IF V_TEST_PARTITION <> 0