title: Trigger based partition creation
body: <p>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).</p>
  
  <p>So this is my trigger (and needed procedure) - note the "check partition section" near the end</p>
  
  <pre><code> CREATE OR REPLACE PROCEDURE Execute_DDL
     (i_sql IN VARCHAR2)
  AS
    pragma autonomous_transaction;
  BEGIN
    EXECUTE IMMEDIATE (i_sql);
    commit;
  END;
  /
  </code></pre>
  
  <p>.</p>
  
  <pre><code>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 &lt;= 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 =&gt; l_job_nr, what =&gt; 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 &gt; 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 &gt;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 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 &lt;= 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;
  /
  </code></pre>
  
  <p>Anyone an Idea to get around this? I use 11gR2 64 Bit on Linux</p>