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

[Gopi Krishna]

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 <logon> ::= <username>[/<password>][@<connect_identifier>] <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>] Enter user-name: SP2-0306: Invalid option. Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM}] [edition=value]] where <logon> ::= <username>[/<password>][@<connect_identifier>] <proxy> ::= <proxyuser>[<username>][/<password>][@<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 <logon> ::= <username>[/<password>][@<connect_identifier>] <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>] Enter user-name: SP2-0306: Invalid option. Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYSKM}] [edition=value]] where <logon> ::= <username>[/<password>][@<connect_identifier>] <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>] SP2-0157: unable to CONNECT to ORACLE after 3 attempts, exiting SQL*Plus"

        ]

    }

}

some one help me

[Kai Stian Olstad]

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


--
Kai Stian Olstad

[vishal natekar]

Hey, wondering if you found the solution to this. 

[Jean-Yves LENHOF]

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,

--
You received this message because you are subscribed to the Google Groups "Ansible Project" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ansible-proje...@googlegroups.com.
To post to this group, send email to ansible...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ansible-project/e31a47af-cc16-4fe0-ab75-80e9a58dfe13%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.