Oracle SQL% ROWCOUNT equivalent for DBMS_PARALLEL_EXECUTE

Question

Is there an equivalent SQL%ROWCOUNTfor DBMS_PARALLEL_EXECUTE? Currently, if I run SQL%ROWCOUNTafter approval, it will return only 1.

Help would be greatly appreciated. Thanks =)

Answer 1

I don’t think that you can - basically what you ask for - is that there was SQL% ROWCOUNT for each of the tasks performed for your pieces. None of the DBMS_PARALLEL_EXECUTE routines seem to have a way of knowing how many records were updated in this fragment, and the DBA_PARALLEL_EXECUTE_CHUNKS table does not have the number of rows in a piece.

, , - SQL $ROWCOUNT , (*) ( , - - ).

Answer 2

! . , - , DBA_PARALLEL_EXECUTE DML (mn ), . , (SQL$ROWCOUNT) . , , .

, , oracle. ( 12c) DBA_PARALLEL_EXECUTE

, run_task. SQL$ROWCOUNT DML MY_PROCEDURE.

DECLARE
  l_task     VARCHAR2(30) := 'parallel_processing';
  l_sql_stmt VARCHAR2(32767);
  l_try      NUMBER;
  l_status   NUMBER;
BEGIN
  DBMS_PARALLEL_EXECUTE.create_task (task_name => l_task);

  DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(task_name   => l_task,
                                               table_owner => 'SCHEMANAME',
                                               table_name  => 'T_PROCESS',
                                               by_row      => TRUE,
                                               chunk_size  => 10000);

  l_sql_stmt := 'begin MY_PROCEDURE( :start_id, :end_id ); end;';

  DBMS_PARALLEL_EXECUTE.run_task(task_name      => l_task,
                                 sql_stmt       => l_sql_stmt,
                                 language_flag  => DBMS_SQL.NATIVE,
                                 parallel_level => 10);

  -- If there is error, RESUME it for at most 2 times.
  l_try := 0;
  l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
  WHILE(l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED) 
  Loop
    l_try := l_try + 1;
    DBMS_PARALLEL_EXECUTE.resume_task(l_task);
    l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task);
  END LOOP;

  DBMS_PARALLEL_EXECUTE.drop_task(l_task);
END;
/