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

Archives Ansible Project
1

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”: “SQLPlus: 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 SQLPlus”,
“stdout_lines”: [
“SQLPlus: 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 SQLPlus”
]
}
}

some one help me

2

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

3

Hey, wondering if you found the solution to this.

4

Hi,

Perhaps, you should have a look on these :

https://github.com/oravirt/ansible-oracle

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

Regards,