sql - Triggering an oracle job based completion of the procedures -

scenario: have 77 procedures, each 1 data file load tables. once complete set of 77 procedures completes transformation job should start push data downstream.

one way create flag value once procedure completes , exits , store on table , based on query on table , condition trigger job. approach needs lot of code changes , it's not advised.

please suggest me dba tables v$session or dba_procedures on can put condition depict running , completion of procedure , can trigger job. or suggest other idea.

"once complete set of 77 procedures completes [successfully] transformation job should start push data downstream."

i added think missing, crucial keyword there. when comes determining whether given piece of application code has completed successfully, yes, can write convoluted logic detects each process (1) has been running in recent past , (2) no longer running right now. wouldn't recommend this, overlooks checking load jobs finished successfully.

i'd suggest proactive, rather reactive design: write 1 central point of code of these processes submitted, monitored, , return-code checked. "correct" way implement depend on how application schedules , runs these load procedures. if have "design-by-clone" application (i.e. each of 77 files loaded independent script cron-scheduled , runs sqlldr utility 1 file), , not want modify these 77 files, simple diy approach might cron-schedule single "master" script that:

1) submits 77 loading procedure background processes - retaining process id in array 2) loops through array, waits on each process, , checks return code 3) submits "post-77-loading" process when #2 completes

basic implementation of above idea:

#! bash load_script1.sh & processes[1]=$? load_script2.sh & processes[2]=$? ... load_script77.sh & processes[77]=$?  (( c=1; c<77; c++))     wait ${processes[c]}     if [ $? -gt 0 ] ; echo "at least 1 load job failed!" ; exit 1 ; fi done run_after_77_loads.sh 

of course, if you're using other cron, e.g. dbms_scheduler, you'll want write that's capable of checking return codes of each job in context.