How to connect and pass Oracle query result into variable

Get Help
1

Hi Team,

Please find the expected result from the database.
SQL> SELECT DESTINATION FROM V$ARCHIVE_DEST
WHERE STATUS != ‘INACTIVE’; 2

DESTINATION

/arch/DVC4I/CDSD0490

I want to store “/arch/DVC4I/CDSD0490” into a variable.

I wrote the following code

  • name: Run query as Oracle user using sudo su
    hosts: oracle_hosts
    become: yes
    become_user: orac4i

    tasks:

    • name: Run SQL query via sudo su
      shell: |
      sudo su - orac4i -c “sqlplus / as sysdba <<EOF
      SELECT DESTINATION FROM V\$ARCHIVE_DEST WHERE STATUS != ‘INACTIVE’;
      exit;
      EOF”
      register: archive_destination
      become: yes
    • name: Display Results
      debug:
      msg: “Oracle Archive Destination: {{ archive_destination }}”

But the archive_destination stores more information than expected.

TASK [Display Results] ********************************************************************************************************************************************************************************
ok: [caddld-590.belldev.dev.bce.ca] => {
“msg”: “Oracle Archive Destination: {‘stderr_lines’: , u’changed’: True, u’end’: u’2025-03-19 16:37:42.763075’, ‘failed’: False, u’stdout’: u’\nSQLPlus: Release 19.0.0.0.0 - Production on Wed Mar 19 16:37:42 2025\nVersion 19.24.0.0.0\n\nCopyright (c) 1982, 2024, Oracle. All rights reserved.\n\n\nConnected to:\nOracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.24.0.0.0\n\nSQL> \nDESTINATION\n--------------------------------------------------------------------------------\n/arch/DVC4I/CDSD0490\n\nSQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production\nVersion 19.24.0.0.0’, u’cmd’: u’sudo su - orac4i -c "sqlplus / as sysdba <<EOF\nSELECT DESTINATION FROM V\\\\\\$ARCHIVE_DEST WHERE STATUS != \‘INACTIVE\’; \nexit;\nEOF"\n’, u’rc’: 0, u’start’: u’2025-03-19 16:37:42.343045’, u’stderr’: u’‘, u’delta’: u’0:00:00.420030’, ‘stdout_lines’: [u’', u’SQLPlus: Release 19.0.0.0.0 - Production on Wed Mar 19 16:37:42 2025’, u’Version 19.24.0.0.0’, u’‘, u’Copyright (c) 1982, 2024, Oracle. All rights reserved.’, u’‘, u’‘, u’Connected to:’, u’Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production’, u’Version 19.24.0.0.0’, u’‘, u’SQL> ‘, u’DESTINATION’, u’--------------------------------------------------------------------------------‘, u’/arch/DVC4I/CDSD0490’, u’‘, u’SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production’, u’Version 19.24.0.0.0’], u’warnings’: [u"Consider using ‘become’, ‘become_method’, and ‘become_user’ rather than running sudo"]}”
}

Kindly advise.

Is there any other method to connect oracle database sqlplus “/as sysdba” with shell module?

Regards
Guru

2

I’ve never used it, but you might want to take a look at the community.general.odbc module. community.general.odbc module – Execute SQL using ODBC — Ansible Community Documentation

3

If you are going the way of running sqlplus in a shell, please use -S option for silent output so you don’t have extra information in the output (like an sqlplus banner, prompt and others). After that, you can use grep, awk, sed and other shell tools to additionally clean up your sqlplus output. Once you get it to a point where only /arch/DVC4I/CDSD0490 is displayed, the value can be accessed with {{ archive_destination['stdout'] }}

4

Also note that there’s ansible plugins to do the escalation you’re doing, so that you’ll run sqlplus -s... alone without the sudo su part.

The become plugin is called community.general.sudosu.

I also cleaned up the tasks, take a look:

- name: Run SQL query via sudo su
  ansible.builtin.shell: 
    cmd: sqlplus -s / as sysdba
    stdin: |
      SELECT DESTINATION FROM V\$ARCHIVE_DEST WHERE STATUS != 'INACTIVE';
      exit;
  register: archive_destination
  become: yes
  become_user: orac4i
  become_method: community.general.sudosu

- name: Display Results
  ansible.builtin.debug:
    msg: "Oracle Archive Destination: {{ archive_destination.stdout }}"
1 Like