Trigger based partition creation

I have a list partitioned table on a foreign key. So if I want to insert a new entity the missing partition throws an exception on insert. I thought I am a cool duke and use a trigger to create new partitions :-) But the partition will not become available during execution. If you wait a bit everything works fine (but a dbms_lock.sleep will no do the trick).

So this is my trigger (and needed procedure) - note the "check partition section" near the end

 CREATE OR REPLACE PROCEDURE Execute_DDL
   (i_sql IN VARCHAR2)
AS
  pragma autonomous_transaction;
BEGIN
  EXECUTE IMMEDIATE (i_sql);
  commit;
END;
/

.

CREATE OR REPLACE TRIGGER Q_FLD_NEW_PART_TRG
AFTER INSERT
ON Q_FLD 
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
  l_cnt number;
  l_wait_cnt number := 0;
  l_alter varchar2(1000);
  l_job_stmt varchar2(1000);
  l_job_nr number;
  l_job dba_jobs_running%rowtype;
BEGIN
  SELECT count(*) INTO l_cnt from user_tables 
     where table_name = 'QUOTE' and partitioned = 'YES';

   if l_cnt <= 0 then return; end if;

   l_alter := 'ALTER TABLE QUOTE ADD PARTITION QUOTE_PART_' || :new.name ||  ' VALUES (' || :new.id || ')';
   l_job_stmt := 'begin Execute_DDL (''' || l_alter || '''); end;'; 
   DBMS_JOB.SUBMIT (job => l_job_nr, what => l_job_stmt);

   if l_job_nr is null then 
     raise_application_error(-20000, 'Partition Job Creation failed!', true);   
   end if;

   -- wait for job to complete
   while l_job_nr is not null loop
     l_wait_cnt := l_wait_cnt +1;
     if l_wait_cnt > 30 then raise_application_error(-20000, 'pratition creation timed out!'); end if;

     begin            
       select * into l_job from dba_jobs_running where job = l_job_nr;
       if l_job.failures >0 then
         raise_application_error(-20000, l_job_stmt, true);
       end if;

       sys.dbms_lock.sleep(2);

       exception when no_data_found then
         l_job_nr := null;     -- job completed
     end;
   end loop;

   -- check partition available
   /* this will lead into a "no data found" exception. 
      so i can not use开发者_StackOverflow the new partition immediatly. why??
   sys.dbms_lock.sleep(2);
   select count(*) into l_cnt
     from  user_objects 
     where object_type = 'TABLE PARTITION'
     and   subobject_name = 'QUOTE_PART_' || upper(:new.name);

   if l_cnt <= 0 then
     raise_application_error(-20000, 'Partition creation falied/timed out: ' || 'QUOTE_PART_' || :new.name, true); 
   end if;
   */ 
   exception when others then
     raise_application_error(-20000, l_job_stmt, true);
END q_fld_new_part_trg;
/

Anyone an Idea to get around this? I use 11gR2 64 Bit on Linux

Since you are using Oracle 11.2, is there a reason that you wouldn't use interval partitioning here? Assuming that ID is a numeric column, something like this would tell Oracle to create a new partition every time you inserted a new ID value.

SQL> create table interval_table(
  2    id number,
  3    value varchar2(10)
  4  )
  5  partition by range(id)
  6  interval( 1 )
  7  (
  8    partition initial_partition values less than (2)
  9  );

Table created.

SQL> insert into interval_table( id, value )
  2    values( 1, 'Initial' );

1 row created.

SQL> insert into interval_table( id, value )
  2    values( 10, 'New' );

1 row created.