Unix shell script run every 10 SQL scripts in parallel in oracle DB

I have file.txt :-
script1.sql
script2.sql
.
.
.
script100.sql

I want to pickup 10 script and run in oracle DB parallelly then wait till those complete and run next 10 and so on till all scripts completed.

CodePudding user response：

Maybe something like:

parallel -j10 sql ora://user:pass@server/db '<' ::: *sql

or:

parallel -j10 sql ora://user:pass@server/db '<' :::: file.txt

CodePudding user response：

If you do not have gnu parallel installed but awk is available, then an awk script like this could work:

function workOnBufferedLines(maxprint){
    cmd = ""
    for(i=1; i<= maxprint; i  ){
        # instead of echo use your sql script executor
        cmd= cmd "echo " buffer[i % MAX] " &\n"
    }
    
    # wait waits for all background jobs to finish
    cmd = cmd "wait\n"

    system(cmd)
}

# set the MAX to the number of lines to buffer
BEGIN{ MAX=10 }

# default rule: append to buffer
{ buffer[NR % MAX] = $0 }

# every MAX line:
NR % MAX == 0 { workOnBufferedLines(MAX) }

# at the file end: work on remaining lines in buffer
END{ workOnBufferedLines(NR%MAX) }

Save the snippet as script.awk and run it like

awk -f script.awk file.txt 

Instead of echo in line 5 use your sql interpreter (e.g. sqlplus).

The idea is to build a sequence of shell commands each ending with & to run them as background jobs and use the wait command to wait for all jobs to finish before going on.