Henry Xie ‘s blog: To Run Any Sql Scripts from Sqlplus on Targets via Ansible Playbook

Henry Xie ‘s blog: To Run Any Sql Scripts from Sqlplus on Targets via Ansible Playbook

The main part of Yaml file is below.
 name description self-explains

 – hosts: “{{ v_host | default(‘test.com‘) }}”
   vars:
    – myparameter: “{{ v_para | default(‘db_unique_name’) }}”
    – mysid:  “{{ v_sid | default(‘ORCL’) }}”
    – myhost: “{{ v_host | default(‘test.com‘) }}”
    – dbhome_locl: “”
    – parameter_sql: /u01/app/ansible/repository/show_parameter.sql

   become: true
   become_method: pbrun
   become_user: oracle
   become_flags: ‘content-ansible’
   gather_facts: False
   tasks:
   – name: find the location of db home for the sid
     shell: ‘grep {{ mysid }} /etc/oratab |grep -v ^[#] | cut -d: -f2|head -1’
     register: dbhomeoutput
   – set_fact:
       dbhome_locl: “{{ dbhomeoutput.stdout }}”
   – debug: var=dbhome_locl

   – name: copy show parameter sql to target host /tmp
     copy:
          src: “{{ parameter_sql }}”
          dest: /tmp/ansible_sql.sql
          force: yes
          mode: 0755
   – name: run the sql to get the result
     shell: |
          export ORACLE_SID={{ mysid }}
          export ORACLE_HOME={{ dbhome_locl }}
          {{ dbhome_locl }}/bin/sqlplus -s “/ as sysdba” @/tmp/ansible_sql.sql “{{ myparameter }}”
     register: sqloutput

   – name: display details of sql output
     debug: msg=”{{ sqloutput.stdout_lines }}”

   – name: delete sql file on the remote target
     file:
       path: /tmp/ansible_sql.sql
       state: absent