# How to connect and pass Oracle query result into variable

**URL:** <https://forum.ansible.com/t/how-to-connect-and-pass-oracle-query-result-into-variable/41135>\
**Category:** Get Help\
**Created:** [March 19, 2025, 8:43pm UTC](https://forum.ansible.com/t/how-to-connect-and-pass-oracle-query-result-into-variable/41135 "2025-03-19T20:43:49Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Gurunathan\_Kanniappa](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/gurunathan_kanniappa/32/7936_2.png) [@Gurunathan\_Kanniappa](https://forum.ansible.com/u/Gurunathan_Kanniappa)\
**Post date:** [March 19, 2025, 8:43pm UTC](https://forum.ansible.com/t/how-to-connect-and-pass-oracle-query-result-into-variable/41135/1 "2025-03-19T20:43:49Z")

</div>

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

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’\nSQL_Plus: 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’SQL_Plus: 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

---

<div class="post-metadata">

**Author:** ![binbashroot](https://avatars.discourse-cdn.com/v4/letter/b/ad7895/32.png) [@binbashroot](https://forum.ansible.com/u/binbashroot)\
**Post date:** [March 19, 2025, 11:01pm UTC](https://forum.ansible.com/t/how-to-connect-and-pass-oracle-query-result-into-variable/41135/2 "2025-03-19T23:01:43Z")

</div>

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](https://docs.ansible.com/ansible/latest/collections/community/general/odbc_module.html)

---

<div class="post-metadata">

**Author:** ![bvitnik](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/bvitnik/32/4132_2.png) [@bvitnik](https://forum.ansible.com/u/bvitnik)\
**Post date:** [March 20, 2025, 1:37pm UTC](https://forum.ansible.com/t/how-to-connect-and-pass-oracle-query-result-into-variable/41135/3 "2025-03-20T13:37:06Z")

</div>

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'] }}`

---

<div class="post-metadata">

**Author:** ![hugonz](https://sea1.discourse-cdn.com/ansible/user_avatar/forum.ansible.com/hugonz/32/503_2.png) [@hugonz](https://forum.ansible.com/u/hugonz)\
**Post date:** [March 21, 2025, 5:13am UTC](https://forum.ansible.com/t/how-to-connect-and-pass-oracle-query-result-into-variable/41135/4 "2025-03-21T05:13:25Z")

</div>

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`](https://docs.ansible.com/ansible/latest/collections/community/general/sudosu_become.html).

I also cleaned up the tasks, take a look:

```yaml
- 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 }}"

```