Ansible: Consume SQL*Plus Output

A simple way to consume Oracle Database data in Ansible playbooks without extensive Python or PL/SQL coding or 3rd party modules.

Ansible: Consume SQL*Plus Output

Due to tight security restrictions, I can't use some of the existing Ansible modules or 3rd party projects. But with a pinch of creativity, your Ansible playbooks can consume database data without extensive Python or PL/SQL coding.
Let's assume that your target host has an Oracle Database client installed,  and the database version is at least 12.2. The main idea is obvious: you need to get output in one of the Ansible native formats, so after crossing XML (native to the Oracle database, but hard to digest by Ansible) and YAML (which is not a thing for the Oracle database ), there is only one format left - JSON.
Without further ado, a sample script below queries database data in JSON format and transforms it into Ansible facts.

- name: Consume SQL Output Example
  hosts: localhost
    sql_password: !vault |
    - name: Query Simple Fact
      no_log: yes
        cmd: |
          sqlplus -S admin/{{ sql_password }}@kclck01_low <<EOF
          set feedback off
          set heading off
          SET LINESIZE 2500;
          set pagesize 5000;
          set long 5000;
          select json_object('db_version' VALUE  BANNER) from v\$version;
      register: simple_out
    - name: Transform response
        simple_json: "{{ simple_out.stdout|from_json }}"       
    - name: Show result
        var: simple_json

Playbook does three simple things: runs SQL*Plus command and produces JSON output, next task turns JSON formatted string to the Ansible fact, and the last task uses the result.

The only real trick here receives raw data output form SQL*Plus, and the primary helpers are:

  • Option -S suppresses all the standard SQL*Plus output.
  • Command set feedback off shuts down all the post query output
  • Command set heading off switches off table headers

The last tip for thoughts: if you need to produce a complex result as a single JSON array, look at the aggregating function json_arrayagg.