3

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?

Improve this question
3
  • Do you want a single session that calls the procedure 1000 times in a session other than your own? Or do you want N sessions simultaneously executing the procedure 1000/N times each? Commented Sep 6, 2011 at 19:39
  • Assuming that you need multiple sessions to execute the procedure asynchronously, N sessions simultaneously executing the procedure 1000/N times each. Commented Sep 6, 2011 at 20:53
  • I want to simulate the stored procedure being called rapidly from an external process. Commented Sep 6, 2011 at 20:54

1 Answer 1

Reset to default
6

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

very nice, i will be trying it out shortly. I'll let you know how it worked. In the meantime +1
It seems as though the dbms_job.submit is not working. I created the proc and I can execute it. However, if I try to use dbms_job.submit to execute the job nothing happens. It says the block was completed, but the proc is not getting executed
@kralco626 - Did you commit? The job can only run after you've committed the dbms_job.submit call. What is your JOB_QUEUE_PROCESSES set to? If that's set to 0, your database isn't configured to allow background jobs so you need to bump it up. Do you see the job in DBA_JOBS (or USER_JOBS or ALL_JOBS depending on your privileges)?
I don't think I have permission to view any of that information. I did execute the commit command after the dbms_job.submit
@kralco626 - You should have permission to view USER_JOBS at a minimum. If you don't have permission to view V$PARAMETER to see the current JOB_QUEUE_PROCESSES setting, you'll need to ask your DBA what the current setting is.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.