# Ansible sqlplus

**URL:** <https://forum.ansible.com/t/ansible-sqlplus/23651>\
**Category:** Ansible Developer\
**Created:** [February 6, 2017, 3:52pm UTC](https://forum.ansible.com/t/ansible-sqlplus/23651 "2017-02-06T15:52:09Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Anand\_Dasari](https://avatars.discourse-cdn.com/v4/letter/a/f475e1/32.png) [@Anand\_Dasari](https://forum.ansible.com/u/Anand_Dasari)\
**Post date:** [February 6, 2017, 3:52pm UTC](https://forum.ansible.com/t/ansible-sqlplus/23651/1 "2017-02-06T15:52:09Z")

</div>

Can somebody explain me is there a possibility , without ssh to onto target VM  
whether ansible can kickoff sqlplus or jndi connection to target db and run commands from ansible machine?

---

<div class="post-metadata">

**Author:** ![Anthony\_Bond1](https://avatars.discourse-cdn.com/v4/letter/a/439d5e/32.png) [@Anthony\_Bond1](https://forum.ansible.com/u/Anthony_Bond1)\
**Post date:** [February 7, 2017, 2:21am UTC](https://forum.ansible.com/t/ansible-sqlplus/23651/2 "2017-02-07T02:21:45Z")

</div>

You could shell out locally and run this on the ansible host, if you provide all the remote database information to sqlplus. Of course you would need to have sqlplus installed locally with a working tnsnames.ora file configured. Another option if someone has time would be to develop an Ansible module that uses the cx\_oracle python module.

---

<div class="post-metadata">

**Author:** ![jd1](https://avatars.discourse-cdn.com/v4/letter/j/46a35a/32.png) [@jd1](https://forum.ansible.com/u/jd1)\
**Post date:** [February 7, 2017, 7:52am UTC](https://forum.ansible.com/t/ansible-sqlplus/23651/3 "2017-02-07T07:52:52Z")

</div>

H

February 6, 2017 4:52 PM, "Anand Dasari' via Ansible Development"  
wrote:

> Can somebody explain me is there a possibility , without ssh to onto target VM  
> whether ansible can kickoff sqlplus or jndi connection to target db and run  
> commands from ansible machine?

you're thinking much too complicated .. you can create a shell task and delegate  
it to local -\>

- name: execute statement  
shell: |  
{{ oracle\_home }}/bin/sqlplus -S {{ connect }} \<\< EOT  
whenever sqlerror exit sql.sqlcode  
set feedback off;  
set pagesize 0;  
set linesize 10000;  
{{ sql }}  
# TODO/FIXME: EOT is sent by magic?  
register: sql\_out  
ignore\_errors: true  
delegate\_to: 127.0.0.1

Another possibility would be to create an ansible python module which connects  
through cx\_Oracle ...

HTH

/jd

---

<div class="post-metadata">

**Author:** ![oravirt](https://avatars.discourse-cdn.com/v4/letter/o/3e96dc/32.png) [@oravirt](https://forum.ansible.com/u/oravirt)\
**Post date:** [February 7, 2017, 9:05am UTC](https://forum.ansible.com/t/ansible-sqlplus/23651/4 "2017-02-07T09:05:48Z")

</div>

Hi,

I’ve written an oracle\_sql module (which is in sort of an alpha stage) that connects via cx\_Oracle and runs arbitrary sql or scripts.  
It (and a bunch of others) can be found [here](https://github.com/oravirt/ansible-oracle-modules).

regards  
/M

---

<div class="post-metadata">

**Author:** ![Ebby](https://avatars.discourse-cdn.com/v4/letter/e/50afbb/32.png) [@Ebby](https://forum.ansible.com/u/Ebby)\
**Post date:** [June 12, 2018, 4:27am UTC](https://forum.ansible.com/t/ansible-sqlplus/23651/5 "2018-06-12T04:27:13Z")

</div>

It will be great if you could explain what goes in {{connect}}. I supplied the below to this variable but it did not work:

“@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host={{inventory\_hostname}})(Port={{oraport}}))(CONNECT\_DATA=(SERVICE\_NAME={{orasvc}})))”

---

<div class="post-metadata">

**Author:** ![Anthony\_Bond1](https://avatars.discourse-cdn.com/v4/letter/a/439d5e/32.png) [@Anthony\_Bond1](https://forum.ansible.com/u/Anthony_Bond1)\
**Post date:** [June 12, 2018, 10:26am UTC](https://forum.ansible.com/t/ansible-sqlplus/23651/6 "2018-06-12T10:26:19Z")

</div>

Ebby the connect variable would need to be the entire SQLPLUS connection string, user/pass included. I would suggest using the [Oracle modules](https://github.com/oravirt/ansible-oracle-modules) that have been created. They work really well cx\_oracle and removes the need to inject data into sqlplus.