Hello. This outstanding community members have helped me a lot over the past few months. Thank you all for donating your time, knowledge and experience. That is greatly appreciated.
I posted a similar question a few months back, but it applied to ServiceNow ticketing system: Passing more than one var between tasks - how is it done? - Get Help - Ansible . You have helped me with that, thank you. But after demoing the solution to my management, they decided to scrap the automated solution because the client doesn’t use ServiceNow for tracking Oracle database requests (my main responsibility).
However, they use the JIRA by Atlassian extensively. That product is more related to project management instead of service ticketing, and the client isn’t necessarily opposed to auto-execution of Oracle Database Change Requests (DCRs) automation directly from JIRA. Here is the premise. Instead of assigning a task of executing a script in a database to a living person, which may take days, the JIRA is assigned to a virtual user Automation API. The database server polls the JIRA URL every so many seconds, and as soon as it detects the DCR form filled out , with the script attached, it immediately downloads the attachment and executes it.
That SINGLE attachment works fine, but I am struggling to expand the functionality into a multiple attachments/scripts realm. I need a VAR that the attachments will loop through, and an algorithm to download them all.
First, let me post the single attachment Ansible playbook and its run log. We will then proceed to debugging the entire JIRA output, which should help you to point me in the right direction.
The single DCR Ansible playbook, which works, but it only downloads and executes ONE of the three scripts attached to the JIRA task (more on that below, including the full debugging):
---
- name: Get all task metadata for the DDI project
hosts: localhost
connection: local
gather_facts: true
vars_files:
- vars/main.yml
tasks:
- name: Set fact for Ansible date_time
delegate_to: localhost
run_once: true
set_fact:
currentdate: "{{ ansible_date_time.date }}"
tags: linux
- name: Display Ansible date fact
ansible.builtin.debug:
var: currentdate
run_once: true
tags: linux
- name: Set Ansiblr current time fact
ansible.builtin.set_fact:
current_time: "{{ now(fmt='%Y-%m-%d_%H-%M-%S') }}"
run_once: true
tags: linux
- name: Display the formatted time
ansible.builtin.debug:
var: current_time
- name: Fetch JIRA issue details
community.general.jira:
uri: "{{ jira_url }}"
username: "{{ jira_user }}"
password: "{{ jira_api_token }}"
issue: KAN-67
operation: fetch
register: jira_issue
- name: Display DCR attachment filename
ansible.builtin.debug:
var: jira_issue.meta.fields.attachment[0].filename
- name: Display DCR attachment URL
ansible.builtin.debug:
var: jira_issue.meta.fields.attachment[0].content
- name: Extract attachment URL
set_fact:
target_attachment: "jira_issue.meta.fields.attachment[0].filename"
- name: Download the attachment
ansible.builtin.get_url:
# url: "https://clerambeau370-1776214257398.atlassian.net/rest/api/2/attachment/content/10464"
url: "{{ jira_issue.meta.fields.attachment[0].content }}"
dest: "{{ scratchpad_dcr_runtime }}"
headers:
Authorization: "Basic {{ (jira_user + ':' + jira_api_token) | b64encode }}"
when: target_attachment is defined
- name: Enter Starting Work entry in the Worklog
community.general.jira:
uri: "{{ jira_url }}"
username: "{{ jira_user }}"
password: "{{ jira_api_token }}"
issue: KAN-67
operation: worklog
comment: A worklog added by Ansible. Automation completed the DCR successfully on {{ ansible_date_time.iso8601 }}, moved the Task to Validation and uploaded all run logs to the JIRA. There are no more tasks. Thank you.
fields:
timeSpentSeconds: 12000
############################DCR EXECUTION###################################################
############################### lockfile####################################
- name: Check if a prior run is exited or failed or Lockfile exists | itsm_dcr
stat:
path: "{{ scratchpad_dcr_runtime }}/lockfile"
register: prior_run_lockfile_out
- name: Proceed with the current DCR if a lockfile is absent | itsm_dcr
when:
- not prior_run_lockfile_out.stat.exists
block:
- name: Debug the GoldenGate Change Request data ogg_cr_custom_out | itsm_dcr
ansible.builtin.debug:
msg: "No prior run (failed or still running) lockfile present, starting the current DCR"
- name: Create Lockfile if Doesnt Exist | itsm_dcr
ansible.builtin.file:
path: "{{ scratchpad_dcr_runtime }}/lockfile"
state: touch
mode: '0644'
############################################################################
- name: 1. Find all Oracle database DCR scripts in the Scratchpad | itsm_dcr
ansible.builtin.find:
paths: "{{ scratchpad_dcr_runtime }}"
patterns: "*.sql"
recurse: yes
file_type: file
register: directory_contents_check
- name: Debug message if the Scratchpad is empty | itsm_dcr
ansible.builtin.debug:
msg: "The DCR directory {{ scratchpad_dcr_runtime }} is empty."
when: directory_contents_check.matched == 0
- name: Debug message if the Scratchpad is NOT empty | itsm_dcr
ansible.builtin.debug:
msg: "The DCR directory {{ scratchpad_dcr_runtime }} is NOT empty, ({{ directory_contents_check.matched }} Oracle DCR scripts found)."
when: directory_contents_check.matched > 0
- name: 2. Pass a list of DCR SQL scripts files to a variable array
ansible.builtin.find:
paths: "{{ scratchpad_dcr_runtime }}"
patterns: "*.sql"
recurse: no
file_type: file
ignore_errors: true
# delegate_to: localhost
register: loop_through_dcr_sql_files_out
- name: 3. Insert the shell heading wrapper to the SQL scripts loop
ansible.builtin.lineinfile:
dest: "{{ item.path }}"
# line: "$ORACLE_HOME/bin/sqlplus -s / as sysdba <<EOF"
line: "{{ dcr_connect_credentials }}"
insertbefore: BOF
# backup: true
with_items: "{{ loop_through_dcr_sql_files_out.files }}"
register: changed_header_out
- name: 4. Insert the shell trailing wrapper to the SQL scripts loop
ansible.builtin.lineinfile:
dest: "{{ item.path }}"
line: "EOF"
insertafter: EOF
# backup: true
with_items: "{{ loop_through_dcr_sql_files_out.files }}"
register: changed_trail
- name: 5. Rename the wrapper-populated .SQL DCR files to .SH shell scripts using the shell module
ansible.builtin.shell:
cmd: "mv {{ item.path }} {{ item.path | regex_replace(old_extension + '$', new_extension) }}"
loop: "{{ loop_through_dcr_sql_files_out.files }}"
loop_control:
label: "Renaming {{ item.path }} to {{ item.path | regex_replace(old_extension + '$', new_extension) }}"
- name: 6. Find all Oracle database DCR scripts in the Scratchpad | itsm_dcr
ansible.builtin.find:
paths: "{{ scratchpad_dcr_runtime }}"
patterns: "*.ssh"
recurse: yes
file_type: file
register: loop_through_dcr_ssh_files_out
- name: 7. Change ownership and group of DCR shell scripts to oracle-readable
ansible.builtin.file:
path: "{{ item.path }}"
mode: '0777'
with_items: "{{ loop_through_dcr_ssh_files_out.files }}"
register: changed_trail
- name: 8. Run each DCR script in order
ansible.builtin.shell:
cmd: "{{ item.path }}"
loop: "{{ loop_through_dcr_ssh_files_out.files }}"
loop_control:
label: "Running DCR script: {{ item.path }}"
ignore_errors: true
register: run_dcr_out
when: directory_contents_check.matched > 0
- name: Debug the results of the DCRs run | itsm_dcr
debug: var=run_dcr_out
- name: Remove the Lockfile after the DCR completion | itsm_dcr
ansible.builtin.file:
path: "{{ scratchpad_dcr_runtime }}/lockfile"
state: absent
mode: '0644'
- name: Archive the scratchpad directory into a zip file, move to /dcr_archived | itsm_dcr
community.general.archive:
path: "{{ scratchpad_dcr_runtime }}"
dest: "{{ scratchpad_dcr_archive }}/DCR_archived_{{ current_time }}.zip"
format: zip
force_archive: true
ignore_errors: true
register: archive_dcr_out
- name: Empty the Scratchpad | itsm_dcr
ansible.builtin.file:
path: "{{ scratchpad_dcr_runtime }}"
state: absent
- name: Recreate the Scratchpad | itsm_dcr
ansible.builtin.file:
path: "{{ scratchpad_dcr_runtime }}"
state: directory
mode: "0755"
- name: Enter Completed Worklog on issue
community.general.jira:
uri: "{{ jira_url }}"
username: "{{ jira_user }}"
password: "{{ jira_api_token }}"
issue: KAN-67
operation: worklog
comment: A worklog added by Ansible. Automation completed the DCR successfully on {{ ansible_date_time.iso8601 }}, moved the Task to Validation and uploaded all run logs to the JIRA. There are no more tasks. Thank you.
fields:
timeSpentSeconds: 12000
- name: Transition the DCR status from IN PROGRESS to RESOLVED
community.general.jira:
uri: "{{ jira_url }}"
username: "{{ jira_user }}"
password: "{{ jira_api_token }}"
issue: KAN-67
operation: transition
status: Resolved
Here is the output:
[misoracle@ehsmg-mis-dba1 ansible]$ ap dcr_jira05.yml
[WARNING]: Found both group and host with same name: localhost
[WARNING]: Collection community.general does not support Ansible version 2.16.3
PLAY [Get all task metadata for the DDI project] ***************************************************************************************************************************************************************************************************************
TASK [Gathering Facts] *****************************************************************************************************************************************************************************************************************************************
ok: [localhost]
TASK [Set fact for Ansible date_time] **************************************************************************************************************************************************************************************************************************
ok: [localhost]
TASK [Display Ansible date fact] *******************************************************************************************************************************************************************************************************************************
ok: [localhost] => {
"currentdate": "2026-05-01"
}
TASK [Set Ansiblr current time fact] ***************************************************************************************************************************************************************************************************************************
ok: [localhost]
TASK [Display the formatted time] ******************************************************************************************************************************************************************************************************************************
ok: [localhost] => {
"current_time": "2026-05-01_08-27-12"
}
TASK [Fetch JIRA issue details] ********************************************************************************************************************************************************************************************************************************
ok: [localhost]
TASK [Display DCR attachment filename] *************************************************************************************************************************************************************************************************************************
ok: [localhost] => {
"jira_issue.meta.fields.attachment[0].filename": "DCR_001.sql"
}
TASK [Display DCR attachment URL] ******************************************************************************************************************************************************************************************************************************
ok: [localhost] => {
"jira_issue.meta.fields.attachment[0].content": "https://clerambeau370-1776214257398.atlassian.net/rest/api/2/attachment/content/10523"
}
TASK [Extract attachment URL] **********************************************************************************************************************************************************************************************************************************
ok: [localhost]
TASK [Download the attachment] *********************************************************************************************************************************************************************************************************************************
changed: [localhost]
TASK [Enter Starting Work entry in the Worklog] ****************************************************************************************************************************************************************************************************************
changed: [localhost]
TASK [Check if a prior run is exited or failed or Lockfile exists | itsm_dcr] **********************************************************************************************************************************************************************************
ok: [localhost]
TASK [Debug the GoldenGate Change Request data ogg_cr_custom_out | itsm_dcr] ***********************************************************************************************************************************************************************************
ok: [localhost] => {}
MSG:
No prior run (failed or still running) lockfile present, starting the current DCR
TASK [Create Lockfile if Doesnt Exist | itsm_dcr] **************************************************************************************************************************************************************************************************************
changed: [localhost]
TASK [1. Find all Oracle database DCR scripts in the Scratchpad | itsm_dcr] ************************************************************************************************************************************************************************************
ok: [localhost]
TASK [Debug message if the Scratchpad is empty | itsm_dcr] *****************************************************************************************************************************************************************************************************
skipping: [localhost]
TASK [Debug message if the Scratchpad is NOT empty | itsm_dcr] *************************************************************************************************************************************************************************************************
ok: [localhost] => {}
MSG:
The DCR directory /mnt/dba/automation/dcr/runtime is NOT empty, (1 Oracle DCR scripts found).
TASK [2. Pass a list of DCR SQL scripts files to a variable array] *********************************************************************************************************************************************************************************************
ok: [localhost]
TASK [3. Insert the shell heading wrapper to the SQL scripts loop] *********************************************************************************************************************************************************************************************
changed: [localhost] => (item={'path': '/mnt/dba/automation/dcr/runtime/DCR_001.sql', 'mode':
TASK [4. Insert the shell trailing wrapper to the SQL scripts loop] ********************************************************************************************************************************************************************************************
changed: [localhost] => (item={'path': '/mnt/dba/automation/dcr/runtime/DCR_001.sql', 'mode':
TASK [5. Rename the wrapper-populated .SQL DCR files to .SH shell scripts using the shell module] **************************************************************************************************************************************************************
changed: [localhost] => (item=Renaming /mnt/dba/automation/dcr/runtime/DCR_001.sql to /mnt/dba/automation/dcr/runtime/DCR_001.ssh)
TASK [6. Find all Oracle database DCR scripts in the Scratchpad | itsm_dcr] ************************************************************************************************************************************************************************************
ok: [localhost]
TASK [7. Change ownership and group of DCR shell scripts to oracle-readable] ***********************************************************************************************************************************************************************************
changed: [localhost] => (item={'path': '/mnt/dba/automation/dcr/runtime/DCR_001.ssh', 'mode':
TASK [8. Run each DCR script in order] *************************************************************************************************************************************************************************************************************************
changed: [localhost] => (item=Running DCR script: /mnt/dba/automation/dcr/runtime/DCR_001.ssh)
TASK [Debug the results of the DCRs run | itsm_dcr] ************************************************************************************************************************************************************************************************************
ok: [localhost] => {
"run_dcr_out": {
"changed": true,
"msg": "All items completed",
"results": [
{
"ansible_loop_var": "item",
"changed": true,
"cmd": "/mnt/dba/automation/dcr/runtime/DCR_001.ssh",
"delta": "0:00:00.312678",
"end": "2026-05-01 08:27:17.540300",
"failed": false,
"invocation":
"start": "2026-05-01 08:27:17.227622",
"stderr": "",
"stderr_lines": [],
"stdout": "\n1 row created.\n\n\nCommit complete.",
"stdout_lines": [
"",
"1 row created.",
"",
"",
"Commit complete."
]
}
],
"skipped": false
}
}
TASK [Remove the Lockfile after the DCR completion | itsm_dcr] *************************************************************************************************************************************************************************************************
changed: [localhost]
TASK [Archive the scratchpad directory into a zip file, move to /dcr_archived | itsm_dcr] **********************************************************************************************************************************************************************
changed: [localhost]
TASK [Empty the Scratchpad | itsm_dcr] *************************************************************************************************************************************************************************************************************************
changed: [localhost]
TASK [Recreate the Scratchpad | itsm_dcr] **********************************************************************************************************************************************************************************************************************
changed: [localhost]
TASK [Enter Completed Worklog on issue] ************************************************************************************************************************************************************************************************************************
changed: [localhost]
TASK [Transition the DCR status from IN PROGRESS to RESOLVED] **************************************************************************************************************************************************************************************************
changed: [localhost]
PLAY RECAP *****************************************************************************************************************************************************************************************************************************************************
localhost : ok=30 changed=14 unreachable=0 failed=0 skipped=1 rescued=0 ignored=0
[misoracle@ehsmg-mis-dba1 ansible]$
Now, for my attempt to get the multiple attachments downloads. It looks like the DCR JIRA task has a unique identifier:
jira_issue.meta.id
The task with that ID may have none, or many attachments, each identified by a unique attachment ID.
"attachment": [
{
"author": {
"accountId": "712020:7d15570a-3512-4d4d-8c3d-85e6359b582c",
"accountType": "atlassian",
"active": true,
"avatarUrls": {
"16x16": "https://avatar-management--avatars.us-west-2.prod.public.atl-paas.net/712020:7d15570a-3512-4d4d-8c3d-85e6359b582c/486224f3-f8a3-483f-b14c-acb285a53f79/16",
"24x24": "https://avatar-management--avatars.us-west-2.prod.public.atl-paas.net/712020:7d15570a-3512-4d4d-8c3d-85e6359b582c/486224f3-f8a3-483f-b14c-acb285a53f79/24",
"32x32": "https://avatar-management--avatars.us-west-2.prod.public.atl-paas.net/712020:7d15570a-3512-4d4d-8c3d-85e6359b582c/486224f3-f8a3-483f-b14c-acb285a53f79/32",
"48x48": "https://avatar-management--avatars.us-west-2.prod.public.atl-paas.net/712020:7d15570a-3512-4d4d-8c3d-85e6359b582c/486224f3-f8a3-483f-b14c-acb285a53f79/48"
},
"displayName": "clerambeau",
"emailAddress": "<REDACTED>@gmail.com",
"self": "https://clerambeau370-1776214257398.atlassian.net/rest/api/2/user?accountId=712020%3A7d15570a-3512-4d4d-8c3d-85e6359b582c",
"timeZone": "US/Eastern"
},
"content": "https://clerambeau370-1776214257398.atlassian.net/rest/api/2/attachment/content/10523",
"created": "2026-04-30T20:01:46.449-0400",
"filename": "DCR_001.sql",
"id": "10523",
"mimeType": "text/plain",
"self": "https://clerambeau370-1776214257398.atlassian.net/rest/api/2/attachment/10523",
"size": 59
},
Below is the playbook that gets all the metadata for a given JIRA task with only two scripts attached, DCR_001.sql, DCR_003.sql and DCR_005.sql:
---
- name: Get all task metadata for the DCR project
hosts: localhost
connection: local
gather_facts: true
vars_files:
- vars/main.yml
tasks:
- name: Fetch JIRA issue details
community.general.jira:
uri: "{{ jira_url }}"
username: "{{ jira_user }}"
password: "{{ jira_api_token }}"
issue: KAN-67
operation: fetch
register: jira_issue
- name: Display DCR attachment filename
ansible.builtin.debug:
# var: jira_issue.meta.id
var: jira_issue
Please help me loop through all the attachments to be downloaded, not just one. Thank you.
Nestor Kandinsky-Clerambeau.