0
Need: To have Oracle kick off a shell script that performs processing on tables and data in the Oracle database. The script is instantiated by specific activity from web clients.
Issue: Web clients will instantiate this job concurrently. In order to execute shell scripts from Oracle, you must do so using the Oracle scheduler. This job is set to execute immediately from Oracle. What I am seeing with the ORA-27477 documentation is that Oracle does not allow jobs with the same name to run concurrently.
Background: We need to run this particular processing job from the shell because it uses C to do the heavy lifting. Porting that code to PL/SQL is not an option. Lots of legacy code in play here too that makes me sad, but that's life.
I did not know of this name generation function, which looks promising. After creating and using the job, would dropping the job be the correct method to avoid having 1000's of uniquely-named jobs crop up over time? – mlehmeher – 2014-08-25T15:49:48.703
There is no need to drop the jobs manually. You can set the
auto_drop
attribute to TRUE when creating the job, so the job will be dropped automatically when completed. – Balazs Papp – 2014-08-25T17:42:31.133All of this works. Great information. The next step would be to pass the generated name to the target program. Is there a way to do that? It would be useful to be able to pass that down to the OS script to avoid collisions when multiple jobs are running at the same time. – mlehmeher – 2014-08-28T15:48:31.190
Have a look at
DBMS_SCHEDULER.CREATE_JOB( number_of_arguments => ..., ...)
andDBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE
. – Balazs Papp – 2014-08-28T19:47:03.313