Jenkins Pipeline: Loop Multiple Databases

Here is an example for using Jenkins to loop through multiple Oracle databases (pluggable databases in this case), and execute an SQL*Plus script on each.

The pipeline uses Active Choice checkboxes to specify which pluggable databases will be affected. The Groovy pipeline then loops over the selected databases, and executes an SQL*Plus script on each.

Create a new Jenkins pipeline job:

In the Pipeline section add the Groovy code below.

properties([
    parameters([
        [
            $class: 'ChoiceParameter',
            choiceType: 'PT_CHECKBOX',
            description: '',
            filterLength: 1,
            filterable: false,
            name: 'pdbs',
            script: [
                $class: 'GroovyScript',
                fallbackScript: [
                    classpath: [],
                    sandbox: false,
                    script: 'return[\'Error\']'
                ],
                script: [
                    classpath: [],
                    sandbox: false,
                    script: 'return["PDB1","PDB2","PDB3"]'
                ]
            ]
        ]

    ])
])
node {
    pdbs.split(",").each {pdb ->
        stage("Database $pdb"){
                withCredentials([
                    usernamePassword(credentialsId: 'my_db_credentials', usernameVariable: 'dbuser', passwordVariable: 'dbpwd')
                ]) {
                    script {
                        conn = "@//dbservername:1521/" + pdb + ".myservice.com"
                    }
                    sh """
sqlplus \$dbuser/\$dbpwd${conn} <<EOF
select 'World!' as hello from dual;
exit;
EOF
"""
                }
        }
    }
}

Lines 1-26 define the job parameters. In this case I use an Active Choices parameter with checkboxes. Line nine defines the name of the parameter, which is referenced below. Line 20 is the list of PDBs.

Line 28 begins the loop iterating over the selected PDBs. Since the pdbs parameter is returned as a comma separated string, the split(“,”) function turns that string into an array list. Each list element is then represented as variable pdb.

Line 29 creates a stage step. Note that with this syntax, the number of stages will be equal to the number of checkboxes checked.

Line 30 sets the dbuser and dbpwd variables based on Jenkins username/password credentials.

The script section in lines 33-35 creates a connection string. In this case an Easyconnect string that does not require a tnsnames.ora file.

Finally, lines 36-41 execute a shell command invoking SQL*Plus with a basic query.

When the job is executed in Jenkins, simply check the databases you wish to effect and click the Build button.

When all three checkboxes are selected, three stages will appear, each executing the SQL*Plus script in the respective database.

This example should help you write more complex SQL*Plus scripts. You can also reference variables inside the SQL*Plus command using the dollar-syntax ${variable}:

select 'my name is ${name_parameter}' from dual;

I hope this helps. Give it a try!