# how can i execute the following oracle sql commands using ansible and get the value back to be stored in variable

**URL:** <https://forum.ansible.com/t/how-can-i-execute-the-following-oracle-sql-commands-using-ansible-and-get-the-value-back-to-be-stored-in-variable/26868>\
**Category:** Ansible Project\
**Created:** [February 28, 2018, 10:58am UTC](https://forum.ansible.com/t/how-can-i-execute-the-following-oracle-sql-commands-using-ansible-and-get-the-value-back-to-be-stored-in-variable/26868 "2018-02-28T10:58:21Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Gopi\_Krishna](https://avatars.discourse-cdn.com/v4/letter/g/d9b06d/32.png) [@Gopi\_Krishna](https://forum.ansible.com/u/Gopi_Krishna)\
**Post date:** [February 28, 2018, 10:58am UTC](https://forum.ansible.com/t/how-can-i-execute-the-following-oracle-sql-commands-using-ansible-and-get-the-value-back-to-be-stored-in-variable/26868/1 "2018-02-28T10:58:21Z")

</div>

HI,

MY playbook:

sample.sh:

. /u05/oracle/VISEBS/12.1.0/VISEBS\_sal-devtl10.env  
val=‘v$archived\_log’  
R=`sqlplus '/as sysdba'<<EOF set numw 1 lines 80 pages 0; select max(NEXT_CHANGE#) "RMAN_SCN" from $val where BACKUP_COUNT > 0; exit; EOF`

echo $R

I am trying to achieve the same through ansible using shell command, but im getting the error

- hosts: source  
tasks:
- include\_vars: sql.yml
- shell: |  
. /u05/oracle/VISEBS/12.1.0/VISEBS\_sal-devtl10.env  
val=‘v$archived\_log’  
R=`sqlplus '/as sysdba' <<-EOF set numw 1 lines 80 pages 0; select max(NEXT_CHANGE#) "RMAN_SCN" from $val where BACKUP_COUNT > 0; exit; EOF`  
echo $R  
register: cmd
- debug:  
msg: “{{cmd}}”

error:

TASK [debug] \*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*\*  
ok: [10.210.8.149] =\> {  
“msg”: {  
“changed”: true,  
“cmd”: “. /u05/oracle/VISEBS/12.1.0/VISEBS\_sal-devtl10.env\n val=‘v$archived\_log’\n R=`sqlplus '/as sysdba' <<-EOF\n set numw 1 lines 80 pages 0;\n select max(NEXT_CHANGE#) \"RMAN_SCN\" from $val where BACKUP_COUNT > 0;\n exit;\n EOF`\n echo $R”,  
“delta”: “0:00:01.031347”,  
“end”: “2018-02-28 10:15:49.032656”,  
“failed”: false,  
“rc”: 0,  
“start”: “2018-02-28 10:15:48.001309”,  
“stderr”: “/bin/bash: line 10: warning: here-document at line 6 delimited by end-of-file (wanted `EOF')", "stderr_lines": [ "/bin/bash: line 10: warning: here-document at line 6 delimited by end-of-file (wanted `EOF’)”  
],  
“stdout”: “SQL_Plus: Release 12.1.0.2.0 Production on Wed Feb 28 10:15:48 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied Enter user-name: SP2-0306: Invalid option. Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM}] [edition=value]] where ::= [/][@\<connect\_identifier\>] ::= [][/][@\<connect\_identifier\>] Enter user-name: SP2-0306: Invalid option. Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM}] [edition=value]] where ::= [/][@\<connect\_identifier\>] ::= [][/][@\<connect\_identifier\>] SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL_Plus”,  
“stdout\_lines”: [  
“SQL_Plus: Release 12.1.0.2.0 Production on Wed Feb 28 10:15:48 2018 Copyright (c) 1982, 2014, Oracle. All rights reserved. ERROR: ORA-01017: invalid username/password; logon denied Enter user-name: SP2-0306: Invalid option. Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM}] [edition=value]] where ::= [/][@\<connect\_identifier\>] ::= [][/][@\<connect\_identifier\>] Enter user-name: SP2-0306: Invalid option. Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM}] [edition=value]] where ::= [/][@\<connect\_identifier\>] ::= [][/][@\<connect\_identifier\>] SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL_Plus”  
]  
}  
}

some one help me

---

<div class="post-metadata">

**Author:** ![Kai\_Stian\_Olstad](https://avatars.discourse-cdn.com/v4/letter/k/e19adc/32.png) [@Kai\_Stian\_Olstad](https://forum.ansible.com/u/Kai_Stian_Olstad)\
**Post date:** [March 1, 2018, 8:20pm UTC](https://forum.ansible.com/t/how-can-i-execute-the-following-oracle-sql-commands-using-ansible-and-get-the-value-back-to-be-stored-in-variable/26868/2 "2018-03-01T20:20:08Z")

</div>

I have never uses here-doc in Ansible, so I have no answer for that.  
But the easiest is to use the script module.

---

<div class="post-metadata">

**Author:** ![vishal\_natekar](https://avatars.discourse-cdn.com/v4/letter/v/ba9def/32.png) [@vishal\_natekar](https://forum.ansible.com/u/vishal_natekar)\
**Post date:** [May 7, 2020, 10:16am UTC](https://forum.ansible.com/t/how-can-i-execute-the-following-oracle-sql-commands-using-ansible-and-get-the-value-back-to-be-stored-in-variable/26868/3 "2020-05-07T10:16:42Z")

</div>

Hey, wondering if you found the solution to this.

---

<div class="post-metadata">

**Author:** ![jean-yves](https://avatars.discourse-cdn.com/v4/letter/j/a587f6/32.png) [@jean-yves](https://forum.ansible.com/u/jean-yves)\
**Post date:** [May 7, 2020, 11:04am UTC](https://forum.ansible.com/t/how-can-i-execute-the-following-oracle-sql-commands-using-ansible-and-get-the-value-back-to-be-stored-in-variable/26868/4 "2020-05-07T11:04:44Z")

</div>

Hi,

Perhaps, you should have a look on these :

[https://github.com/oravirt/ansible-oracle](https://github.com/oravirt/ansible-oracle)

[https://github.com/oravirt/ansible-oracle-modules](https://github.com/oravirt/ansible-oracle-modules)

A lot of works has been done here…

If you don’t want all of these, a step in the right direction here :

[https://stackoverflow.com/questions/41796466/ansible-playbook-to-execute-oracle-script](https://stackoverflow.com/questions/41796466/ansible-playbook-to-execute-oracle-script)

Regards,