YML script
- name: Execute SQL using shell
hosts: all
gather_facts: no
vars_prompt:
- name: db_name
prompt: "Enter the Oracle database name (service name or SID)"
private: no
- name: db_host
prompt: "Enter the Oracle hostname or IP"
private: no
- name: db_user
prompt: "Enter DB username"
private: no
- name: db_pass
prompt: "Enter DB password"
private: yes
vars:
sql_statement: "revoke update on ashwani.test from tarun;"
oracle_home: "/u01/app/oracle/product/19.0.0/db_1/"
tasks:
- name: Run SQL script
shell: |
export ORACLE_HOME={{ oracle_home }}
echo "{{ sql_statement }}" | $ORACLE_HOME/bin/sqlplus {{ db_user }}/{{ db_pass }}@'{{ db_host }}/{{ db_name }}'
register: query_result
- name: Display query result
debug:
var: query_result.stdout_lines
================= EXECUTION LOGS============================
[root@srv1 mypb]# ansible-playbook revoke.yml
Enter the Oracle database name (service name or SID): finance
Enter the Oracle hostname or IP: 192.168.1.122
Enter DB username: ashwani
Enter DB password:
PLAY [Execute SQL using shell] ****************************************************************************************************************************************
TASK [Run SQL script] *************************************************************************************************************************************************
[WARNING]: Platform linux on host 192.168.1.122 is using the discovered Python interpreter at /usr/bin/python, but future installation of another Python interpreter
could change this. See https://docs.ansible.com/ansible/2.9/reference_appendices/interpreter_discovery.html for more information.
changed: [192.168.1.122]
TASK [Display query result] *******************************************************************************************************************************************
ok: [192.168.1.122] => {
"query_result.stdout_lines": [
"",
"SQL*Plus: Release 19.0.0.0.0 - Production on Wed Nov 19 23:31:15 2025",
"Version 19.3.0.0.0",
"",
"Copyright (c) 1982, 2019, Oracle. All rights reserved.",
"",
"Last Successful login time: Wed Nov 19 2025 23:27:00 +04:00",
"",
"Connected to:",
"Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production",
"Version 19.3.0.0.0",
"",
"SQL> ",
"Revoke succeeded.",
"",
"SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production",
"Version 19.3.0.0.0"
]
}
PLAY RECAP ************************************************************************************************************************************************************
192.168.1.122 : ok=2 changed=1 unreachable=0 failed=0 skipped=0 rescued=0 ignored=0
=================================================================
Other Used case:
Option 1: Read DB list from an external YAML file
step 1)
Create a YAML file with the database list
Create a file named: db_list.yml
databases:
- host: 192.168.1.50
service: FINANCE
- host: 192.168.1.60
service: HRDB
- host: 10.10.10.5
service: SALES
- host: srv-db01.domain.local
service: ORCL
Step 2). Updated Ansible Playbook Using db_list.yml
- name: Execute SQL on multiple Oracle databases from file
hosts: all
gather_facts: no
vars_files:
- db_list.yml
vars_prompt:
- name: db_user
prompt: "Enter DB username"
private: no
- name: db_pass
prompt: "Enter DB password"
private: yes
vars:
sql_statement: "revoke update on ashwani.test from tarun;"
oracle_home: "/u01/app/oracle/product/19.0.0/db_1/"
tasks:
- name: Execute SQL on each DB from the file
shell: |
export ORACLE_HOME={{ oracle_home }}
echo "{{ sql_statement }}" | \
$ORACLE_HOME/bin/sqlplus {{ db_user }}/{{ db_pass }}@{{ item.host }}/{{ item.service }}
loop: "{{ databases }}"
loop_control:
label: "{{ item.host }} / {{ item.service }}"
register: sql_outputs
- name: Display results
debug:
msg: |
DB: {{ item.item.host }} / {{ item.item.service }}
Output:
{{ item.stdout }}
loop: "{{ sql_outputs.results }}"
