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
  vars:
    sql_password: !vault |
      $ANSIBLE_VAULT;1.1;AES256
      31663439333934653339323738336538353632663561643633316362366434303261316163613161        
      6664363630333466363566366636636439383333323334650a363836343036336165353662663961
      353332666130333235386365343132666534363435663635633533306333383363303237343033625
      37643331653731620a363562346435376664363536356662626338626331383035643330373265     
  tasks:
    - name: Query Simple Fact
      no_log: yes
      shell:
        cmd: |
          sqlplus -S admin/{{ sql_password }}@kclck01_low <<EOF
          set feedback off
          set heading off
          SET SERVEROUTPUT ON SIZE 5000;
          SET LINESIZE 2500;
          set pagesize 5000;
          set long 5000;
          select json_object('db_version' VALUE  BANNER) from v\$version;
          EOF
      register: simple_out
    - name: Transform response
      set_fact:
        simple_json: "{{ simple_out.stdout|from_json }}"       
    - name: Show result
      debug:
        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.