title: DBMS_PARALLEL_EXECUTE exiting without running task
body: <p>For large loads, we generally prefer to execute it in parallel and hence Oracle package DBMS_PARALLEL_EXECUTE. Here we are using chunking by row. When I run this, I am getting </p>
  
  <pre><code>ORA-29495: invalid state for resume task
  </code></pre>
  
  <p>Task status is CHUNKED and chunks are in UNASSIGNED status.</p>
  
  <p>What is going on here?</p>
  
  <pre><code>declare
      l_sql varchar2(500);
      l_chunk_sql varchar2(500);
      l_task_nm varchar2(50):='k_clearance_1';
      l_try number:=0;
      l_status number;
      l_count number:=0;
  begin
      select count(1)
      into l_count
      from user_parallel_execute_tasks
      where task_name=l_task_nm;
  
      --create task if not exist
      if l_count = 0 then
          --create task
          dbms_parallel_execute.create_task(l_task_nm);
      end if;
  
      --create chunk
  
      dbms_parallel_execute.create_chunks_by_rowid(
          task_name=&gt;l_task_nm,
          table_owner=&gt;'RXTEAM',
          table_name=&gt;'RPM_STAGE_CLEARANCE_ID_FIX',
          by_row=&gt;TRUE,
          chunk_size=&gt;10000
      );
  
      l_sql := 'update XXXXXX krsc
          set krsc.lgcy_extr_tmst = sysdate
          where exists (select 1
                        from rxteam.rpm_stage_clearance_id_fix rscf
                        where rscf.rpm_stage_clearance_id=krsc.rpm_stage_clearance_id
                        and rscf.rowid between :start_id and :end_id)';
  
      --run task        
      dbms_parallel_execute.run_task(task_name=&gt;l_task_nm,
          sql_stmt=&gt;l_sql,
          language_flag=&gt;DBMS_SQL.NATIVE,
          parallel_level=&gt;40
      );
  
      l_try := 0;
      l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task_nm);
  
      while (l_try &lt; 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED) 
      loop
          l_try := l_try + 1;
          DBMS_PARALLEL_EXECUTE.resume_task(l_task_nm);
          l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task_nm);
      end loop;
  
      --dbms_parallel_execute.drop_task(l_task_nm)
  end;
  /
  </code></pre>