How can I write a plsql code for creating a new split partition in a table?

Question

This is my alter statement:

alter table demo_table
split partition PART_35 values('9999') into (partition PART_35, partition PART_36)

The table already consists of a list partition on emp_id,where PART_35 is the last partition created and it is having the default value used in ORACLE and PART_36 is the new partition which I have created.

The procedure will take two arguments:

1. Table name
2. Emp_id(in the above code 9999 is the emp_id which I want to create the partition, basically I am
   creating the partition on emp_id)

The point where I am getting stuck is how to consider part_35 and part_36 as generalized one. So that for every partition if the user pass emp_id and table name a new partition will be created making part_35 for emp_id 9999 and part_36 as the partition for default value in oracle. Note: The partition having the default value will be the last partition every table.

Thanks :)

Answer 1

When you create a table with list partition you will give it a default partition name and split it when you need a new partition.

Let's examine it a little bit. For example you have a table named demo_table right ?

CREATE TABLE DEMO_TABLE
(
ID NUMBER,
NAME VARCHAR2(100 CHAR)
)
PARTITION BY LIST (ID) 
(
     PARTITION PDEFAULT VALUES (DEFAULT)
);

And create a procedure to split its partition when needed.

CREATE OR REPLACE PROCEDURE DEMO_PROCEDURE (TABLENAME VARCHAR2,PID VARCHAR2)
IS
V_CONTROL NUMBER;
BEGIN
--Controls if any of the given partitions created before??
   SELECT COUNT (*)
     INTO V_CONTROL
     FROM USER_TAB_PARTITIONS
    WHERE     TABLE_NAME = TABLENAME
          AND PARTITION_NAME = 'P' || PID;

   IF V_CONTROL > 0
   THEN
      EXECUTE IMMEDIATE
            'ALTER TABLE '||TABLENAME ||' DROP PARTITION P'|| PID;
   END IF;

   EXECUTE IMMEDIATE
         'ALTER TABLE '||TABLENAME ||' SPLIT PARTITION PDEFAULT VALUES('
      || PID
      || ') INTO ( PARTITION P'
      || PID
      || ',PARTITION PDEFAULT)';

END DEMO_PROCEDURE;
/

Below code should work.

Official Docs.

alter table demo_table
split partition YOUR_DEFAULT_PARTITION(In your case it is PART_35) values('9999') into 
(partition YOUR_NEW_PARTITION, partition YOUR_DEFAULT_PARTITION(In your case it is PART_35))