Execute async stored procedure in sql developer

Question

I would like to use Oracle SQL Developer to execute a stored procedure asynchronously a large number of times.

Pseudo Code

var pStatus number
var pOraErrCd varchar2
var pOraErrMsg varchar2
for i 1 .. 1000 -- do async
loop 
    exec myproc('test',:pStatus ,:pOraErrCd ,:pOraErrMsg);
end loop;

The stored procedure's purpose is to do some inserts. For testing I just want to execute the stored procedure asynchronously a large number of times. I don't care about any return values.

Is there a "easy" way to do this?

Answer 1

Since you want to simulate N sessions each calling the procedure 1000/N times, I would probably do something like

CREATE OR REPLACE PROCEDURE call_myproc_n_times( p_n IN NUMBER )
AS
  p_status         NUMBER;
  p_ora_error_code VARCHAR2(1000);
  p_ora_error_msg  VARCHAR2(1000);
BEGIN
  FOR i IN 1 .. p_n 
  LOOP
    myproc( 'test', 
            p_status, 
            p_ora_error_code, 
            p_ora_error_msg );
  END LOOP;
END;

DECLARE
  l_num_sessions     number := 10;
  l_exec_per_session number := 100;
  l_jobno            pls_integer;
BEGIN
  FOR i IN 1 .. l_num_sessions
  LOOP
    dbms_job.submit( 
      l_jobno,
      'BEGIN ' ||
      '  call_myproc_n_times( ' || l_exec_per_session || ' ); ' ||
      'END;',
      sysdate + interval '1' minute );
  END LOOP;
  commit;
END;

This example will start 10 sessions each of which will execute the procedure 100 times in quick succession assuming your database's JOB_QUEUE_PROCESSES is at least 10 meaning that Oracle is allowed to have 10 jobs running in the background simultaneously. Creating the CALL_MYPROC_N_TIMES procedure isn't strictly necessary-- it just makes building the string to execute in the job easier.

An alternative would be to submit 1000 jobs each of which just called MYPROC once and relying on the JOB_QUEUE_PROCESSES parameter to limit the number of jobs that would be run simultaneously. That would work, it's just more difficult to change database parameters if you want to run more of fewer simultaneous sessions-- it's easy to adjust L_NUM_SESSIONS in the code I posted.